The Excel PPMT Function

Related Functions:

Function Description

The Excel 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.

The syntax of the function is:

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

Where the arguments are as follows:

rate	-	The interest rate, per period.
per	-	The period for which the payment on the principal is to be calculated (must be an integer between 1 and nper).
nper	-	The number of periods over which the loan or investment is to be paid.
pv	-	The present value of the loan/investment.
[fv]	-	An optional argument that specifies the future value of the loan/investment, at the end of nper payments. If omitted, [fv] takes on the default value of 0.
[type]	-	An optional argument that specifies whether the payment is made at the start or the end of the period. This can have the value 0 or 1, meaning: 0 - The payment is made at the end of the period; 1 - The payment is made at the start of the period. If the [type] argument is omitted, it takes on the default value of 0 (denoting payments made at the end of the period).

Cash Flow Sign Convention:

Note that, in line with the general cash flow sign convention, cash outflows are represented by negative numbers and cash inflows are represented by positive numbers. This is seen in the examples below.

Excel Ppmt Function Examples

Example 1

The following spreadsheet shows the Excel Ppmt function used to calculate payment on the principal, in months 1 and 2 on a loan of $50,000 which is to be paid off in full after 5 years. Interest is charged at a rate of 5% per year and the payment to the loan is to be made at the end of each month.

Formula:

	A	B
1	Payments on the principal, during mths 1 and 2, on a loan of $50,000 that is to be paid off in full over 5 years, with an interest rate of 5% per year (payment made at end of each mth):
2	Mth 1:	=PPMT( 5%/12, 1, 60, 50000 )
3	Mth 2:	=PPMT( 5%/12, 2, 60, 50000 )

Result:

	A	B
1	Payments on the principal, during mths 1 and 2, on a loan of $50,000 that is to be paid off in full over 5 years, with an interest rate of 5% per year (payment made at end of each mth):
2	Mth 1:	-$735.23
3	Mth 2:	-$738.29

Note that in this example:

The payments are made monthly, so it has been necessary to convert the annual interest rate of 5% into the monthly rate (=5%/12), and the number of periods from years into months (=5*12).
As the forecast value is zero, and the payment is to be made at the end of the month, the [fv] and [type] arguments can be omitted from the above functions.
The returned payments are negative values, as these represent outgoing payments (for the individual taking out the loan).

Example 2

In the spreadsheet below, the Excel Ppmt function is used to calculate the payment on the principal, during quarters 1 and 2 of an investment that is required to increase an investment from $0 to $5,000 over a period of 2 years. Interest is paid at a rate of 3.5% per year and the payment into the investment is to be made at the beginning of each quarter.

Formula:

	A	B
1	Payments on the principal, during qtrs 1 and 2, into an investment with current value $0, which is required to reach $5,000 over 2 yrs. The interest rate is 3.5% per year (payment made at beginning of each qtr):
2	Qtr 1:	=PPMT( 3.5%/4, 1, 8, 0, 5000, 1 )
3	Qtr 2:	=PPMT( 3.5%/4, 2, 8, 0, 5000, 1 )

Result:

	A	B
1	Payments on the principal, during qtrs 1 and 2, into an investment with current value $0, which is required to reach $5,000 over 2 yrs. The interest rate is 3.5% per year (payment made at beginning of each qtr):
2	Qtr 1:	-$600.85
3	Qtr 2:	-$606.11

Note that, in this example:

The payments are made quarterly, so the annual interest rate of 3.5% has been converted into a quarterly rate (=3.5%/4), and the number of periods has been converted from years to quarters (=2*4).
The [type] argument has been set to 1, to indicate that the payment is to be made at the beginning of each quarter.
The returned payments are negative values, as these represent outgoing payments (for the investor).

For further details and examples of the Excel Ppmt function, see the Microsoft Office website.

Ppmt Function Errors

If you get an error from the Excel Ppmt function, this is likely to be one of the following:

Common Errors

#NUM!	-	Occurs if the supplied per argument is < 0 or is > the supplied value of nper.
#VALUE!	-	Occurs if any of the supplied arguments are non-numeric.

Also, the following problem is encountered by some users:

Common Problem

The result from the Excel Ppmt function is much higher or much lower than expected.

Possible Reason

Many users, when calculating monthly or quarterly payments, forget to convert the interest rate or the number of periods to months or quarters.

Solve this problem by ensuring that the rate and the nper arguments are expressed in the correct units. I.e.:

months = 12 * years
quarters = 4 * years

monthly rate = annual rate / 12
quarterly rate = annual rate / 4

Return to the Excel Financial Functions Page
Return to the List of All Built-In Excel Functions