Author: Oscar Cronquist Article last updated on September 06, 2018

The PPMT function calculates the principal payment for a specific period for an investment based on repeated constant payments and a constant interest rate. The principal is the amount you have to pay back and interest is the charges you pay for borrowing the money.

The image above shows the PPMT function in cell E3 calculating the principal for the second month for a 10 year loan of 100 000.

Formula in cell E3:

=PPMT(C3/12,C4,C5*12,C6)

Excel Function Syntax

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

Arguments

rate Required.  The interest rate.
per Required. The period.
nper Required.  The total number of periods in an annuity.

Examples:
A monthly payment - 12
Quarterly payment - 4

pv Required.  Present value.
[fv] Optional.  Future value, default value is 0 (zero).
[type] Optional.  When payments are due.

0 - End of period, default value.
1 - At the beginning of the period.

Comments

Use the same unit for rate and nper, the above example uses monthly payments. That is why the interest rate is divided by 12 and nper is multiplied by 12. There are 120 monthly payments in a 10 year period.