Mortgage calculator formula

  • Tagged with:
  • Excel,
  • Innovation Bay,
  • Lookups,
  • Mortgage Calculator,
  • Tips & Tricks,

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:

=IF(A55=”",”",IF(A55=nper,I54+D55,MIN(I54+D55,IF(C55=C54, E54,IF($D$10=”Acc Bi-Weekly”,ROUND((-PMT(((1+C55/ CP)^(CP/12))-1, (nper-A55+1)*12/26,I54))/2,2),IF($D$10 =”Acc Weekly”, ROUND((-PMT(((1+C55/CP)^ (CP/12))-1, (nper-A55+1)* 12/52,I54))/4,2),ROUND(-PMT(((1+C55/ CP)^(CP/periods_per_year))-1,nper-A55+1,I54),2)))))))

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:

=PMT([effective rate],[periods remaining],[opening balance])

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.

3 Comments so far Join the Discussion

  1. Justin

    3rd June 2010 at about 4am

    That spreadsheet is interesting because you can tell that the creator is pretty savvy and knows some fairly advanced Excel techniques but he/she implements them in a very strange manner (named formulas to calculate the Compound Period??). Goes to show that advanced Excel skills and good spreadsheet design do not go hand in hand, something that many people do not realize. Sumwise to the rescue! :)

  2. AdamV

    5th June 2010 at about 12am

    I’m a bit baffled. I get the idea that Sumwise will do some clever things with lookup tables that might be better than excel’s lookups or named ranges. But I think you have chosen not to compare apples and apples here.
    You say the Excel formula is long and unwieldy, with which I agree, but your proposal for an alternative starts with “the key components of the formula should be broken out into separate columns”. I agree, that would be one approach to take using Excel but I don’t see that as part of the argument for a different tool, just a better approach to the model. As for whether (dynamic) named ranges, or a UDF might be a better way to get to some of the component values needed, I’m not sure.

    I suspect the original could be better written, made shorter (but perhaps obfuscated further by doing so), and certainly broken into chunks. How does sumwise help? (by the way, this is an actual question, not a rhetorical snark)

  3. Darren Miller

    8th June 2010 at about 10am

    Adam, thanks for your comment. Solving model complexity is itself a complex issue and there is never just one right answer. In this specific case my view is that the formula should be broken out into separate cells or columns. I agree this is primarily a design issue, not an application issue. Where Sumwise shines relative to Excel and other traditional spreadsheets is in its simple implementation of things that are common but complex in Excel. Things like lookups, conditional sums, dynamic ranges, etc. As we progress towards our public release later this year I’ll get into each of these things in more detail.

Join the discussion

Popup

Register your Interest

Please register to keep up to date with our developments.

Register your Interest

Please register to keep up to date with our developments.

беременость 20 недель