Categories
Microsoft Excel

The PMT Function in Excel

PMT is an abbreviation for payment. The PMT function calculates the regular payment that needs to be made for an amount of money, based on a fixed interest rate and for a fixed duration. It is ideal for estimating the regular repayments to be made for a loan, but remember that in most situations fees, taxes and charges are added, so check with your lending institution first.
The general function to calculate regular repayments on a loan is =PMT(interest rate, duration, loan amount).
Before you start, ensure that the data you are working with in your Excel worksheet looks similar to the screen capture in Step 1.

STEP 1:

Select cell F7, then type =PMT(D7/$D$3,E7*$D$3,C7)*-1. Make sure to carefully check the formula, then when you are sure it is correct press CTRL+ ENTER.

ExcelPMTFunction-1-DynamicWebTraining
Click image to enlarge 

STEP 2:

Double-click on the fill handle to copy the formula from cell F7 down to cell F15.

ExcelPMTFunction-2-DynamicWebTraining
Click image to enlarge 

STEP 3:

Click in cell H7, type =F7*(E7*$D$3), then press TAB.

ExcelPMTFunction-3-DynamicWebTraining
Click image to enlarge 

STEP 4:

In cell I7, type =H7-C7 then press ENTER.

ExcelPMTFunction-4-DynamicWebTraining
Click image to enlarge 

STEP 5:

Click in cell H7, hold down SHIFT, and then click in cell I7 to select the range H7:I7.

STEP 6:

Drag the fill handle to row 15 to copy the formulas down.

ExcelPMTFunction-5-DynamicWebTraining
Click image to enlarge 

Handy To Know

Most financial functions in Excel return a negative value because it is viewed as money being spent. You could multiply the answer by -1 or use the ABS function to return a positive value.