Thread: Credit card debt Calculator

Hi guys and girls, not really sure if this question belongs on a programming forum or not?

Basically, I'm trying to figure out the formula(s) involved for designing a credit card debt repayment calculator. The user will enter his/her current debt, the interest rate they are paying, minimum monthly repayments, and a date in the future when they want to be debt free.

The calculator needs to determine what kind of monthly repayments the user needs to make to meet his/her debt free deadline on time vs. minimum repayments, as well as how much interest they will end up paying in the long run (I assume this would be complex interest).

Any thoughts?

Hi, This might be a start:-

Calculating Repayments on Loan
This formula will return the Periodic Repayments to be made over the period specified.
Use AER Interest Rates fo "r"
P = Principle. r = Annual Interest Rate. R = (1+ r) S = Periodic repayment.
n = Period (mth etc.)
S = P ( R(n+1) - Rn )
Rn - 1
Note:- When dealing with Loans and Mortgages the normal repayment periods are months.
In these cases the 12 root of R would be used to represent a period of one month

In the example "r" = 10%, "R" = 1.1. This would be replaced by 1.1(1/ 12) = 1.00797414
If the period "n" was 3 years, "n" would equal 36 Months (3 x 12.)
In the case (n + 1). 1 is always 1 irrespective of whether the periods are months or years.
NB:- For Payments in advance, alter the bottom line of the equation to:- R(n+1) -R
Regards Mick

How do you plan to handle variable interest rates? It is not a simple assignment, to be sure, but you should be able to get your calculator around it.

There is one thing you may not know to include. Consider this:

1) Credit card companies often suggest writing one of their checks and even transferring other balances to their company - usually at a very low interest rate. They may even tell you the interest rate is guaranteed as long as the transferred balance exists.

2) What may NOT be clear from these offers is one very important clause in the rules. The company reserves the right to allocate excess funds any way they wish. It is not obvious how important this is. Example:

If you have this:

Regular Balance @ 15%: 10000
Special Balance @ 2%: 5000
Avg Rate (10000*.15 + 5000*0.02)/15000 = 0.10666666666

And you make a minimum payment, say 1200, it gets allocated like this:

Regular Balance @ 15%: 10000 - 800 = 9200
Special Balance @ 2%: 5000 - 400 = 4600
Avg Rate (9200*.15 + 4600*0.02)/13800 = 0.10666666666 -- No change.

800 and 400 are proprtional to the account balances.

Here's the fun part. If you pay MORE THAN THE MINIMUM, it is very unlikely that the allocation will remain proportional. Suppose you pay 2000 and 1200 is the mimimum - an extra 800. This is what most likely occurs.

Regular Balance @ 15%: 10000 - 800 = 9200 <== Why didn't this change?!
Special Balance @ 2%: 5000 - 400 - 800 = 3800
Avg Rate (9200*.15 + 3800*0.02)/13000 = 0.1120000 -- Up!!!

ALL the excess was allocated to the lower interest rate balance. It is in the company's best interest to get rid of those low balances. Why would they do anything else? In this way, the company is effectively forcing you to make minimum payments if you intend to rely on the long term effects of that lower interest rate guarantee. As long as you make minimum payments, the payments will be allocated as you expect.

I hope I have not discouraged you. Helping people get out of debt is a great pursuit.

Good luck.

Thanks very much for the replies guys! Just wondering, do either of you have any programming experience?

Hi Cycloptic
If you post you programming problem on www.mrexcel.com or www.excelforum.com you will find my name with lots of other people who may be able to help. This is with VBA for excel.
Regards Mick

The card is aimed at folks without bank accounts. About 4 million Americans on the Social Security payroll are "unbanked" and must receive paper checks, which are vulnerable to financial crimes like check fraud and more minor problems such as delivery delay.
-----------
george
Credit Card Debt