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.
|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.|
Table 2. Mortgage Amortization Schedule
|1||Addt'l Pmt.||Pmt. #||Pre Pmt. Prin.||Int. Pmt.||Prin. Pmt.||Tot. Pmt.||Post. Pmt. Prin.|
Below is a table that describes the terms used in the calculator above:Table 3. Mortgage Calculator Term Definitions
|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:
|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:
|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:
|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.|
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.
|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.|
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.
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.|
|$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|