Excel Mortgage Formula: Mastering the Basics for Easy Calculations

Calculating your mortgage payments can be a daunting task, but with the right tools, it’s a breeze. Excel has a built-in formula that can help you quickly and easily determine your monthly mortgage payments. All you need to know is the loan amount, interest rate, and the number of periods (usually months) for repayment. Let’s dive into how to use this handy formula.

Step by Step Tutorial on Using an Excel Mortgage Formula

Before we get into the nitty-gritty, let’s understand what we’re trying to achieve here. By following these steps, you will learn how to use Excel’s PMT function to calculate your monthly mortgage payments.

Step 1: Open Excel and Set Up Your Spreadsheet

Start by opening Excel and setting up your spreadsheet with the necessary information.

Make sure your spreadsheet has separate cells for the loan amount, interest rate, and the total number of periods. It’s good practice to label each cell so you don’t get confused later on.

Step 2: Input the PMT Function

In a new cell, input the PMT function to start the calculation process.

Type “=PMT(” into the cell where you want your monthly payment to appear. This is the beginning of the Excel formula you’ll use to calculate your mortgage.

Step 3: Enter the Interest Rate

Add the interest rate to the PMT function.

After “PMT(“, click on the cell where you’ve input your annual interest rate. You’ll need to divide this rate by 12 (the number of months in a year) to get your monthly interest rate. Don’t forget to add a comma after the interest rate.

Step 4: Input the Number of Periods

Now, add the total number of periods to the formula.

Click on the cell that holds the number of periods over which the loan will be repaid. This is typically the loan term in months. Again, add a comma after this number.

Step 5: Include the Loan Amount

Complete the formula by adding the loan amount to the PMT function.

Click on the cell with your total loan amount and close the function with a parenthesis. Remember, this number should be negative since it’s money you’re borrowing.

After completing these steps, Excel will automatically calculate your monthly mortgage payment. You’ll see the amount appear in the cell where you entered the PMT function.

Tips on Using an Excel Mortgage Formula

  • Always double-check your input values to ensure accuracy.
  • Remember to convert annual rates to monthly rates by dividing by 12.
  • Make sure the loan amount is entered as a negative number.
  • Use the absolute value if you want to see the payment as a positive number.
  • Consider using other related functions like IPMT and PPMT for more detailed breakdowns of interest and principal payments.

Frequently Asked Questions

What if I want to calculate bi-weekly payments instead of monthly?

To calculate bi-weekly payments, you will need to adjust the interest rate and the total number of periods to reflect the bi-weekly schedule.

Can I use the Excel mortgage formula for an adjustable-rate mortgage?

The PMT function works best for fixed-rate mortgages. For adjustable-rate mortgages, you would need to update the interest rate in the formula each time it changes.

How do I account for extra payments or lump-sum payments?

Extra payments can be accounted for by reducing the loan amount by the extra payment amount or using additional functions like PPMT to break down each payment individually.

Is there a way to include property taxes or insurance in the mortgage calculation?

While the PMT function itself doesn’t include taxes or insurance, you can add these amounts to the calculated mortgage payment manually after using the PMT function.

What happens if I enter the wrong values in the formula?

Entering incorrect values will result in inaccurate calculations. Always double-check your inputs and the final payment amount.


  1. Open Excel and set up your spreadsheet.
  2. Input the PMT function in a new cell.
  3. Enter the interest rate.
  4. Input the number of periods.
  5. Include the loan amount.


Using Excel to calculate your mortgage payments can save you a lot of time and hassle. It might seem a bit confusing at first, but once you get the hang of it, it’s quite straightforward. Remember, the key to a successful calculation is accurate data entry and a clear understanding of the formula. So, go ahead and give it a try. Don’t let the numbers scare you—Excel’s got your back. And once you’ve mastered the excel mortgage formula, you’ll feel like a finance pro, ready to tackle any number that comes your way.

Join Our Free Newsletter

Featured guides and deals

You may opt out at any time. Read our Privacy Policy