Skip to main content

How to Calculate Monthly Mortgage Payment in Excel

Author Sun Last modified

For many people today, calculating monthly mortgage payments is a common task. In this article, I will guide you through the process of calculating monthly mortgage payments in Excel.

Calculate monthly mortgage payment with formula

Calculate monthly mortgage payment with Excel template
A screenshot of Excel showing options to calculate monthly mortgage payments

Calculate monthly mortgage payment with formula

 

To calculate your monthly mortgage payment, follow these steps:

Step 1: Gather the Required Information:

Before proceeding with the calculation of your monthly mortgage payment, it's essential to gather the necessary information.

Loan Amount (B2)

Annual Interest Rate (B3)

Life Loan (B4)

Number of Payments per Year (B5)

A screenshot displaying the input data for calculating mortgage payments in Excel

Step 2: Calculate the Total Number of Payments Months

In the cell B6, use below formula and press "Enter" key to calculate the total number of payments months.

=B4*B5

A screenshot showing the formula to calculate the total number of payments in Excel

A screenshot showing the calculated total number of payments in Excel

Step 3: Use the PMT Function to calculate the Monthly Mortgage Payment
Pro tip:

The PMT function in Excel is a financial function used to calculate the periodic payment, such as monthly loan or mortgage payments, based on constant payments and a constant interest rate. It's a useful tool for financial planning and analysis. The function requires three main arguments:

  • Rate (required): The interest rate for each period. In this example, it will be =B3/B5.
  • Nper (required): The total number of payment periods. In this example, it will be B6.
  • Pv (required): The present value, or the initial amount of the loan or investment. In this example, it will be B2.

Additionally, you can include two optional arguments:

  • Fv (optional): The future value or a cash balance you want to attain after the last payment is made (usually omitted or set to 0 for loans).
  • Type (optional): Indicates whether the payment is due at the beginning or end of the period (0 or omitted for end-of-period payments, 1 for beginning-of-period payments).

In the cell B7, use below formula and press "Enter" key to get the monthly mortgage payment.

=PMT(B3/B5,B6,B2,0)

A screenshot of the PMT formula being used to calculate monthly mortgage payments in Excel

A screenshot of the monthly mortgage payment calculation result in Excel

Generally, the PMT function returns a negative value, for getting a positive value, please add a minus sign at the front of the PMT function.

=-PMT(B3/B5,B6,B2,0)

A screenshot of the PMT formula modified to return positive values in Excel

Step 4: Calculate the Total Loan Cost

If you want to know how much your entire loan will cost in total, you can use the following formula and press "Enter" key to get the total loan amount.

=B7*B6

// Monthly mortgage payment * total number of payments months

A screenshot showing the formula to calculate total loan cost in Excel

A screenshot of the total loan cost calculation result in Excel

Step 5: Calculate the Total Interest

To get the total interests, use below formula and press "Enter" key.

=B8-B2

// Total loan cost – loan amount

A screenshot showing the formula to calculate total interest paid on a mortgage in Excel

A screenshot showing the total interest calculation result in Excel

Note: If you wish to generate an amortization schedule, similar to the one shown in the screenshot above, you can follow a step-by-step tutorial provided in this guide Create a loan amortization schedule in Excel – A step by step tutorial.
Once you've downloaded the sample, simply click on the "Enable Edit" button when opening the file to allow editing the values as needed.

Calculate monthly mortgage payment with Excel template

 

In Excel, various templates are provided for you, including a monthly mortgage payment calculator. Please follow the steps below to apply an Excel template.

  1. Click "File" > "New".

    A screenshot of the File > New section in Excel for selecting templates

  2. In the search bar in the "New" section, type "mortgage" or other keywords as you need into it, press "Enter" key to search the relative templates.

    A screenshot showing the search bar in Excel for locating mortgage templates

    Then the relavant templates are list.

    A screenshot displaying a list of mortgage templates available in Excel

  3. Double-click on the template that you need.

    A screenshot of a selected mortgage template in Excel

Now the template is created and opened.

A screenshot of an opened mortgage payment calculator template in Excel

The insights shared above outline methods to calculate monthly mortgage payments in Excel. I trust this information serves you well. For more game-changing Excel strategies that can elevate your data management, explore further here.

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
{# article_3824_afterDisplayContent #}

Table of contents

Kutools Makes Your Work Easier
--300+ features, experience 30-day free trial now. 👈

Including 40+ Practical Formulas (Calculate age based on birthday ...) 12 Text Tools (Add Text, Remove Characters ...) 50+ Chart Types (Gantt Chart ...) 19 Insertion Tools (Insert QR Code, Insert Picture from Path ...) 12 Conversion Tools (Numbers to Words, Currency Conversion ...) 7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...) ... and more.