Mortgage Amortization Calculator and Guide

Welcome to the Numplexis mortgage amortization calculator and guide! The following contents contain a mortgage amortization calculator, it's complementary excel spreadsheet, a step by step walkthrough of the formulas used by the calculator, and a guide to making the calculator from a blank workbook. If you are unfamiliar with how to use excel it is recommended to learn the basics by following the Goodwill Community Foundation's Excel 2016 Basics videos before tacking the Excel guide.

Amortization Schedule Calculator

To use the calculator, edit the input boxes below. Be sure to set the remaining principal equal to the original loan amount for new loans.

P0 Initial Loan Amount $
ia Annual Interest Rate
%
na Loan Term
Years
R1 Remaining Principal $
dk Addt'l Monthly Pmt. $
Table 1. Mortgage Payment Summary
A B C D
1 Variable Name Value Units Description
2 P0 $150,000 $ Original loan amount.
3 ia 5% $/$ Annual interest rate.
4 im =B3/12 $/$ Monthly interest rate.
5 na 30 Years Term of the loan in years.
6 n 360 Months Term of the loan in months.
7 A =(B4*B2*(1+B4)^B5) / ((1+B4)^B5-1) $ Mortgage monthly payment.
8 R1 $100,000 $ Remaining Principal.

Table 2. Mortgage Amortization Schedule
F G H I J K L
1 Addt'l Pmt. Pmt. # Pre Pmt. Prin. Int. Pmt. Prin. Pmt. Tot. Pmt. Post. Pmt. Prin.
2 dk k Rk tk ck ok Rk+1

Excel Amortization Schedule Calculator

The button below is the download link to the excel version of the amortization calculator shown above. For a guide on how to create elements of the spreadsheet from a blank sheet, consult the sections below.

Download Excel Amortization Spreadsheet

Calculator Background Information

Below is a table that describes the terms used in the calculator above:

Table 3. Mortgage Calculator Term Definitions
Term Definition
Amortization Schedule A comprehensive table of loan payments, expressly presenting the amount of principal and interest paid in each loan payment until the loan balance has been fully repaid. Most often the table is calculated for periodic monthly payments.
Interest Money paid regularly at a particular rate for the use of money lent, or for delaying the repayment of a debt. One can think of interest as the regular fee proportionate to the amount of money left to be repaid by the borrower.
Principal A sum of money lent or invested on which interest is paid. You can think of this as the remaining loan balance after each payment. A principal payment is one that directly reduces the balance owed to the person or institution who loaned you the money.

Calculator Formulas

In the case of the mortgage amortization calculator, the first step is to calculate the fixed monthly payments of the mortgage. The formula for calculating this periodic payment is as follows:

img not found
Figure 1. Formulas for fixed monthly payment and monthly interest rate

Table 4. Mortgage Calculator Variable Descriptions for Figure 1 Formulas
Variable Description
P0 Original loan amount.
ia Annual interest rate.
im Monthly interest rate.
na Term or duration of the loan in terms of years.
n Term or duration of the loan in terms of months.
A Mortgage monthly payment. The combined interest and principal reduction portions.


Once the monthly payment A is calculated the first interest and principal payments can be separately calculated by the formulas:

img not found
Figure 2. Formulas for First Month Interest and Principal Reduction Payments

Table 5. Variable Descriptions for Figure 2 Formulas
Variable Description
R1 The schedule's remaining principal or starting loan balance before payment. For new Loans R1 = P0
t1 The schedule's first interest payment.
c1 The schedule's first principal reduction payment.
d1 The schedule's first additional principal reduction payment.

If the schedule is starting at the first payment of the loan, R1 will equal P0. In the case of a preexisting loan, one which has been paid for any length of time, R1 will equal the remaining loan principal, some value less than P0. This freedom to set R1 independent of P0 is particularly useful for simulating paydown strategies of a loan regardless of how long you have had it.

For subsequent months, the generalized formulas are as follows:

img not found
Figure 3. Generalized formulas for monthly interest and principal reduction payments

Table 6. Variable Descriptions for Figure 3 Formulas
Variable Description
k The month payment number ascending from the first payment, k = 1, 2, …, n.
Rk The remaining principal or loan balance before the kth payment.
tk The kth month's interest payment.
ck The kth month's principal reduction payment.
dk The kth month's additional principal reduction payment.

Spreadsheet Documentation

If you are creating a spreadsheet to solve a problem, you may wish to share it with coworker or need it years later. To address these possibilities it is best to add descriptions and figures to the sheet so anyone using it will understand what the spreadsheet is accomplishing and how. The below steps follow documentation best practices that you may find beneficial with any spreadsheet application.

Excel: Fixed Payment Calculator

Follow the below instructions to create the portion of the excel amortization sheet that solves for the mortgage monthly payment.

  1. Click File -> New -> Blank Workbook.
  2. Adjust the width of column B to 10 and column D to 30 to better display input values.
    1. To adjust column width right click on the letter corresponding to the column you wish to change.
    2. Select Column Width from the drop down menu.
    3. Type the desired number into the input cell and click Ok.
  3. Copy the table 4 just below the table tag and paste it into cell A1 (the top left cell of the spreadsheet). Warning: The formulas will not function properly if the table is pasted into any other cell.
  4. Adjust the formatting of the value cells as follows:
    1. Change format of the monthly interest value (B4) to a percentage format Home Ribbon -> Number -> %.
    2. Increase decimal format monthly interest value (B4) to show 3 decimal places Home Ribbon -> Number -> -.0 to .00 -> click 3 times.
    3. Change format of the mortgage monthly payment value (B7) to the accounting number format Home Ribbon -> Number -> $.
  5. Confirm pasted table resembles Table 1 shown above.
Table 7. Mortgage Fixed Payment Excel Input Table (copy below and paste into cell A1)
Variable Name Value Units Description
P0 $150,000 $ Original loan amount.
ia 5% $/$ Annual interest rate.
im =B3/12 $/$ Monthly interest rate.
na 30 Years Term of the loan in years.
n =B5*12 Months Term of the loan in months.
A =(B4*B2*(1+B4)^B6) / ((1+B4)^B6-1) $ Mortgage monthly payment.
R1 $100,000 $ Remaining Principal.

Excel: Amortization Schedule Calculator

Now that the monthly payment is established, we may create the amortization schedule detailing each monthly payment. Follow the instructions below to create the table.

  1. Adjust the width of column G, H, I, and J to 12 to better display input values.
    1. To adjust column width, left click the "F" representing column F, hold the shift key, then left click on the "I". This will highlight the 4 columns.
    2. Right click anywhere within the highlighted region which should create a drop down menu.
    3. Select Column Width from the drop down menu.
    4. Type the desired number into the input cell and click Ok.
  2. Copy the contents of Table 6 and paste them into cell F1.
  3. Copy down cells F3 through K3 until the Rk+1 is less than zero
    1. Method 1:
      1. Select cells F3 to K365.
      2. Press Ctrl + D
      3. It is recommended to keep the #N/A rows as they add flexibility if more payments are needed after adjusting the input values in columns B and F.
    2. Method 2:
      1. Select cells F3 to K3.
      2. Click the small square symbol at the bottom right of the selected cells and drag down to row 365.
      3. The values in column F may increment by 1 ($50,$51,$52...). If this is the case the user may have all values mimic cell F3. To do this, select cell F4 and set it equal to F3 in the formula bar (=F3). Once the cell is entered re-select cell F4 and double click the square symbol in bottom right corner of the cell to propagate the equation to the bottom of the amortization schedule. Now the whole column in the schedule will mimic the value set in cell F3
  4. Confirm the pasted table resembles that of Table 2 shown above.
  5. You now have a complete mortgage amortization schedule!
  6. Feel free to edit the additional payment values in column F. These can be used to project how additional principal payments will effect the loan pay down rate. The larger the additional payment value, the fewer loan payments necessary to pay down the loan principal, and thus the less interest paid over the duration of the loan. Note that each row in column F can be individually changed as you see fit within the Excel file.

Table 8. Header and First Row of Mortgage Amortization Schedule (copy below and paste into cell F1)

Addt'l Pmt. Pmt. # Pre Pmt. Prin. Int. Pmt. Prin. Pmt. Tot. Pmt. Post. Pmt. Prin.
dk k Rk tk ck ok Rk+1
$50.00 =IF( ISNUMBER(G2), G2+1, 1) =IF(K2 <=0, NA(), IF( ISNUMBER(H2), K2, $B$7)) =H3*$B$4 =IF(I3=0, 0, $B$6-I3+F3) =I3+J3 =H3-J3