What is the formula for calculating the months till pay off of a credit card that includes the APR?

Question

I'm playing around in Excel attempting to get the total months to pay off a credit card. I'm not looking to pay off by a certain date. I just want to know how long it will take me to pay off what I currently own. Adjusting the monthly payment from minimum amount due, to adding a few extra hundred dollars to watch the months come down.

I have the APR, Monthly Min Due %, and the Balance, and I know I can just take the total balance and divide that by my monthly amount and that will give me the total months, if I had 0 interest and no more charges.

But what I am looking for is how to include the APR into the calculation. What is the formula where I don't have to make a giant spread sheet with multiple rows showing each months principle balance and pay off etc..? I just want to be able to plug in my monthly amount I want to pay, and watch the months go up or down.

Answer 1

Use can use the NPER function to calculate the number of periods until payoff given a constant payment amount:

Balance   1,000
Rate      5%
Payment   100
NPER      10.23557252    =NPER(5%/12,100,-1000)

Note that if you divide the APR by 12 to get a monthly rate, NPER will give you the number of months until payoff.