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 to be made on the investment.
pv - the present value of the investment. For future value this argument is omitted.
fv - future value - the value of the investment at the end of the investment period.
type - indicates when payments are made:
- "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 Find Future Value:
Note: In this example, the type argument has been omitted (see the image to the right).
- Enter the following data into cells:
D2 - Rate:
D3 - # of payments:
D4 - Pv:
D5 - Future value:
D6 - Payment:
E2 - 7
E3 - 24
E4 - 0
E5 - $10,000.00
- Click on cell E6 - 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 ( # of payments ) in the dialog box.
- Click on cell E3 in the spreadsheet.
- Click on the Pv line in the dialog box.
- Click on cell E4 in the spreadsheet.
- Click on the Fv line in the dialog box.
- Type a minus sign ( - ) and then click on cell E5 in the spreadsheet.
- Click OK in the dialog box.
- The payment amount - $389.39 appears in cell E6.
- When you click on cell E6 the complete function = PMT ( E2/12 , E3 , E4 , -E5 ) appears in the formula bar above the worksheet.
Related Functions
