In Excel, you can easily create a mortgage calculator with the PMT function
Explanation of the PMT function
PMT calculates the PayMenT for a loan for a constant interest rate.
The arguments of the function are:
- Rate The interest rate on the loan.
- Nper The total number of payment periods for the loan.
- Pv The Present Value, the value of the mortgage or loan.
- Fv The future value. It's optional and the value = 0 most of the time.
- Type Whether the payment calculation is done at the beginning of the period (1) or at the end (0)
Example to calculate the mortgage payments
The amount of your mortgage is 50,000$ for a period of 15 years and the interest rate is 4%.
The formula is:
=PMT(4%;15;50000) => -4 497,06 €
The function returns a negative value because you have to pay the financial institution. But it is easy to return a positive value either by multiplying by -1 or with the ABS function (absolute value).
=PMT(4%;15;50000) *-1
or
=ABS(PMT(4%;15;50000))
This result is the annual refund (1 payment for the whole year). But usually you refund a mortgage monthly and the value for the arguments of the function is different.
Mortgage calculator by month
To return the value to refund by month, you must convert the value of the interest rate and the period in month
Convert the period in month
This conversion is very easy to do. You just have to multiply the number of years by 12.
=Number of years*12
Convert the annual interest rate in month
This is the hardest part of the calculation. In fact there is 2 situations
- The interests are calculated at the end of the period
- The interests are running over the period
If the interest are calculated at the end of the period, the conversion is really easy
=annual interest rate/12
If the interests are calculated over the period (common situation) the formula is more complex.
=(1+annual interest rate)^(1/12)-1
Result for the monthly value
By replacing the previous formulas in the arguments of the function
=PMT((1+4%)^(1/12)-1;15*12;50000)
