Mortgage calculator with Excel

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 for the loan.
  • Nper    The total number of periods for the payments for the loan.
  • Pv    The Present Value. It's the value of the mortgage or loan.
  • Fv    The future value. It's optional and the value = 0 most of the time.
  • Type    Does the payment 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)

 

 

 

Permanent link to this article: https://www.excel-exercise.com/mortgage-calculator-with-excel/


Leave a Reply

Your email address will not be published.