formula to get number + 80 % from Previous Balance

[Abdul]

I have thefollowing
Y1 Y2 Y3 Y4
1000 1000 1000 1000
800 800 800
640 640
512
Total 1000 1800 2440 2952

the logic like this

Every year 1000 New Plus 80 % from previous year diminishing value

so Y3 figures are like this

1000 New
80 % of Y2 New =800
80 % of Y1 remaining (800)=640

Can I get this one through a formula

thanks

 

[Roger Govier]

Hi Abdul

There may be easier ways, but this seems to work.
I put the number of Years in C1, the Principal (1000) in D1 and the
Percentage (80%) in E1, and then
=IF(C1=0,D1,D1+SUMPRODUCT((D1*E1^ROW(INDIRECT("1:"&C1)))))
With 0 in C1 1000, 1 in C1 1800, 2 in C1 2440 etc.

Obviously you could hard code the values into the Sumproduct formula if you
wish, as opposed to using cells to hold the variables

 

[Dana DeLouis]

Hi. One way...
Assuming data in A1:B4:


0 $1,000
1 $800
2 $640
3 $512


Equation in B1 is the following, and drag down...
=FV(-0.2,A1,,-1000,0)

Total for years 0-3:

=1000*(0.8^(3+1)-1)/(0.8-1)

= 2,952

= = =
HTH :>)
Dana DeLouis