Excel macro to calc payment/payoff of credit card



Hi ... I'm working with families in debt ... and this is a really big post.

I used to have a Lotus 123 macro that would allow me to do wha-if scenarios.

I would click on the cell with Credit Card Macro ... to run the credit card
It would then prompt for these variables credit card balance, int rate,
minimum payment amount, an alternative payment greater than a minimum and
beginning payment month.

It would display it on the spreadsheet like

Balance 2500.00
Int rate 18.00 percent per year
min payment 2.50 percent of outstanding balance
alt payment 50.00 (to do the what if I pay more per month as a
Begin month Dec

When I clicked run ... it would then do calculations under the hood that
would compute the entire payment as the credit card company ... calc the new
int amount, adding the int to the balance ... substract the new int amount
and subtract principal payment from balance and display both the interest
paid and principle payment so that we could show how much int was being paid
over the life of the credit card ... and how long it would take to pay it off.

It would then calculate the formulas for a credit card account and display
the results like this ... until it finished up with the last payment. It
would check each cell for the balance to see if it was >= to either min pay
or alt pay and apply the payment, it then inserted the next line with the
line count, new month and payment ... etc for the remainder of the credit
card payoff until the balance gets to 0.

Pay # Mo Begin Min + New Mo - Int - Prncpl
Bal Pay Int applied
applied Balance
01 Dec 2500 62.50 37.50 (37.50) (25.00)
02 Jan 2475 62.60 37.21 (37.12) (25.00)
03 Feb 2462.12 etc ......
42 42.26 42.89 00.63 (00.63) (42.26)
Totals xxxxx.xx
2500.00 0.0

Total pay off cost (xxxxxx + 2500) yyyyy.yy

I know how to do the formulas for each individual line, but I want to
automate this so that Excel does all the work after I input the variables,
inputs the new lines and number, month, gets the new balance, calcs the int,
etc ... until the credit card is paid off .

I wont ask for all the customized stuff he put in like green banding, and
bolding of cell totals etc ... :)

I know it is a lot to ask ... but I've spent a long time crawling over MS
sites and using google to look at various templates ... and I just cant seem
to find the template that I want that does the min payments by the credit
card company or the alt. payment method.

Any help would be greatly appreciated ....






I'm going to have a go at this for you to get you started. I'm useless
with interest rates so keep me on the right track as I go!
If the start balance is 2500 do you then add the monthly interest and
then subtract the payment made?

Eg. £2500 + interest of £37.50 - payment of £125 = new balance of

And do you want the results of this macro to produce on a new sheet?
You can e-mail if that's easier.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question