Using the Excel PPMT Function
SummaryThe Excel PPMT function will return the principal portion of a payment for a given period for an investment. The principal payment can be based on periodic, constant payments and a constant interest rate.
To find the total payment for a period, both interest and principal, use the PMT function. Alternatively, use the IPMT function to find interest only payments.
Syntax=PPMT (rate, per, nper, pv, [fv], [type])
Required. Interest rate per period.
Required. Period for which you want to find the interest. Must be in the range of 1 to nper.
Required. Total number of payment periods.
Required. Present value that a series of future payments is worth today. PV should be entered as a negative number.
Optional. The future value desired after the last payment is made. If omitted, FV is assumed to be zero (0).
Optional. Argument indicating when payments are due. Use 1 for beginning of the period and zero (0) for end of the period.
Usage NotesPPMT is used to calculate the principal payments for an investment in a given payment period.
Rate and Nper Units
It is critical that you are consistent with the units used when entering values for rate and nper. Some conversion (i.e. from annual to monthly) may be required.
For example, if you are making monthly payments on a 3-year loan at 2.5% annual interest, use 2.5%/12 for rate (to get the monthly rate) and 3*12 for nper (to get the total number of monthly payments).
Failing to ensure the units are the same will produce incorrect results.