Help on very complex formula!

D

Diego

Hi everyone,

I have a very complicated issue, maybe you guys can help.

Here is the situation

I have 600+ records (assume they are rates) and they increase ever
year by 4%. I need to take these 30 years ahead in time not a big deal
X^30.
Now here is the problem, each year i have to calculate 1% of the tota
amount so 1% of X^1 + 1% of X^2 ...... 1% X^n and this 1% will generat
a 8% profit per year creating a compound interest issue (what is th
amount of each record after 30 years). Since i have 600 records at 3
years each i do not wish to create a huge table, anybody can think of
formula that resume this?

Gladly appreciate all the help i could get!!

Thanks
 
S

sulprobil

Hope I understood you correctly and got this right:

Set r1=4%, r2=1%, r3=8% and calculate

sum=0

for i=1 to 30

sum=sum + (x * r2 * (1+r1)^i)*(1+r3)^(30-i)

next i

for each x of your 600 rates.

I do not see a simple closed formula to do this.

Since Excel has no problem with 600 rows * 30 columns I
would suggest to solve this with a "huge table" step by
step.

Regards,
sulprobil
 
D

Diego

Thanks, is rather a complex issue, i thought to incorporate the compoun
interest formula somewhere at the end so that r2 which would be 1% o
(X +X4%) at 8% for the next 30 yrs...

Thanks for your help!!
 
D

Diego

Thanks, is rather a complex issue, i thought to incorporate the compoun
interest formula somewhere at the end so that r2 which would be 1% o
(X +X4%) at 8% for the next 30 yrs...

Thanks for your help!!
 
D

Dana DeLouis

Hello. I do not understand the question. However, I believe a closed form
of your equation that you were looking for is the following:

((1+r1)*r2*(1+r3)^n*(((1+r1)/(1+r3))^n-1)*x)/(r1-r3)

Here, both versions return the same numbers.
HTH
Dana DeLouis


Sub Demo()
Dim r1, r2, r3, i, Sum, x, n

r1 = 0.04
r2 = 0.01
r3 = 0.08
x = 10 'Start amount

For i = 1 To 30
Sum = Sum+(x*r2*(1+r1)^i)*(1+r3)^(30-i)
Next i
Debug.Print Sum
' 17.7300743855194

n = 30
Sum = ((1+r1)*r2*(1+r3)^n*(((1+r1)/(1+r3))^n-1)*x)/(r1-r3)
Debug.Print Sum
' 17.7300743855194

End Sub
 
Top