How to use PMT Function in Excel

The Excel PMT function is a financial function that returns the periodic payment for a loan. You can use the NPER function to figure out payments for a loan, given the loan amount, number of periods, and interest rate.

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

The PMT function syntax has the following arguments:

  • Rate    Required. The interest rate for the loan.
  • Nper    Required. The total number of payments for the loan.
  • Pv    Required. The present value, or the total amount that a series of future payments is worth now; also known as the principal.
  • Fv    Optional. The future value, or a cash balance you 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.
  • Type    Optional. The number 0 (zero) or 1 and indicates when payments are due.
Set type equal to If payments are due
0 or omitted At the end of the period
1 At the beginning of the period

Example: Let’s look at some Excel PMT function examples and explore how to use the PMT function as a worksheet function in Microsoft Excel:

Example 1:

Syntax:  =PMT(B1/12,B2,B3)

Result: -207406.42

PMT Function in Excel - How to use PMT Function in Excel

Example 2:

Syntax: =PMT(B1/12,B2*12,0,B3)

Result: -9464.85

PMT Function - How to use PMT Function in Excel

Note:

  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.
READ:  How to use TBILLYIELD Function in Excel

Add a Comment

Your email address will not be published. Required fields are marked *