I need a Formula, Please

  • Thread starter Thread starter easybucks
  • Start date Start date
E

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
 
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
 
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
 
Back
Top