Can you believe that you can answer this question using nothing but simple arithmetic in Excel? Addition, subtraction, multiplication. That’s all. No functions, and Excel writes the formulas for you. Yes, I know, you think you have to do some complicated formulas that nobody knows. Seriously, here is all you need to know:
- The interest payment is the interest rate multiplied by the amount owing.
- Whatever is left over from your payment after the interest is paid goes to pay off the amount you owe.
- The monthly interest rate is 1/12 the yearly interest rate.
Let’s say you just got a 30-year home loan for $300,000 at 4% with a payment of $1432 a month. Here’s the house payment spreadsheet to answer that question.
We start with the assumptions at the top. Create names for them so they play nice. I made them blue to remind you that you can change them.
Now we’ll look at what happens month-by-month. This is much more enlightening than just getting an answer from some formula. Each month you’ll see how much you’ll owe, how much interest you’ll pay that month, and how much you’ll put toward paying down the loan that month. Once you get the first two rows, you can AutoFill the whole thing down for the full 360 months of the loan.
The amount owing is the full amount the first month. After that, you’ll owe whatever you owed the previous month minus the amount you paid down the previous month. Get Excel to write the formula for you by typing “=” and then clicking on the numbers you want to use with a “-“ in between. To get the amount of interest paid that month, multiply the amount owing that month by the monthly interest rate, which is the interest rate divided by 12. The paydown is just your monthly payment plus the additional payment minus the interest paid. That’s all there is to it.
You see that in March of 2041, the amount owing becomes red. That means it’s negative; you’ve paid it all off and then some, 3 ½ years early. It turns out that you save over $61,000 by doing this, at a cost (at $100 a month) of about $31,000, a net savings of $30,000. Try $200 a month or more and see what that does.
Bonus: Set the additional payment to zero and the spreadsheet turns into a nice payment table for your loan. See, it really does go to 0 in September 2044. In fact, if you want to figure out the monthly payment on a loan, you can use Goal Seek to find the monthly payment value that makes the last amount owing equal to 0. And you thought you needed some complicated formula to do that!