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.

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.

%

Years

◢ | A | B | C | D |
---|---|---|---|---|

1 | Variable Name | Value | Units | Description |

2 | P_{0} |
$150,000 | $ | Original loan amount. |

3 | i_{a} |
5% | $/$ | Annual interest rate. |

4 | i_{m} |
=B3/12 | $/$ | Monthly interest rate. |

5 | n_{a} |
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 | R_{1} |
$100,000 | $ | Remaining Principal. |

◢ | 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 | d_{k} |
k | R_{k} |
t_{k} |
c_{k} |
o_{k} |
R_{k+1} |

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 SpreadsheetBelow is a table that describes the terms used in the calculator above:

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. |

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:

Variable | Description |
---|---|

P_{0} |
Original loan amount. |

i_{a} |
Annual interest rate. |

i_{m} |
Monthly interest rate. |

n_{a} |
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:

Variable | Description |
---|---|

R_{1} |
The schedule's remaining principal or starting loan balance before payment. For new Loans R_{1} = P_{0} |

t_{1} |
The schedule's first interest payment. |

c_{1} |
The schedule's first principal reduction payment. |

d_{1} |
The schedule's first additional principal reduction payment. |

If the schedule is starting at the first payment of the loan, **R _{1}** will equal

For subsequent months, the generalized formulas are as follows:

Variable | Description |
---|---|

k | The month payment number ascending from the first payment, k = 1, 2, …, n. |

R_{k} |
The remaining principal or loan balance before the k payment.^{th} |

t_{k} |
The k month's interest payment.^{th} |

c_{k} |
The k month's principal reduction payment.^{th} |

d_{k} |
The k month's additional principal reduction payment.^{th} |

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.

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

- Click
**File -> New -> Blank Workbook**. - Adjust the width of column B to 10 and column D to 30 to better display input values.
- To adjust column width right click on the letter corresponding to the column you wish to change.
- Select
**Column Width**from the drop down menu. - Type the desired number into the input cell and click
**Ok**. - 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. - Adjust the formatting of the value cells as follows:
- Change format of the monthly interest value (B4) to a percentage format
**Home Ribbon -> Number -> %**. - Increase decimal format monthly interest value (B4) to show 3 decimal places
**Home Ribbon -> Number -> -.0 to .00 -> click 3 times**. - Change format of the mortgage monthly payment value (B7) to the accounting number format
**Home Ribbon -> Number -> $**. - Confirm pasted table resembles
**Table 1**shown above.

Variable Name | Value | Units | Description |
---|---|---|---|

P_{0} |
$150,000 | $ | Original loan amount. |

i_{a} |
5% | $/$ | Annual interest rate. |

i_{m} |
=B3/12 | $/$ | Monthly interest rate. |

n_{a} |
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. |

R_{1} |
$100,000 | $ | Remaining Principal. |

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.

- Adjust the width of column G, H, I, and J to 12 to better display input values.
- 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.
- Right click anywhere within the highlighted region which should create a drop down menu.
- Select
**Column Width**from the drop down menu. - Type the desired number into the input cell and click
**Ok**. - Copy the contents of Table 6 and paste them into cell F1.
- Copy down cells F3 through K3 until the
**R**is less than zero_{k+1} - Method 1:
- Select cells F3 to K365.
- Press
**Ctrl + D** - 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.
- Method 2:
- Select cells F3 to K3.
- Click the small square symbol at the bottom right of the selected cells and drag down to row 365.
- 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
- Confirm the pasted table resembles that of
**Table 2**shown above. - You now have a complete mortgage amortization schedule!
- 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. |
---|---|---|---|---|---|---|

d_{k} |
k | R_{k} |
t_{k} |
c_{k} |
o_{k} |
R_{k+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 |