Earn your certification as a Financial Modeling & Valuation Analyst (FMVA)™. Register today!

PMT Function

Calculates the payment for a loan or investment with constant payments and fixed interest rate

What is the PMT Function?

The PMT function is categorized under financial Excel functions. The function helps calculate the total payment (principal and interest) required to settle a loan or an investment with a fixed interest rate over a specific time period.

 

Formula

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

 

The PMT function uses the following arguments:

  1. Rate (required argument) – The interest rate of the loan.
  2. Nper (required argument) – The total number of payments for the loan taken.
  3. Pv (required argument) –  The present value or total amount that a series of future payments is worth now. It is also termed as principal.
  4. Fv (optional argument) – The FV or a cash balance we want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
  5. Type (optional argument) – The security’s price. It is the type of day count basis to use. The possible values of basis are:

 

PMT Function

 

How to use the PMT Function in Excel?

As a worksheet function, the PMT function can be entered as part of a formula in a cell of a worksheet. To understand the uses of PMT, let us consider an example:

 

Example 1

Let’s assume that we need to invest in such a manner that after two years, we’ll receive $75,000. The rate of interest is 3.5% per year and the payment will be made at the start of each month. The details are:

 

PMT Function - Example 1

 

The formula used is:

 

PMT Function - Example 1a

 

We get the results below:

 

PMT Function - Example 1b

 

The above function returns PMT as $3,240.20. It is the monthly cash outflow required to realize $75,000 in two years. In this example:

  • The payments into the investment are on a monthly basis. Hence, the annual interest rate is converted to a monthly rate. Also, we converted the years into months: 2*12 = 24.
  • The [type] argument is set to 1 to indicate that the payment of the investment will be made at the beginning of each quarter.
  • As per the general cash flow convention, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers.
  • As the value returned is negative, it indicates an outgoing payment is to be made.
  • The value 3,240.20 includes the principal and interest but no taxes, reserve payments, or fees that are sometimes associated with loans.

 

Few things to remember about the PMT Function:

  1. #NUM! error – Occurs when:
    1. The given rate value is less than or equal to -1.
    2. The given nper value is equal to 0.
  2. #VALUE! error – Occurs when any of the arguments provided are non-numeric.
  3. When calculating monthly or quarterly payments, we need to convert annual interest rates or the number of periods to months or quarters.
  4. If we wish to find out the total amount that was paid for the duration of the loan, we need to multiply the PMT as calculated by nper.

 

Click here to download the sample Excel file

 

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!