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:
Based on the Excel spreadsheet above, the following PPMT examples would return:
- #NUM! error – Occurs if the given per argument is less than 0 or is greater than the supplied value of nper.
- VALUE! error – Occurs if any of the given arguments are non-numeric.
- 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.