How to Calculate Mortgage in Excel (2 Easy Steps)

Are you looking for an easy way to calculate your mortgage payments in Excel? Look no further! One of Excel’s many functions is the PMT function, which calculates loan or mortgage payments with a fixed interest rate.

In this article, we’ll explain two simple steps to help you calculate your mortgage payments in Excel quickly and easily using the PMT function. Let’s get started!

Calculate Mortgage with the PMT Function

The PMT function is used to calculate the payments on a loan with a fixed interest rate. It requires the user to input the interest rate, the number of payments, and the loan amount. The PMT function can also consider additional factors such as down payments and other fees.

The formula for the PMT function is =PMT(rate, nper, pv, [fv], [type]).

Here, the rate is the interest rate per period; nper is the total number of payment periods; and pv is the present value or loan amount. fv and type are optional parameters that stand for future value and period type (0 means payment at the end of the period, and 1 means payment at the beginning of the period).

To use the PMT function, follow these step-by-step instructions:

  1. Open a new Excel sheet and input the loan amount, interest rate, and loan periods into separate cells. If there is a down payment, subtract that number from the loan amount to get the payable loan amount.
  2. Enter “=PMT (” into a separate cell, followed by your inputs in order. Be sure to separate each value with a comma and enclose them in parentheses.

In our case, the PMT function formula would look like this: =PMT (F3/12, F4*12, F7). Press Enter and the result will be the number of your monthly payments.

And there you have it! You’ve successfully calculated your loan payments using the PMT function.


The PMT function is an incredibly helpful tool in Excel for calculating loan payments. With its advanced capabilities and flexibility, the PMT function can help you accurately calculate the payments for any loan. It is an easy and efficient way to figure out your monthly payment amounts, taking into account interest rates and other factors.

Hopefully, this tutorial has given you the confidence to use the PMT function for all your loan calculations in Excel!