Excel > Functions

# PPMT Function

The Excel PPMT function is used to calculate the principal payment made in a period of an investment. Where as IPMT calculates the interest paid in a period of an investment, PPMT relates to the amount paid that comes of the balance.

The syntax for the PPMT function is:

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

Argument

Purpose

rate

The interest rate per period

per

The period for which you want to find the principal amount paid. It must be entered in the range 1 to nper

nper

The number of payment periods during the lifetime of the loan. If payments are monthly for 5 years, this would be entered 5*12 to calculate 60 payment periods

pv

The present value, or current amount of the loan or investment.

fv

The future value, or amount after the last payment is made.

FV is optional and if omitted the value is assumed to be 0 i.e. the future value of a loan will be 0

type

When the payments are due. It can be entered as 1 or 0 and is optional. If omitted the value is assumed to be 0

• 0 - Payments are made at the end of the period
• 1 - Payments are made at the beginning of the period

The examples below show the PPMT function being used to return the amount paid as principal on a loan using different parameters.

Function

Result

=PPMT(C4/12,D4*12,B4*12,-A4)

£405.84

Amount paid as principal in the January of the 5th year of a 20 year loan

=PPMT(C4,D4,B4,-A4,0,1)

£4,482.36

Amount paid as principal in the 5th year of a 20 year loan, with a future value of 0 and payments made at the beginning of each year