• Share

Discuss in my forum

# Calculating Future Value Using Excel's PMT Function

Excel 2007 PMT Function

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).

1. 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

2. Click on cell E6 - the location where the results will be displayed.

3. Click on the Formulas tab.

4. Choose Financial functions > PMT from the ribbon to bring up the function's dialog box.

5. Click on the Rate line in the dialog box.

6. Click on cell E2 in the spreadsheet.

7. 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.

8. Click on the Nper line ( # of payments ) in the dialog box.

9. Click on cell E3 in the spreadsheet.

10. Click on the Pv line in the dialog box.

11. Click on cell E4 in the spreadsheet.

12. Click on the Fv line in the dialog box.

13. Type a minus sign ( - ) and then click on cell E5 in the spreadsheet.

14. Click OK in the dialog box.

15. The payment amount - \$389.39 appears in cell E6.

16. 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

Ted French