Items you will need
- Loan amount
- Loan annual percentage rate (APR)
- Loan term, in years
- Computer with Excel
Building your own mortgage repayment calculator in Excel takes a little bit of work, but once it is done, you can calculate any repayment scenario you'd like. You can compare mortgage rates side by side, see the effect of different loan terms and even see what happens to the repayment schedule when you make additional principal payments. Unlike Web-based calculators, you can save all your work on your own computer and look at it any time you want without having to reenter all your data.
Calculating Monthly Payment
Open a new Excel worksheet and add labels. In cells A1 to A3, type "Loan Amount," "Loan APR" and "Loan Term" in that order. Skip cell A4, and in cell A5, type "Monthly Payment." Adjust the width of Column A if necessary.
Add the initial loan data. In cell B1, type in the amount of the mortgage or the principal. Convert the APR into decimal form. For instance, 5 percent = 0.05. Add this to cell B2. In cell B3, type in the mortgage term length in years.
Add the formula for the monthly payment to cell B5. In Excel, this is written as "=-PMT(B2/12,B3*12,B1)." The APR is divided by 12 in the formula to obtain a monthly interest rate. The loan term is multiplied by 12 in the formula to convert years to months. The minus sign in front is used to display the payment amount as a positive number. The monthly payment will display in cell B5.
To calculate the interest and principal payments for each payment made on the loan, you can build an amortization table. Start with the monthly payment calculator and add labels for the table. In cells B8 to E8, type in the labels "Beginning Balance," "Interest," "Principal" and "Ending Balance" in that order.
Type "B1" under "Beginning Balance" in cell B9. The initial beginning balance is the initial loan amount. The ending balance will become the new beginning balance from this point on. In cell B10, type in "E9."
In cell C9, under "Interest," type in the equation "=B9*$B$2/12." The APR is converted into a monthly interest rate and applied to the monthly beginning balance. Notice the absolute reference for the interest rate.
In cell D9, under "Principal," type in the equation "=$B$5-C9." The interest payment is subtracted from the monthly payment to obtain the principal payment for that month.
In cell E9, under "Ending Balance,"type in the equation "=B9-$B$5." The monthly payment is subtracted from the beginning balance to obtain the ending balance.
Copy cells C9 to E9 into cells C10 to E10. This represents the first two months. Copy cells B10 to E10 down the column for as many months as you would like to calculate. A 30-year mortgage would be a total of 360 rows.
Calculating Additional Principal Repayment
Add the label "Additional Payment" in cell F8. You can also add a column between the ones you already have and use that instead. The formulas will all adjust themselves accordingly. Fill in the column below it with your additional payments. You can use a different payment in every space or you can copy a single payment down the column.
In cell E9, under "Ending Balance," edit the formula to read "=B9-($B$5+F8)" to account for the extra payment. Copy the new formula down the column. Although it's not necessary for the calculations, you can edit the "Principal" column in the same way.
With the complete calculator, you can now adjust the various values to try different mortgage repayment scenarios.
This calculator can be converted to a biweekly repayment calculator. Everywhere that the interest is used, divide by 26 instead of 12, and everywhere the loan term is used, multiply by 26 instead of 12. This is just a basic format. Once you get used to where the values are being used, you can change around the format to meet your own preferences. If you have an Internet connection, there are also several templates available online, but it is a good idea to do it once yourself so you know how the templates are working. Search for "mortgage" under the "Help" function in Excel to find the mortgage repayment templates.