 Home » Advanced » Mortgage calculator with Excel

# Mortgage calculator with Excel

Last Updated on 22/11/2019 by Frédéric LE GUEN

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)

# Mortgage calculator with Excel

Last Updated on 22/11/2019 by Frédéric LE GUEN

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)