How to Calculate Loan Payments Using Excel PMT Function [Solved]

How to Calculate Loan Payments Using Excel PMT Function

Excel LogoThe 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:

  1. D2 – Rate:
    D3 – # of payments:
    D4 – Principal:
    D5 – Payment:
    E2 – 7%
    E3 – 24
    E4 – $5,000.00
  2. Click on cell E5 – 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 in the dialog box.
  9. Click on cell E3 in the spreadsheet.
  10. Click on the Pv line in the dialog box.
  11. Type a minus sign ( – ) and then click on cell E4 in the spreadsheet.
  12. Click OK in the dialog box.
  13. The answer $223.86 appears in cell E5.
  14. When you click on cell E5 the complete function = PMT ( E2/12 , E3 , – E4 ) appears in the formula bar above the worksheet.


Leave a Reply

Your email address will not be published. Required fields are marked *