In Excel, you can easily create a mortgage calculator with the PMT function
Explanation of the PMT function
The PMT function calculates a Payment for a loan with a constant interest rate. It needs 5 arguments:
- Rate: The interest rate on the loan.
- Nper (Number of periods): Total number of payments for the loan.
- Pv (Present Value): The amount of the loan or mortgage.
- [optional] Fv (Future Value): The value of the loan when you sell it.
- Type: Payment timing – at period start (1) or end (0).
=PMT(Rate, Nper, Pv, Fv, Type)
Example to calculate the mortgage payments
- Morgage $1,500,000 (Pv)
- 15 years (Nper)
- Interest rate 5% (Rate)
The formula is:
The function returns a negative value because you must pay the financial institution. However, it is easy to transform the result into a positive value by multiplying by -1, or with the ABS function (absolute value).
This figure represents the annual payment—just one payment for the entire year. By multiplying this amount by the number of years, you obtain the total sum you will pay, including the principal and the interest.
But you usually pay a mortgage every month. So you must change the values of the argument, and there are some mistakes to avoid.
Mortgage calculator by month
To calculate your monthly mortgage payment, you must consider 2 updates.
- The number of periods MUST be expressed in months; Number of years * 12
- The interest rate must also be converted in months; Interest rate / 12
The formula is the following
Mortgage simulator
In the following Excel workbook, you can simulate your monthly payment to refund your mortgage.
Remark about the Monthly interest rate
To find the monthly interest rate from an annual rate, you typically need to perform a simple calculation, dividing the annual interest rate by 12 (since there are 12 months in a year). However, when calculating the effective monthly interest rate, you might encounter two different calculations, depending on the context and what exactly needs to be calculated: simple conversion and compounding.
1. Simple Conversion
Calculation: Monthly Interest Rate =Annual Interest Rate/12
Purpose: This method simply divides the annual rate into 12 equal parts, assuming that interest does not compound monthly. It’s straightforward but doesn’t account for the effects of compounding within the year.
2. Compounding Monthly
Calculation: Monthly Interest Rate =(1+annual interest rate)^(1/12)-1
Purpose: This method calculates the monthly interest rate considering the effect of compounding. It finds what monthly rate, when compounded monthly, would equal the annual rate if compounded annually. This accurately represents how interest accumulates over time, especially in contexts like savings accounts or loans where compound interest is applied.
You will find the explanation for the ^(1/12) operation in the article discussing cube roots.
