The Excel PPMT function is used to calculate the principal payment made in a period of an investment. Where as IPMT calculates the interest paid in a period of an investment, PPMT relates to the amount paid that comes of the balance.
The syntax for the PPMT function is:
=PPMT(rate, per, nper, pv, [fv], [type])
| Argument | Purpose |
|---|---|
| rate | The interest rate per period |
| per | The period for which you want to find the principal amount paid. It must be entered in the range 1 to nper |
| nper | The number of payment periods during the lifetime of the loan. If payments are monthly for 5 years, this would be entered 5*12 to calculate 60 payment periods |
| pv | The present value, or current amount of the loan or investment. |
| fv | The future value, or amount after the last payment is made.FV is optional and if omitted the value is assumed to be 0 i.e. the future value of a loan will be 0 |
| type | When the payments are due. It can be entered as 1 or 0 and is optional. If omitted the value is assumed to be 00 – Payments are made at the end of the period1 – Payments are made at the beginning of the period |
The examples below show the PPMT function being used to return the amount paid as principal on a loan using different parameters.
| Function | Result |
|---|---|
| =PPMT(C4/12,D4*12,B4*12,-A4) | £405.84Amount paid as principal in the January of the 5th year of a 20 year loan |
| =PPMT(C4,D4,B4,-A4,0,1) | £4,482.36Amount paid as principal in the 5th year of a 20 year loan, with a future value of 0 and payments made at the beginning of each year |
