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])
|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.
|=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|