Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 macro 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 minimum) 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. A B C D E F G H Pay # Mo Begin Min + New Mo - Int - Prncpl New Bal Pay Int applied applied Balance 01 Dec 2500 62.50 37.50 (37.50) (25.00) 2475.00 02 Jan 2475 62.60 37.21 (37.12) (25.00) 2462.12 03 Feb 2462.12 etc ...... 42 42.26 42.89 00.63 (00.63) (42.26) 0.0 __________________________________________________ ________ 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 .... Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel macro to calc payment/payoff of credit card
Steve,
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 2412.50? And do you want the results of this macro to produce on a new sheet? You can e-mail if that's easier. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
download credit card statements to Excel | Excel Discussion (Misc queries) | |||
Credit Card Number in excel | Excel Discussion (Misc queries) | |||
credit card payoff worksheet | Excel Worksheet Functions | |||
Credit Cards, time in months to payoff and payoff amount | Excel Worksheet Functions | |||
How do I do a MOD-10 Credit Card check in Excel? | Excel Worksheet Functions |