How to Calculate Loan Payments Using Excel 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 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.