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

**Credit:** http://spreadsheets.about.com/od/excelfunctions/qt/20071020.htm