• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel PMT Function

You are here: Home / Excel Functions / Excel PMT Function

The Excel PMT Function returns the regular and constant repayments for a loan or mortgage required to reduce the balance to zero, or an amount you specify. It’s based on a constant interest rate.

Excel PMT Function Syntax

Syntax: =PMT( rate, nper, pv, [fv], [type])
 

Excel PMT Function Arguments

rate Interest Rate Per Payment Period. e.g. if interest is calculated monthly then this would be, roughly*, the annual interest rate (APR) divided by 12.
nper Number of Payments e.g. for a 20-year loan with monthly repayments there would be 240 payments
pv Loan Amount Principal or loan amount i.e. the total amount borrowed, excluding interest.
[fv] Final Balance (target) Optional argument. e.g. if you want to pay off the loan you would enter zero. If omitted, zero is assumed.
[type] Payments Due Optional argument.  0 = end of period, 1 = beginning of period. If omitted, zero is assumed.
 

*For a more accurate calculation of the interest rate you can use the EFFECT Function, which takes into account compounding of interest when interest is calculated daily or monthly etc.

Excel PMT Function Example

Let’s say we borrowed $500,000 over 20 years at 12% interest per annum and we make monthly repayments at the end of each month. Our aim is to repay the loan in full at the end of 20 years.

Excel PMT Function

Notice that the Excel PMT function returns a negative value because this represents payments being made from you to your lender. Alternatively, if you prefer the PMT function return a positive value you can enter the Loan Amount as a negative figure.

Excel PMT Function Calculator

Enter your own values into the PMT Function calculator (grey cells below) to try it out.

Tip: Remember, to enter interest rates with the percentage symbol, or their decimal equivalent. And if payments are monthly, divide your annual Interest Rate by 12.

Limitations

Caution: Banks and other lenders are notoriously complex in the way they calculate interest and repayments on loans. As a result, the repayment amount returned may differ from your bank. For example, there may be additional fees and taxes levied by your bank. The timing of the interest calculation and compounding may not be in line with the repayments etc.

Therefore, use the PMT function as an indicator, but your lender has the final say on what you owe them 😊

Related Functions

 
EFFECT Returns the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.
ACCRINT Returns the accrued interest that gets paid periodically.
FV Calculate the future value of an investment.
NPER Calculates the number of periods to reach an investment goal.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.