How to use PPMT Function in Excel

The Excel PPMT function can be used to calculate the principal portion of a given loan payment. For example, you can use PPMT to get the principal amount of a payment for the first period, the last period, or any period in between.

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

The PPMT function syntax has the following arguments:

• Rate    Required. The interest rate per period.
• Per    Required. Specifies the period and must be in the range 1 to nper.
• Nper    Required. The total number of payment periods in an annuity.
• Pv    Required. The present value — the total amount that a series of future payments is worth now.
• Fv    Optional. The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
• Type    Optional. The number 0 or 1 and indicates when payments are due.

Set type equal to	If payments are due
0 or omitted	At the end of the period
1	At the beginning of the period

Example: Let’s look at some Excel PPMT function examples and explore how to use the PPMT function as a worksheet function in Microsoft Excel:

Syntax:  =PPMT(B1/12,1,B2*12,B3)

Result:

Based on the Excel spreadsheet above, the following PPMT examples would return:

Note:

1. #NUM! error – Occurs if the given per argument is less than 0 or is greater than the supplied value of nper.
2. VALUE! error – Occurs if any of the given arguments are non-numeric.
3. An error can arise if we forget to convert the interest rate or the number of periods to months or quarters when calculating monthly or quarterly payments. For the monthly rate, we need to divide the annual rate by 12, or by 4 for the quarterly rate.