Question on calculating payoff/interest paid a credit card with an annual fee

[timmyhawken]

Hi,

I'm working on an Excel class project where I'm creating a comparison calculator to help the user determine if they should accept a 0% balance transfer offer on another card or keep their current card. Its pretty straight forward for the most part, but the part I'm needing help with if the credit card has an annual fee and how to go about factoring that in.

I'm having the user input their current card(s) balance, APR, payment amount (this is explained to be the amount they regularly pay, not necessarily the minimum payment) and any annual fees they have. Then they input the 0% balance transfer offer term in months, the APR after the promo period and that card's annual fee.

The end result will be a side by side comparison of how many months it will take to payoff the debt and how much interest you will save (if any) by accepting the balance transfer offer. I am aware there are online calculators too, this is for a final project.

For factoring the annual fee, I suppose I could use NPER to figure out how many months they would need to payoff, divide by 12 and multiply that by the fee, add that to the principle balance, but that doesn't seem to be totally logical or accurate. Any better ideas?

Thanks in advance for any help--this a beginner course on Excel, so bear with me

Tim

 

[timmyhawken]

Bump (sorry, desperate for an answer)