Loan Calculations
Compare Loan Payment Options
in Excel
Mortgage lenders give you many options for
your interest rate and amortization period. This workbook helps you to choose from among
them.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

(You
can download the Loan Payment Options workbook here.)
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
loanpayment 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.
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 endoftheperiod 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.
You
can download the Loan Payment Options workbook here.
