How to Calculate Mortgage Payment in Excel: A Step-by-Step Guide 'calculate mortgage payment in excel'

Calculating mortgage payments can be a complex task, but with Microsoft Excel's PMT function and IPMT formula, you can easily determine the amount of principal and interest (P&I) payment for a given interest rate, loan amount, and term. In this article, we will explore how to calculate the interest portion of a mortgage payment in Excel.

To start, enter the following formula in Cell C5: =PMT(rate, nper, pv). This function calculates the monthly payment for a loan or mortgage based on periodic, constant payments and a constant interest rate. The arguments for this function are:

* Rate: The annual interest rate as a decimal.

* Nper: The number of periods (in this case, 12 months per year).

* Pv: The present value of the loan or mortgage.

Now, hit Enter and see the total number of payments. In this case, Cell C7 contains the number of years, and for monthly payments, you need to pay 12 times a year. To do so, select Cell C8 and type the formula below: =12*C7.

Initially, type the below formula in Cell C5:

=PPMT(rate, nper, pv)

The PPMT function calculates the principal portion of the mortgage payment. However, we need to find the total number of payments. This function allows users to easily determine the amount of the P&I payment for a given interest rate, loan amount, and term.

To find out the interest portion of our mortgage payment, follow these steps:

1. Enter the IPMT formula in Cell C9: =IPMT(rate, nper, pv)

The IPMT function returns the interest amount as a number.

2. The IPMT function calculates interest payment for a given investment based on periodic, constant payments and a constant interest rate.

All the arguments mentioned here are similar to the one for the PPMT function (explained earlier in this article).

This time, we will calculate the Interest portion of the mortgage payment using the IPMT formula:

=IPMT(0.05/12, 8*12, -500000)

In this example, the interest rate is 5% per annum, and there are 8 years to repay the loan.

Step 3: Apply IPMT Function to Determine Interests

As a result, the above formula will return the below output:

$1323.41

This means that out of $1696.41 (monthly payment of the mortgage), $1323.41 is the principal amount.

By dividing the interest rate by 12, we can convert the annual interest rate to a monthly interest rate.

In this example, we have divided the interest rate by 12:

0.05 / 12 = 0.00417 (monthly interest rate)

Now you can use the IPMT formula to calculate the interests over the 8 periods.

By following these steps and using Excel's PMT function and IPMT formula, you can easily calculate your mortgage payment in Excel and determine the interest portion of your monthly payment.

Calculate mortgage payment in excel