How to Calculate Mortgage Repayments in 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.

Difficulty: Moderately Challenging

Instructions

Things You'll Need:

• Loan amount Loan annual percentage rate (APR) Loan term, in years Computer with Excel

Calculating Monthly Payment

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

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

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

Amortized Repayment

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

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

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

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

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

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

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

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

12. With the complete calculator, you can now adjust the various values to try different mortgage repayment scenarios.

Tips & Warnings

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

[tgray98]

on 9/8/2009 You have an error in your calculations!
You said the ending balance is "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". The Ending balance is not reduced by the entire payment!! The ending balance should be (E9-D9)....Beginning balance minus the principle payment. Additionally, additional principle payments can be added to the equation as B10=E10-F10.

[londonlondon]

on 8/12/2009 I'm tying to solve a repayment mortgage work problem in excel, but having a bit of trouble. Could you explain how to solve the problem below in excel?


From its pick at the end of 2007, house prices in London have dropped around 15% until now. Believing that this downward trend is about to be reversed, Tom Herbert, a single 26 year-old trying to get on “the property ladder” has identified a 1-bedroom flat he could buy for £200,000. Tom contacted a number of financial institutions and was offered the following mortgage options:

•repayment fixed rate for 2-years of 2.94%. After that period, the rate reverts to the bank’s standard variable rate, which currently is 5%;

•repayment fixed rate for 5-years of 3.45%. After that period, the rate reverts to the bank’s standard variable rate, which currently is 5%;

•interest only mortgage at 4% for the life of the loan. In this instance, you...

[gndcis]

on 7/28/2009 This is an amazing article. I have been looking for something like this for ages. I have now further customized it for my requirements.
Thanks
GD