Skip to main content

How to Calculate Monthly Mortgage Payment in Excel

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

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)

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

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)

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)

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

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

Note: If you wish to generate an amortization schedule, similar to the one shown in the screenshot below, 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.

  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.

    Then the relavant templates are list.

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

Now the template is created and opened.

The insights shared above outline methods to calculate monthly mortgage 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

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

Description

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.
Comments (1)
No ratings yet. Be the first to rate!
ulaliaulalia
ulaliaulalia
This comment was minimized by the moderator on the site
Very good article, I was able to calculate the company's rent due thanks to your article. Besides, it also synthesizes sonnerie gratuite in a reasonable way.
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations

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.