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