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.
Steps: Using PMT Function in Excel
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.
STEP 2:
Double-click on the fill handle to copy the formula from cell F7 down to cell F15.
STEP 3:
Click in cell H7, type =F7*(E7*$D$3), then press TAB.
STEP 4:
In cell I7, type =H7-C7 then press ENTER.
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.
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.
