The Excel PPMT Function

Search this site: Custom Search

Related Functions:  IPMT Function PMT Function

Basic 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 :

Where the arguments are as follows:

Cash Flow Convention :

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

Excel Ppmt Function Examples

In each of the examples below, the spreadsheet on the left shows the format of the Ppmt function, and the spreadsheet on the right shows the result.

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:

Result:

Note that in this example :

• The payments are made monthly, so we have had to convert the annual interest rate of 5% into the monthly rate (=5%/12), and the number of 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 this example, the spreadsheet below shows the Excel Ppmt function being used to calculate the payment on the principal, during quarters 1 and 2, on a loan of $10,000 that is to be reduced to $5,000 over a period of 2 years, by a series of constant quarterly payments. Interest is charged at a rate of 3.5% per year and the payment is to be made at the beginning of each quarter.

Formula:

Result:

Note that, in this example :

• The payments are made quarterly, so the annual interest rate of 3.5% is converted into a quarterly rate (3.5%/4), and the number of years is converted into 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 individual taking out the loan).

Further examples of the Excel Ppmt function can be found on 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:

Also, the following problem is encountered by some users: