**In Excel, you can easily create a mortgage calculator with the PMT function**

Table of Contents

## Explanation of the PMT function

**PMT** calculates the **P**ay**M**en**T** 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)