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

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 2

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

Answer 3

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 4

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