How to Use the PMT Function in Excel

The PMT function is a powerful financial tool in Excel used to calculate loan payments. It helps you determine how much a customer will pay per period based on interest rate, loan term, and loan amount. In this lesson, you’ll learn how the PMT function works and see how it’s applied to calculate monthly financing payments for HVAC system replacements, making it easy to present clear payment options to customers.

Download the Excel file used in this tutorial:

Download

How to Use the PMT Function in Excel

1. Identify the key inputs required for the calculation

  • Interest rate
  • Loan term (number of periods)
  • Loan amount (present value)
  • Optional inputs: future value and payment timing

The function becomes very straightforward once these inputs are clearly defined.

2. Adjust the interest rate to match the payment frequency

  • If you are calculating monthly payments, convert the annual interest rate using division
  • If you are calculating annual payments, leave the rate as is
  • The key rule is that the rate and periods must always use the same time unit

3. Align the number of periods with the payment structure

  • If the term is already in months, use it directly
  • If the term is in years, convert it to months using multiplication
  • Always match the number of periods to the frequency used for the interest rate

4. Input the loan amount as a negative value

  • The present value represents the loan amount being financed
  • Enter it as a negative number to correctly calculate the payment output
  • This ensures the result is returned as a positive payment value

5. Apply the PMT function

  • Use the PMT function with:
    • Adjusted interest rate
    • Number of periods
    • Present value (loan amount)
  • Optional inputs:
    • Future value, typically assumed to be zero
    • Payment timing, beginning or end of period

By default, payments are assumed to occur at the end of each period

6. Fill the calculation down for multiple scenarios

  • Once the function is set correctly, drag it down to calculate payments across different loans or financing options
  • Ensure all inputs remain consistent across rows

7. Validate consistency across inputs

  • Confirm that:
    • Interest rate frequency matches the number of periods
    • Loan amount is entered correctly
    • Time units are consistent across the entire model

This step prevents the most common PMT errors

8. Calculate total payment over the life of the loan

  • Multiply the periodic payment by the number of periods
  • This gives the total amount paid over the loan term

9. Calculate total interest paid

  • Subtract the original loan amount from the total payments
  • This reveals the total interest cost over the life of the loan

10. Extend the analysis with related financial functions

  • The PMT function is part of a broader set of financial tools
  • Common related functions include:
    • Rate
    • Number of periods
    • Present value
    • Future value

These functions can be combined to model financing scenarios, pricing strategies, and long-term financial planning

Result

You now have a repeatable process to calculate periodic loan payments, evaluate financing options, and break down total cost versus interest over time using Excel’s PMT function

The PMT Function in Excel

Q1. What does the PMT function do in Excel?
 The PMT function calculates the periodic payment required to pay off a loan based on a fixed interest rate, number of periods, and loan amount. It’s commonly used for loans, mortgages, and financing scenarios.

Q2. Why is the PMT function useful for business applications?
 It allows businesses to quickly calculate and present financing options to customers. For example, HVAC companies can show monthly payment amounts for system installations, helping customers make purchasing decisions.

Q3. What inputs are required for the PMT function?
 At a minimum, you need three key inputs:

  • Interest rate (adjusted to the payment frequency)
  • Number of periods (loan term)
  • Present value (loan amount)

These inputs determine the payment amount.

Q4. Why do I need to adjust the interest rate and periods?
 The interest rate and number of periods must match the payment frequency. For example, if you’re calculating monthly payments, the interest rate should be divided by 12 and the number of periods should be in months.

Q5. Why is the loan amount entered as a negative number?
 In Excel financial functions, cash outflows (like loan amounts) are typically entered as negative values. This ensures the payment result is returned as a positive number.

Q6. Can I use the PMT function for personal finance?
 Yes. The PMT function is widely used for personal scenarios such as calculating mortgage payments, car loans, or planning savings goals, making it a valuable tool beyond business use.

Q7. Can I calculate total interest paid using PMT?
 Yes. You can multiply the payment amount by the total number of payments to find the total paid over the life of the loan, and then subtract the original loan amount to estimate total interest paid.

Get more lessons like this
by joining our newsletter

Receive weekly Excel tutorials, tips, and KPIs straight to your inbox

Join Now

Menu

Databoards

Analysis & Development

© 2025 All Rights Reserved.

Facebook Twitter Youtube