PPMT Function in Excel | Financial Formula

The PPMT Function is categorized under Excel Financial functions.  The PPMT function in Excel is used to calculate the principal payment of an investment in a specific payment period based on a series of regular payments and a constant interest rate. The function syntax is:


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


Rate – This is the interest rate per period.
Per – The payment period of interest.
Nper – The total number of payment periods.
Pv – This is the present value of the loan/investment.
Fv (optional) – Specifies the future value of the loan/investment at the end of nper payments.
Type (optional) – This specifies whether the payment is made at the start or the end of the period.

For example, suppose you want to calculate the principal payment made in the 5th payment period of an investment that pays $100 at the end of each month for 5 years, with an annual interest rate of 6%, and a present value of $5000. The formula would be:

=PPMT(6%/12, 5, 5*12, 5000, 0, 0)

The result would be the principal payment made during the 5th payment period, which would be the amount that goes towards reducing the outstanding balance of the investment.

What is the difference between PPMT and PMT?

Whereas the PMT function tells you how much each payment will be, the PPMT function tells you how much of the principal is being paid in any given pay period.