I need a Formula, Please

[easybucks]

In the example below, I need a formula to accomplish this. As you can
see: The "1" row is the titles.

B2XC2=D2. Then B2+D2=E2. Then the E2 total is also B3 (the second day).
I want to be able to change the number in B2 and have all the other
numbers change accordingly. I would like to be able to populate the
chart for XX number of days, probably at least a year. I also need to
change the interest rate to 2.25% when the "E" column reaches $1000.00
and to 2.50% when the E column reaches $5000.00. HELP!!

A B C D
E
Day Begin Bal. Int. Rate Int. Amt. End Bal.
1 $100.00 2% $2.00 $102.00
2 $102.00 2% $2.04 $104.04
3 $104.04 2% $2.08 $106.12
4 $106.12 2% $2.12 $108.24
5 $108.24 2% $2.16 $110.40
6 $110.40 2% $2.21 $112.61
7 $112.61 2% $2.25 $114.86
8 $114.86 2% $2.30 $117.16
9 $117.16 2% $2.34 $119.50
10 $119.50 2% $2.39 $121.89
11 $121.89 2% $2.44 $124.33
12 $124.33 2% $2.49 $126.82
13 $126.82 2% $2.54 $129.36
14 $129.36 2% $2.59 $131.95

 

[Ron Coderre]

Try this:

Somewhere away from your loan amort tablel, put this rate table:
(I'll assume it's in cells G1:H4)

EndBal_____Rate
0_________2%
1000______2.25%
5000______2.50%

Then, put this formula in C2 and copy it down:
=VLOOKUP(B2,$G$2:$H$4,2,1)

That should take care of your tiered interest rate problem.

Does that help?

Regards,
Ron

 

[agarwaldvk]

Easybucks

Instead of doing all this of going around, why dont' you just compound
interest concept to compute the value of the accrued amount at the end
of each day/week/month/year?

As you would know the accrued amount at the end of 'n' periods with an
annual interest rate of 'r'%' is generally given by the formula :-

P * (1 + r/(365 * 100)) ^ n

where P is the Principal amount
n is the number of periods after which the accured amount is desired
r is the annual interest rate (expressed as a fraction - a 10% rate
would be expressed as 0.1)


Hope this helps!


Best regards


Deepak Agarwal