How to model an unusual mortgage repayment strategy in Excel?

February 14, 2008 5:16 PM

How can I model an unusual mortgage repayment strategy in, say, Excel?

A recent post somewhere in blogland (Lifehacker?) suggested a strategy for paying your mortgage out early. Whenever you make an ordinary 'interest plus principal' payment, make an additional payment equal to your next payment's principal component.

I can find plenty of amortisation templates for Excel - plug in a term, frequency of payment and interest rate andit spits out the interest and principal components for all payments until the loan is paid out.

Some of the templates even allow you to schedule an additional repayment (eg, 'show me what happens if I kick in an extra $1000 a month"). However, I'm having trouble working out how to model makng an additional payment with a different value each month - as the loan amortises (?), interest payments go down and principal goes up.

Any ideas? I pay fortnightly / biweekly, if it matters. (And no, I don't want to hear about whether this makes sound financial sense. I know the opportunity costs - I just want to see the numbers.)

posted by obiwanwasabi to work & money (6 comments total)

Mortgage Professor has spreadsheets for that.
posted by hjo3 at 5:32 PM on February 14, 2008

(Er, they're for monthly, not bimonthly, payments. But it wouldn't be hard to modify the 'sheet.)
posted by hjo3 at 5:42 PM on February 14, 2008

Why reinvent the wheel? You can do what you describe using one of the free mortgage calculators on www.dinkytown.net They have over 250 financial calculators including a whole section for just mortgage related financial what-ifs.
posted by 45moore45 at 5:43 PM on February 14, 2008

The strategy was given in Get Rich Slowly.
posted by crazycanuck at 5:52 PM on February 14, 2008

If you use the Extra Payments on Bimonthly Payment Fixed-Rate Mortgages sheet from hjo3's link, with a few modifications, you can do this.

Every time you make an additional principal payment, all the payments that follow have to be recalculated. If you are basing this period's additional principal payment on the next period's principal payment, you are going to run into a circular reference problem. Here is what you can do to avoid this:

Use Column H to contain the balance of the loan before the extra payment is applied:
Use H14 =MAX($F$6+$F$6*F4/24-B14;0)
and then H15=MAX(F14+F14*$F$4/24-B15;0), copy H15 and paste down to the end.

Calculate the extra payment for the period in Column E using the data is Column H:
Use E14=((IF(H14>(B14-C14);$G$9;F14+C15))-(H14*$F$4/24)), copy E14 and paste down to the end.

You'll get some errors once you hit payments past the point where the mortgage has been paid off, but you can ignore these.

Also, if you want the sheet to calculate for biweekly payments, you'll have to change instances of 24 in all sheet formulae to 26 (and 12 in the header to 13).

(Of course, the short answer is that you pay the mortgage off twice as fast).
posted by ssg at 6:31 PM on February 14, 2008

Thanks ssg - that's exactly what I was after. For some reason my version of Excel likes commas instead of semi-colons, but it all worked perfectly.
posted by obiwanwasabi at 6:48 PM on February 15, 2008