Make a Simple Mortgage Spreadsheet

If you're a spreadsheet nerd, there are few applications more fun than mortgage math! You can project out your mortgage balance every year. See how many payments you'll shave off if you throw extra on the principle every month. See how many payments go away if you throw a tax refund at it one year. There are lots of ways to do it but here's how I set mine up.

First, use the PMT function to find the basic loan payment. This doesn't include taxes or insurance. 

=PMT(rate/12, years * 12, loan amount, 0)

For example, to figure up monthly payments on a 6% loan for 30 years to pay off $200,000:

=PMT(6%/12,30*12,-200000,0) = $1200

That $1200 will be your baseline payment. Of course this does not include taxes, insurance, or any other costs of owning a home (repairs!) but it covers the mortgage. 

Using our same example numbers:

	A	B	C	D
1	Month	Amount	Interest	Payment
2	1/15/2024	$200,000.00	$1,000.00	$1,200
3	=A2 + 365.25/12	=B2 + C2 - D2	=B3 * 6% / 12	$1,200

You can see it's really simple. Every month, I add interest to the loan balance and take off the payment I determined using the PMT formula. If you carry that out 30 years, you'll see that the balance gets down to zero. 

Results:

	A	B	C	D
	Month	Amount	Interest	Payment
1	1/15/2024	$200,000	$1,000	$1,200
2	2/14/2024	$199,800	$999	$1,200
3	3/15/2024	$199,599	$998	$1,200
4	4/15/2024	$199,397	$996	$1,200

Change any "payment" you want to see what will happen if you pay more on the principle. Some facts are obvious... for every extra $100 you pay, you save $6 a year in interest from now until forever. But just to add some fun...

If I change one of the payments to $10000 in the first year, I save over three years of payments! If the $10,000 is in year 15, I save 17 months.

Paying an extra $100 a month ($1300) saves 65 months... over 5 years. That's way better than a single $10,000 payment!

You can change the interest rate (I used 6% in all these examples) or loan amount but you get the idea - now you can line out what's going to happen with your mortgage until you pay it off.