How to I charge rate to different balances?

[Brett]

I have two credit card amounts that use different rates but are with
the same company and compose one balance. For example:
$5000 @ 5.99%
$2000 @ 4.99%
-------------
$7000

Any payments are applied to the $2000 @ 4.99% first until it is paid
off. Then payments are applied to the $5000 @ 5.99% balance. I have
calcuated my daily balance for the $5000 using

=30*5000*((1+0.0599/12)^(12/365)-1)

That's an average of 30 days in a month. It's only a few pennies off.
How do I setup a formula to calculate interest for both amounts with
their respective rates and apply payments correctly?

Thanks,
Brett

 

[Norman Harker]

Hi Brett!

My inclination would be to set up a schedule that shows the changing daily
balance and then use a formula to calculate interest on the previous day's
balance.

You might find it easier to set up columns that divide the total balance
between the amount in excess of 5000 and the amount below 5000. You can then
calculate interest at the different rates on those balances.

So to start off, your problem is how to divide the balance in those two
balance columns. Also before you start, you will need to address your mind
to the signing of the balance, the credits, debits and interest obligations.


--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Brett]

The problem is, once the $2000 is paid off, I'm not sure how to have
the payments auto apply to the $5000.

Brett

 

[Norman Harker]

Hi Brett!

A lot depends upon how you have set this up.

If you want to split the debt in B1 (expressed as a negative) so that
C1 contains the first -5000 and D1 the balance

B1
-6500
C1
=MAX(B1,-5000)
Returns -5000
D1
=B1-C1
Returns -1500

Now you can apply your interest to the separate amounts.

You might need to address what happens when B1 is positive.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.