# Mortgage calculator with Excel

Last Updated on 11/06/2024

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

## Explanation of the PMT function

The PMT function calculates the PayMenT for a loan for a constant interest rate. This function needs 5 arguments

1. Rate

The interact rate on the loan

2. Nper (Number of periods)

The total number of payment periods for the loan

3. Pv (Present Value)

The value of the mortgage or loan

4. [optional] Fv (Future Value)

It's the value of the good when you will sell it

5. Type

Whether the payment calculation is done at the beginning of the period (1) or at the 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

• 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.

# Mortgage calculator with Excel

Last Updated on 11/06/2024

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

## Explanation of the PMT function

The PMT function calculates the PayMenT for a loan for a constant interest rate. This function needs 5 arguments

1. Rate

The interact rate on the loan

2. Nper (Number of periods)

The total number of payment periods for the loan

3. Pv (Present Value)

The value of the mortgage or loan

4. [optional] Fv (Future Value)

It's the value of the good when you will sell it

5. Type

Whether the payment calculation is done at the beginning of the period (1) or at the 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

• 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.