How to Design a Program to Perform Complex financial calculations with .NET

Question

Alright, I hope this isn't too broad a question but my curiosity got the better of me. I'm working for a large insurance company. We are building excel spreadsheets that handle projection of future cash value for life insurance policies. These are pretty large workbooks(40-50mb) with a ton of sheets and columns that have to take an asinine amount of variables into account in order to accurately project cash values.

How would this be built as a desktop or business application? I know .net fairly well for a beginner but I'm having trouble thinking outside of the spreadsheet mindset and how you could apply .net to something like this. How would you track a calculation in code, that takes 3 or 4 pages with 60 columns at 1300 rows each in a spreadsheet? As well as changing interest rates, fees and other things that affect the policy, dynamically based on inputs like age, sex, smoker/non smoker, etc...

Answer 1

You'd have to break things down one piece at a time, and start with database design. Think about the underlying tables. You could go through each sheet in excel and think about what it would look like if you took information that is related and put it into a database table.

Database views would be excellent for doing basic math, since it can do calculations based on many tables and present the results in a nice format.

Answer 2

Here's the approach I'd take:

• Store your data (the stuff in the 3-4 pages with 60 columns/1,300 rows) in a DB or other data store.
• Do the calculations by either using DB functions (select sum(foo) from blah;), temp tables, in-memory arrays, whatever
• Save calculations in data store (for later easier retrieval and auditing)

Answer 3

I'll bet your company is doing > $1B annually, all based on an Excel spreadsheet. You aren't alone in that.

I'd start by thinking about the problem instead of the Excel spreadsheet. What is the business problem? Can you model it with objects? I see a few objects: Policy, with all its attendant Coverage and Class children; Insured, with characteristics like gender, smoker, etc. - you get the idea.

Projection sounds like you'll have a handful of "what if" variables that will have some statistical properties that you'll vary. Your job will be to run lots of Monte Carlo like simulations that alter the variables by drawing from a sample and computing the output. Your result will be a projected mean value with a standard deviation and a confidence level.

Could be a good candidate for massive parallel computing.

What is the basis for the calculations? Any well-known models that you can share? Maybe that would give a hint.

Answer 4

This is really a simple question... with a complex answer.

You are asking: How can I plan a complicated software project?

Know your environment (which you sound like you do):

• http://en.wikipedia.org/wiki/Data_mining
• http://en.wikipedia.org/wiki/Actuarial_science

Know software engineering:

• http://www.mypearsonstore.com/bookstore/product.asp?isbn=0201309580&xid=PSED
• http://en.wikipedia.org/wiki/Software_engineering

Know your language/developing environment:

• http://www.amazon.com/2008-NET-Platform-Fourth-Windows-Net/dp/1590598849/ref=sr_1_2?ie=UTF8&s=books&qid=1247090641&sr=8-2

---

1. First step is to figure out how you work as a developer.
2. Plan out the steps you will or could take.
3. Simplify everything to it's MINIMUM.
4. Revisit your list of what to do.
5. Mock it up.
6. Do it.

---

If you are nervous about starting, then I would create a simple functional prototype. Start with visualizing the project in something like Balsamiq Mockups, so that you can see how each piece of the puzzle will come together.

If you take a few days of researching software engineering methods, you will save yourself hours and hours of headaches. Start with good practices gifted to us from those alpha geeks who've already done what you're trying to do.

Answer 5

Also make sure not to use floats or doubles for calculations involving money. Use Decimal.

Answer 6

Consider for a moment that speadsheets, for many things are working at a higher level of abstraction than C# code is.

So to reimplement your worksheets in .Net you have a few broad approaches

1. Custom ad-hoc solution with lots of narrow specific features around your problem domain
2. Or you you could reimplement the concept of a spreadsheet. Split the calculation engine that evaluates a matrix of expressions with optional dependencies between the cells from the persistance and view of the maxtrix values. Store the data in a datastore that you like (xml, relationalDB), create a web/desktop app to edit and read the results and evaluate it using the custom engine.

Answer 7

To add some specifics:

• Cell values become variables or entries in a database. E.g., Instead of the formula for B1 being = A1 + 2 (where A1 contains the number of widgets for a given policy), you have:

  var widgets = (from policy in db.Policies select new {policy.widgets}).First().widgets;
var moreWidgets = widgets + 2;

• Functions and Macros get ported--E.g., Round(B1, 0) becomes Math.Round(moreWidgets, 0, MidpointRounding.AwayFromZero)

• Related data and functionality gets grouped into classes and object instances.
• Sheets become database tables for storage and tab pages/dialog boxes/web pages, etc. for UI.
• You might make forms for data that's entered/reviewed one at a time:

First Name: [____________]
Last Name: [____________]
SSN: [____________]

The benefits to doing all of this (assuming it's done well) is:

• Improved organization so that functionality can be found more easily for re-use/augmentation.
• Improved performance
• Shared, real-time access to the data including aggregating data that's currently spread across multiple files.
• A whole world of new functionality--maybe you want to add speech synthesis or what-have-you.

Answer 8

SpreadsheetGear for .NET will let you use your Excel models directly in your .NET applications without Excel and without spending a lot of time converting to C# or VB every time the business folks want to update the model.

You can download a free trial here if you want to try it for yourself.

Disclaimer: I own SpreadsheetGear LLC