I had the privilege of being selected to present Sumwise at the Innovation Bay dinner last night. The presentation went really well and I received some good questions and valuable feedback.
One question at the end of my talk caught my attention. I had discussed how traditional spreadsheets such as Excel actually build in complexity and opacity, and I gave the example of a formula from a mortgage calculator spreadsheet I searched for and downloaded from the web. This formula calculates the repayments required for each payment period:
I suspect that this formula gives the right results, but the issue I have with it is “how do you know”? I challenge anyone to dissect this formula and fully understand what it is doing and whether it is correct. Just look at it! It ends with no less than 7 closing round brackets!
Following on from this I ran through Sumwise and how it dramatically simplifies your models and lets you express what would be complex, coded, formulae into easy-to-write, easy-to-read, formulae in your native language. The final question from the floor was a really good one: “what would this formula look like in Sumwise”?
Firstly, the key components of the formula should be broken out into separate columns. I would have a column that calculates the correct effective rate using a lookup table approach. (I will describe how Sumwise deals with lookup tables in a separate post.)
Once this is done, the equivalent formula in Sumwise would be something like:
For those who are interested, basically the PMT function is a replica of the Excel PMT function, which is just a short-cut for the mathematical equation: PxR(1+R)^N/((1+R)^N-1), where P = principal, R = rate, N = number of periods.