PPMT Function

The PPMT function returns the principal portion of a payment for a given period of an investment or a loan, based on constant periodic payments and a constant interest rate. PPMT takes six arguments, four of which are required:

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

Each argument has the following meaning:

• rate - the interest rate per period. Typically, this is the annual interest rate divided by the compounding periods per year.
• per - the period of interest (i.e. 1, 2, 3, etc.)
• nper - The total number of payment periods for the loan or investment.
• pv - The present value, or the principal amount of the loan or investment.
• fv - Optional. The desired future value, or a cash balance after the last payment (defaults to 0)
• type - Optional. The timing of the payment: 0 = end of period (default), 1 = beginning of the period.

Example #1 - hardcoded values

Suppose you have a 5-year loan of $10,000 with an annual interest rate of 5% and 12 compounding periods per year. You want to figure out the principal portion of the payment for period 1. You can calculate this amount with the PPMT function like this:

=PPMT(5%/12,1,60,-10000)

The inputs to PPMT are as follows:

• rate - 5%/12 = 0.00416 (annual interest rate with monthly compounding)
• per - 1 (interest for period 1)
• nper - 60 (a 5-year loan has 60 periods)
• pv - -10,000 (the loan amount is negative to yield a positive result)
• fv - Omitted. Defaults to 0.
• type - Omitted. Defaults to 0.

The result is 147.05. This is the principal portion of the payment for period 1 of the loan. Notice we have provided the loan balance as a negative value in order to get a positive result from PPMT. If we provide 10,000 as a positive number, PPMT will return -147.05. In practice, the decision to use a positive or negative value for pv depends on the specific scenario.

Example #2 - worksheet formula

In the example above, all inputs to PPMT are hardcoded to make the formula easier to read. More typically, the inputs will come from cell references. The screen below shows how the same example can be transferred to a worksheet:

The formula in cell C10 is evaluated like this:

=PPMT(C5/C7,1,C6*C7,-C4)
=PPMT(0.05/12,1,5*12,-10000)
=PPMT(0.004167,1,60,-10000)
147.05

Notice that we provide (years * periods per year) for nper instead of hardcoding the number 60. We do this so that the formula will automatically adapt to a loan with a different term in years, or a loan with a different number of periods per year.

Also, notice that the monthly payment is not needed by the PPMT function. This is because PPMT calculates the principal portion of a payment based on the original principal (or present value), the interest rate, and the number of periods. To calculate the payment for a loan you can use the PMT function.

Notes

1. Use the IPMT function to get the interest portion of a loan payment for a given period.
2. The interest rate can be provided as a percentage like 5% or a decimal number like 0.05.
3. Be careful to provide the periodic interest rate for rate. For example, 5%/12 or 0.05/12.
4. The loan value (pv) can be entered as a positive value or a negative value. 
5. The value for period (per) must be in the range 1 to nper.