Using the Excel PMT Function
SummaryThe PMT function is one of Excel's financial functions and is used to calculate the payment for a loan based on constant payments and a constant interest rate.
To find the interest portion of a payment for a given period use the IPMT function. To find the principal only portion of a payment use the PPMT function.
Syntax=PMT (rate, nper, pv, [fv], [type])
Required. Interest rate for the loan.
Required. Total number of payments for the loan.
Required. The principal, or 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 NotesPMT is used to calculate the principal and interest payments for a loan. The function assumes a constant interest rate and constant payments. The PMT function does not include fees, taxes, or reserve payments that are occasionally included with a loan.
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 10-year loan at 3.5% annual interest, use 3.5%/12 for rate (to get the monthly rate) and 10*12 for nper (to get the total number of monthly payments).
Failing to ensure the units are the same will produce incorrect results.