When you’re comparing your payment options for mortgage loans, your choices can be overwhelming…
When in St. Ives, I found a home,
Then looked at seven kinds of loans;
But each loan had seven plans;
Each plan had seven terms;
Each term had seven rates;
With all those rates, terms, plans, and loans,
I changed my mind and rented.
–© Charley Kyd, 1982
One way to reduce this confusion is to analyze your loan-payment options in Excel. I recently discovered a worksheet like this that I created when I bought my current home. I’ve dressed it up a little, and updated the numbers. You’ll see images from it below.
(You can download the workbook at this link.)
My Initial Loan Analysis
Initially, I set up this loan calculator, which let me compare the two items that I cared most about: the monthly payments and the total amount of interest paid.
I entered the loan amount in cell C3 and assigned the name LoanAmt to that cell. To do so, I selected the range B3:C3, pressed Ctrl+Shift+F3 to launch the Create Names dialog, made sure that only Left Column was checked, and then chose OK.
Because I wanted column C to be narrower than would have been necessary to display that large number, I merged cells C3 and D3. To do so, I selected the range C3:D3, pressed Ctrl+1 to launch the Format Cells dialog, chose Merge Cells in the Alignment tab, and then chose OK.
To find the first payment amount I used this formula in the cell shown:
C5: =-PMT($B5/12,$A5*12,LoanAmt)
The PMT function has this syntax…
=PMT(rate,nper,pv,fv,type)
…so in cell C5…
- rate is the annual rate in cell B5 divided by 12 to find the monthly rate.
- nper is the number of years in cell A5, multiplied by 12 to find the number of monthly amortizing periods.
- pv is the loan amount.
- and the other two arguments are optional and can be ignored.
And to find the total interest paid, I used this formula in the cell shown:
D5: =$C5*($A5*12)-LoanAmt
Here, the total interest paid is equal to the total number of payments over the life of the loan, less the loan amount.
Then I copied the range C5:D5 to cell C6.
My Expanded Loan Analysis
As I searched for more options for my mortgage, I found myself comparing many different results. So, rather than writing them down, I added four tables to my worksheet.
This figure shows the first table. Here, I entered a variety of interest rates in row 11, and a variety of amortization periods in column A.
I highlighted the middle values as the primary choice, with the other values as options to consider.
Here’s the formula for the cell shown:
B12: =-PMT(B$11/12,$A12*12,LoanAmt)
This formula uses the same logic as the previous PMT formula. I copied it to the range B12:F16.
Next, I wanted to compare the payment options by looking at the total amount of interest paid for each option, as shown here.
In doing so, I wanted to set up the ability to test my assumptions about how long I might live in the house, a period that would be different than the amortization period.
So the Years Loan Life cell, cell M9, contains that assumed number of years. But I started with the value of 99 years, which would allow me to ignore the loan life setting when I wanted to. (You can see how this works in the following formula.)
Here’s the formula for the cell shown:
I12: =-CUMIPMT(I$11/12, $H12*12, LoanAmt, 1, MIN($M$9,$H12)*12, 0)
The CUMIPMT function finds the cumulative interest for a specified period. Here’s its syntax:
=CUMIPMT(rate,nper,pv,start_period,end_period,type)
So in cell I12…
- rate is the annual rate in cell I11 divided by 12
- nper is the number of years in cell H12 multiplied by 12
- pv is the LoanAmt
- start_period is 1, because I was looking at the beginning of each loan
- end_period is equal to either the loan life in years or the amortization period in years—whichever is least—multiplied by 12.
- type is equal to 0, which Excel tells us is the setting for an end-of-the-period calculation.
I copied this formula to the range I12:M16, and then tested it by changing the loan life to 15 years, as shown here.
As you can see, the difference in interest rates is less significant for amortizing periods of 25 and 30 years compared to the previous figure. But even so, if I were to amortize the loan over 30 years, while paying it off in 15 years, that choice would cost me the difference between 76,169 and 115,806, or nearly $40,000.
Finally, in my analysis, I wanted to be able to see how my monthly payments would differ as both my interest rates and amortization periods changed. So I set up these two tables:
In the first table for example, if I had a 15 year loan, my payments would fall by about $18 per month if I could reduce my interest rate from 3.125% to 3%.
And in the second table, if my loan were at 3%, my payments would fall by $807 if I could increase the term of my loan from 15 years 30 years.
The formulas in these tables merely show the differences between various cells in the table titled Monthly Payment Options above.
A final warning…Don’t rely on this worksheet exclusively to choose a specific loan. Its calculations and assumptions might not match your specific circumstances. So be sure to reconfirm the workbook’s key assumptions and payment calculations with your lender or financial adviser.
