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

Share Button

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!

Share Button
The Best Bank Rates
Loading Disqus Comments ...
Loading Facebook Comments ...

14 thoughts on “An Introduction To Compound Interest With Spreadsheets, Part 3: A Simple Mortgage Calculator

  1. Is that really right that a 180k loan would cost you around 450k over the life of the loan? That really doesn’t make me want to buy a house.

  2. That Total Loan cost doesn’t take into account that you will be able to deduct some of the interest from your taxes. Also a home will appreciate over 30 years.

  3. That’s correct, given the assumptions above. Note that I am using an interest rate of 8%, though, and if your credit is okay at all you can get lower than that. For instance, if your rate is 6.25%, your monthly payments are only $1,108.29, which gives you a total cost of just south of $400K. If you want to run other numbers, try building the calculator.

    This is also a good reason to consider paying in cash rather than getting a loan, because with those same payments into a 5.05% savings account, you’ll be able to cut a check in about twelve years (assuming housing prices stay stable).

    As Jason mentioned, this is a simple view. We’re also not including PMI, insurance, and maintenance either.

  4. Yeah I thought the interest rate looked a little high in comparison to what I’ve seen, and I’m still a little young to be in the market, but it is still a little depressing to see that much money get put in to interest. I have a Roth IRA going and I know after five years you can pull the money to put towards your first house downpayment, what do you think about doing that?

  5. Wow, paying that much in interest for a $180K loan is frightening. It is extremely difficult to find any property (including apartments that are condos!) within an hour’s drive of where I live now that go for under $400K. My mother values property highly and is really pressuring me to buy a house, but I’m making $30K a year pre-tax as a graduate student on fellowship and have no idea how I would pay the mortgage and property tax. My possible sources of money: My rent now is $675/month (which gets me a room in a flat with 3 roommates near school); I occasionally get $1000/month from an over-invested 529 plan, if my parents don’t need the money, which currently goes into my investment account; I save $500-700/month in an ING account; and I could squeeze out a few hundred more a month by eating only rice and free food. So, it’s possible, but I’ll be right on the edge and not saving or investing anything, and that really disturbs me. I’m not sure how I can tell my mom that buying a house is going to stress me financially and emotionally when she’s so convinced that it’s a good investment.

  6. You may want to add an “additional principal” column to the spreadsheet. Putting an extra $100 a month on the loan balance will take several years off the length of the loan and decrease the total interest.

  7. Two questions:

    1. How would I add the, “Additional Principal”, coloumn?

    2. How could I adjust the formula to factor in making payments bi-weekly or weekly?

  8. Sergio,

    1. In column g12 enter Additional Principal
    change cell f12 to =B13-(E13+G13)
    And copy all the down. Now if you enter any amount in the g column it will subtract it from the ending balance.

    2. Change cell b8 from =B4*12 to =B4*26 for bi-weekly or =B4*52 for weekly.

    Hope it helps

  9. I added at the top an additional monthly principal field and just added that field to the PMT field. Now when I change the extra amount I add to pay down principal it gets applied to all the columns already created.

    To see how it effects your total interest and loan cost you have to scroll down your columns until you find the month with a negative (or zero) ending balance. Make note of the month number and put that number into the number of payments field (B8).

    Increasing my principal payment by only about 10% of my existing monthly payment shortens my loan term by 7 years and my total interest is reduced by 27%.

  10. Thanks, but that didn’t explain anything.
    What is the continuous compounded interest for $8000 compounded weekly for 1.5 years at 9%?

    P(1+i)^n

    P= 8000
    r= .09
    n = 78 weeks x 1.5 years = 117
    i = r/m = .09/78 = .0011538
    A = ?

    8000(1 + .0011538)^117 = $9155.23
    Annual Interest for 1.5 years is $9155.23.

    Solve that you suckers!

  11. Comment 12:

    To calculate the number of payments automatically based on any additional payments, use the NPER formula.

    Enter “=NPER(B5/12,B7,-B3)” in B8 and the number of payments (and interest and total cost) will be updated automatically.

  12. This calculation spreadsheet is an excellent tool. If you use it work out all the payments and then work out how much you can save by making extra payments you’ll be able to own your home much sooner and save a lot of interest. Always try to pay extra.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>