An Introduction To Compound Interest With Spreadsheets, Part 3: A Simple Mortgage Calculator

Regular compound interest is (basically) the way most loans and savings accounts work, including home mortgages. Here, we’re going to use a spreadsheet to calculate a home mortgage payment estimator (and even a full payment schedule) using the principles of compound interest.

Fire up your spreadsheet and enter the following information into cells:
In A1, enter Simple Mortgage Calculator
In A3, enter Amount Borrowed
In A4, enter Length (in Years)
In A5, enter Interest Rate
In A7, enter Monthly Payment
In A8, enter Number of Payments
In A9, enter Total Interest
And in A10, enter Total Loan Cost

That’s a lot of labels. We’re going to use the first three numbers to calculate the last four, so enter some dummy values in B3, B4, and B5. I used $180,000, 30, and 8.00%. Then we begin setting up the calculations:

Compound Interest 11

The piece in B7 looks really strange. This is what should be typed in:

=-PMT(B5/12;B4*12;B3)

If you’re using Excel instead of OpenOffice, it will look just a bit different:

=-PMT(B5/12,B4*12,B3)

This PMT function is a part of the spreadsheet that calculates how much a monthly payment will be for you. The interest on each payment will be 1/12th of the annual rate (hence the B5/12 part) but you’ll be making 12 payments a year (hence the B4*12 part). The number that appears here is the actual cost of a monthly payment given the terms you set up in B3, B4, and B5.

The other three numbers are fairly straightforward:
To calculate the number of payments (in cell B8), enter =B4*12
To calculate the total interest paid over the loan’s lifetime (in cell B9), enter =(B7*B8)-B3
To calculate the total cost of the loan (in cell B10), enter =B7*B8

In the end, you’ll get something that looks like this:

Compound Interest 12

You can change the first three numbers however you want to look at various different possibilities. Want to make it even cooler? In cell A13, write Month 1, then click and drag the black square down from that cell until you can see month 360. Then, in B12 through E12, add these labels: Starting Balance, Payment, Interest Paid, Principal Paid, and Ending Balance. Yep, we’re going to see what exactly each payment is doing.

Compound Interest 13

To set up the numbers, you’re going to have to enter some more formulas into the first couple rows of this little table, but after that, you can just drag down to fill in the rest.

In B13, in order to set the starting balance for the first month, you want it equal to what you entered above, so enter =B3
In C13, you’ll want it to always equal the payment calculated above, so enter =$B$7
In D13, you want to see how much interest you paid that month, which would be 1/12th of the annual interest (set permanently in B5) on the current balance, so enter =B13*$B$5/12
In E13, you’ll want the amount of principal paid this month, which is just the difference between the total payment and the interest paid, so enter =C13-D13
In F13, you’ll want to calculate the balance at the end of the month, which is the starting balance minus the principal paid, so enter =B13-E13
And finally, in B14, you just want the same starting value as the ending value from last month, so enter =F13

You can click on the cells B14, C13, D13, E13, and F13 and drag the black square in the lower right of each one all the way down until it lines up with Month 360. Don’t worry about weird values until you’ve done all five columns.

When you’re done, you’ve got a full mortgage amortization calculator – and you understand completely how the math works! Be sure to save this file for later – it’s useful!

Compound Interest 14

Now that you’ve assembled your own mortgage calculator, you can see that the possibilities of using spreadsheets to do personal finance calculations are endless. Good luck!

Loading Disqus Comments ...