PPMT Function
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 0
|
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.84 Amount paid as principal in the January of the 5th year of a 20 year loan |
=PPMT(C4,D4,B4,-A4,0,1) |
£4,482.36 Amount 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 |
Download the Excel Formulas Ebook for a fast and simple guide to the most useful Excel formulas.
Follow us on
