Mortgage Loan Calculator Using Excel
The PMT function within Excel is a financial function that is used to calculate loan payments (it can also be used to calculate the value of investments over time); with the associated IPMT and PPMT functions it is easy to create a worksheet that will calculate an amortization schedule for a mortgage or other loan.
These functions give you the ability to model different loan situations, including additional principal payments, different loan amounts, different interest rates etc. and calculate monthly total payments as well as payments over the life of the mortgage.
This article will provide a step by step guide on how to create a mortgage loan calculator using Microsoft Excel. While the worksheet has been designed in Microsoft Excel 2007, the functionality will be the same on Microsoft Excel 2003.
Creating the Format for the Mortgage CalculatorClick thumbnail to view full-size
Explanation of financial functions used in Mortgage Calculator:
PMT, IPMT & PPMT Functions - for a loan using a constant interest rate and payment:
- PMT calculates the total payment.
- IPMT calculates the interest payment for a given period.
- PPMTcalculates the principal payment for a given period.
Syntax of these functions:
- =PMT(rate, number of payments, present value, [Future Value], [type])
- =IPMT(rate, period, number of payments, present value, [Future Value], [type])
- =PPMT(rate, period, number of payments, present value, [Future Value], [type])
- Rate: the monthly interest rate for the loan.
- Period: the period for which you want to calculate the interest or principal.
- Number of payments: the total number of payments of the loan.
- Present value: the current valuethat the future payments are worth now
- Future Value: the future value you want to attain after last payment. Leaving this out will assume the last value is zero.
- Type: indicates when the payments are due. (0 is end of period, 1 is beginning of period)
The process to create a mortgage schedule in Microsoft Excel is relatively simple although it does use some complex financial functions.
Start off by creating a simple worksheet where the data will be entered and the amortization will be calculated. In this example add the basic variables such as loan amount, number of months, interest rate etc. and the headings for the actual amortization schedule. To do this:
- Create a range with the main loan details: Total Loan, Interest Rate, Months. These are the variables that will allow for modelling of different loan terms.
- Add Total Monthly Payment, Total Payments and Total Interest Paid to this range – these will be calculated later.
- Create the actual schedule by adding heading: Month, Opening Balance, Principal, Interest, Extra Payment, Closing Balance and Total Payment. Once formulas are added, this section will automatically calculate the amortization schedule based on the variables input in the main loan range.
- Add a loan amount, and interest rate and the number of months to the range. This is done to ensure that the formulas are working and can be any value.
- Format the headings and range.
- Add the formula =-PMT(C3/12,C4,C2) to cell C5 (See explanation of PMT on the right) IMPORTANT NOTE: the interest in the main range is an annual interest. Divide this value by 12 to get the monthly interest rate used in the formula)
The basic format is complete. Other items that could be added (see above screenshot for an example of a more complex final worksheet):
- PMI payment
- Monthly Insurance Payment
- Property Taxes
Adding the initial formulas to the Mortgage CalculatorClick thumbnail to view full-size
Now that the initial format is set, the remaining formulas in the summary range and the top row in the amortization schedule can be created. In each cell referenced below, enter the relevant formula:
Totals all the 'Total Payment' rows to give an overall payment made (principal and interest).
Totals all the 'Interest' rows to give an overall interest payment made.
As this is the first payment, this is set as 1.
As this is the first payment, this is set as the loan value.
Calculates the principal payment for this period only.
Calculates the interest payment for this period only.
No value assigned yet.
Calculates the remaining balance of the loan by taking the opening balances and reducing this by principal payments only.
Calculates the total monthly payment (principal plus interest).
Adding the final formulas to the Mortgage CalculatorClick thumbnail to view full-size
The first row of formulas has been created. Now the rest of the formulas can be created. They essentially are the same but check to see if the month is blank (the month checks to see if the prior closing balance is zero and returns blank if it is, otherwise it returns the value of the prior month plus one. In each cell referenced below, enter the relevant formula:
This checks to see if there is a Closing Balance first; then it checks to see if the month prior has a value; if both values or true then it adds one to the prior month's value.
If the value of the Month is not blank then this uses the value from the prior month Closing Balance, otherwise it leaves the cell blank.
If the value of the Month is not blank then this calculates the principal for this period only, otherwise it places zero in the cell.
If the value of the Month is not blank then this calculates the interest for this period only, otherwise it places zero in the cell.
No value assigned yet
If the value of the Month is not blank then this calculates the remaining balance of the loan by taking the opening balances and reducing this by principal payments only, otherwise it places zero in the cell.
If the value of the Month is not blank then this calculates the total monthly payment (principal plus interest, otherwise it places zero in the cell.
Copy these formulas down to row 500. The basic mortgage calculator is now complete. The amortization schedule will automatically recalculate when the values in cells C2, C3 and C4 are amended.
Adding Extra Payments to the Mortgage Calculator
Once the design is complete you can amend any of the loan details to model the amortization; additionally you can add Extra Payments throughout the entire loan. In the example an extra payment has been applied to the loan every month. This extra payment reduces the length of the loan, the total paid and the interest paid. This extra payment can be made at any time during the loan and doesn’t have to be monthly.