Related Function:

The PPMT function calculates the payment on the principal, during a specific period of a loan or investment that is paid in constant periodic payments, with a constant interest rate.

Syntax

=PPMT(rate,per,nper,pv,[fv],[type])

Arguments

Argument Description
rate The interest rate, per period
per Specifies the period and must be in the range 1 to nper
nper The total number of payment periods in an annuity
pv 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 omitted, [fv] defaults to 0 (zero), i.e. the future value of a loan is 0
[type] Optional. Defines whether the payment is made at the start or the end of the period

  0 the payment is made at the end of the period; default if argument is omitted
  1 the payment is made at the beginning of the period

Examples

  A B C D
1 Data      
2 10% Annual interest rate    
3 5 Loan term – years    
4 $20,000 Loan Amount    
5        
6 Formula Result Notes
7 =PPMT(A2/12,1,A3*12,A4) -$258.27 Payment on principle for the first month of loan
8 =PPMT(A2/12,24,A3*12,A4) -$312.59 Payment on principal for the 24th month of loan

Note: In the above example, the payments are made monthly, so

  • the annual interest rate of 10% had to be converted into a monthly rate (=10%/12), and
  • the number of years had to be converted into months (=5*12)

Cash Flow Convention: In line with general cash flow conventions, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any of the supplied arguments are not recognized as numeric values
#NUM! Occurs if the supplied per argument is < 0 or is > the supplied value of nper
Result is much higher or much lower than expected Usually occurs due to failure to convert the rate or the nper to months or quarters, i.e.:

  months = 12 * years   monthly rate = annual rate/12
  quarters = 4 * years   quarterly rate = annual rate/4