Are you considering a new car loan, taking an equity loan out on your home, or perhaps about to buy a new home? Find out what the payment amount will be using Excel's PMT Function.
The payment function is used to calculate the monthly payment amount on a loan based on level payments and a fixed interest rate. The payment function takes five arguments explained below:
• Rate: is the annual interest rate as charged by the lender. It should be divided by the number of payments to be made in a year. For example suppose a 5% rate for a loan of $5,000 and the loan terms are 3 years paid monthly. The formula would be =PMT(.05/12,36,5000). The rate must be divided by the number of payments in a year to convert it to a monthly interest rate.
• nper: is the total number of payments to be made on the loan. A 3 year loans with monthly payments would result in a nper of 36.
• PV: is the present value of the loan (i.e., the principle of the loan). It represents the value today of a future stream of payments. If you want the payment amount to be returned as a positive value, you would enter the PV as a negative.
• FV: represents the residual balance at the end of a payment stream. It is an optional argument that is set to zero by default.
• Type: indicates when the payment is due. A type of Zero or omitted calculates the payment as due at the end of the period. A type of 1 calculates the payment as due at the beginning of the period.
The proper syntax is: =PMT(rate, nper, pv, fv, type)
The formula is proceeded by an equal sign and each of the arguments in the function is separated by a comma.
Typically, you would set up your worksheet with cells to store the values to be used in the PMT function arguments. The function arguments would reference the cells where the values are located (as shown in the example below). This technique makes the function dynamic and you can calculate any combination of principal, interest and terms.
See Also: Building Wealth with Excel's PMT Function
