Let Excel help you cut your credit card debt by calculating the monthly payments needed to pay off the card. The PMT function in Excel calculates of loan payments needed to clear the debt in a certain time.
Suppose there is a remaining balance of $5,400 on a credit card with an annual interest rate of 17%. With no additional purchases will be made on the card while the debt is being paid off.
Therefore, based on the below example, we use the Excel formula like this:
=PMT(0.017/12,2*12,5400)The result will display the monthly payment required to pay off the credit card debt within the specified loan term. So in the case of $5.400 debt with a 17% annual interest rate and two years to pay off requires payments of $266.99 per month, see below.
Syntax for PMT()
= PMT(rate,NPER,PV)- Rate: The annual interest rate applied to the debt.
- NPER: Calculates the number of payment periods e.g. the loan term or desired timeframe to pay off the debt.
- PV: returns the present value of the investment, but in our case when paying off a credit card debt, it’s the total amount of debt on the credit card.
Monthly payments calculated two different ways
With any of Excel’s financial functions it’s important to match the interest rate and term period to the same value. It’s easy to mix up an annual interest rate with monthly terms.
The interest rate is always an annual figure so it needs to be adjusted to match the term being used. Divide by 12 for monthly, 52 for weekly etc.
The annual interest rate is divided by 12 and the term in years multiplied by 12 so they match the monthly payment number we’re after.
If the term is in months, then only the interest rate has to be divided by 12.
Weekly or Fortnightly (two week) payments
If you want to make payments weekly or every two weeks.
- Change the Term to show the number of payments per year (52 per year for weekly, 26 for fortnight).
- Change the PMT formula to match (divide by either 52 or 26), so the interest rate matches the payment frequency.
Practical Notes
- Does NOT include any fees or charges that might be added to the debt.
- Credit card companies are notorious for the way they calculate interest in a way to maximize their profits. Excel’s PMT will give you a good guide for budgeting and comparison but it might not match the reality.
- It’s likely the interest rate will change, so you will have to adjust the rate, term and amount outstanding over time.
- Enter the interest rate as decimal e.g. 13% = 0.13 or type in the interest rate with % sign and Excel will handle it.
- Use PMT to compare rates and payments needed if you’re considering transferring balances to a new card.
Predict the future value of your investments with Excel
Investment Rate of Return is easy in Excel
Track the largest companies market caps, real time, in Excel
