The PMT function, one of Excel's Financial functions, can be used to calculate the payments for a loan or the future value of an investment.
The syntax for the PMT function is:
= PMT ( rate , nper , pv , fv , type )
rate - the annual interest rate for the loan.
nper - the total number of payments for the loan.
pv - the present value or the amount borrowed or the "principal of the loan.
fv - future value - for a loan this will be $0.00. For loans this argument can be omitted.
type - indicates when payments are due:
- "0" (or omitted) - at the end of the period ie: end of the month.
- "1" - at the beginning of the period ie: beginning of the month.
Example Using Excel 2007's PMT Function to Calculate Loan Payments:
Note: In this example, both the future value and type arguments have been omitted (see the image to the right).
- Enter the following data into cells:
D2 - Rate:
D3 - # of payments:
D4 - Principal:
D5 - Payment:
E2 - 7%
E3 - 24
E4 - $5,000.00
- Click on cell E5 - the location where the results will be displayed.
- Click on the Formulas tab.
- Choose Financial functions > PMT from the ribbon to bring up the
function's dialog box.
- Click on the Rate line in the dialog box.
- Click on cell E2 in the spreadsheet.
- After the E2, type a forward slash " / " followed by the number 12 in the Rate line of the dialog box. This gives you the interest rate per month.
- Click on the Nper line in the dialog box.
- Click on cell E3 in the spreadsheet.
- Click on the Pv line in the dialog box.
- Type a minus sign ( - ) and then click on cell E4 in the spreadsheet.
- Click OK in the dialog box.
- The answer $223.86 appears in cell E5.
- When you click on cell E5 the complete function = PMT ( E2/12 , E3 , - E4 ) appears in the formula bar above the worksheet.
Related Articles
