Written by PMT function has various uses; if you go to Excel help you?ll see its uses. I am using it to calculate monthly payment I have to do pay off credit card debt. If you also want to do that please follow these steps:
1. Type this A1=Card Name, B1=Balance, C1=Years, D1= Interest rate, E1=Monthly Payment, F1=Total payment, G1=Interest paid.
2. In the Card Name field you should enter the Card?s name you want to calculate monthly payments for, Balance is enter your credit card balance, Years you want to pay your debt off, current interest rate of your credit card. Monthly payment, total payment, and interest paid would be calculated using our formulas. Also, change the format of the interest rate field to percentage by Right click>Format Cells>Number>Percentage
3. In cell E2 enter this formula
=IF(ISERROR(PMT(D2/12,C2*12,B2)),0,PMT(D2/12,C2*12,B2))
Explanation:
PMT(D2/12,C2*12,B2)
D2, interest rate, is divided by 12 to get monthly interest rate
C2, number of years, is multiplied by 12 to get number of months
B2, balance on your credit card
Now, PMT function will calculate exact monthly payments you?d need to pay off your debt.
ISERROR(?.)
Explanation:
ISERROR function will check if the expression given produces an error and it will return TRUE or FALSE.
Finally, an IF is used to put a zero as result of the formula if it produces any error.
4. In cell F2 enter this formula
=E2*C2*12
This will give us the total payment done over the years.
5. In cell G2 enter this formula
=F2+B2
This will give us the total interest paid to pay off the debt.
This sheet should look like this 
Note: the result of our formulas will generate red numbers in brackets; this is the way of Excel to show us negative numbers. If you have Office XP or 2003 you can view a training session here.
Related posts:
Recent Comments