Cumulative Returns

[viquarshaikh]

Hi

I have a series of daily returns e.g.
0.4%
0.7%
0.2%
0.2%
-0.5%

I intend to look at the cumulative effect of these returns, meaning if I start with an index value of 100 and keep adding the effects of these returns to the previous index value, I'll end up with 100.96 or 0.96%.

I was wondering if there was a way where I could do this in a single formula given the series of returns, rather than having the index etc. calculated.

 

[Peter_SSs]

if I start with an index value of 100 and keep adding the effects of these returns to the previous index value, I'll end up with 100.96 or 0.96%.

How did you calculate that result?

 

[viquarshaikh]

The series is as follows and supposing this is in A2:A6
0.4%
0.7%
0.2%
0.2%
-0.5%

starting at 100 in cell B1
my formula in B2 is =B1+B1*A2 and I drag the formula down until B6. these could be daily, weekly or monthly returns on my investment.

Then I get the overall return by another formula which works as =B6/B1-1 and format as percent to get the returns.

that's how i've arrived at this returns number and i was wondering if there could be a formula to sidestep the extra column.

 

[Peter_SSs]

Your description of your calculation is what I had envisaged and the reason for my question since it doesn't produce 100.96 or 0.96% as shown in my sheet below. Perhaps some of your percentages in column A actually have more decimal places behind the scenes?

In any case, the series of calculations can be combined into a single cell as I have shown in B10.

Excel Workbook
	A	B
1		100
2	0.40%	100.4
3	0.70%	101.1028
4	0.20%	101.3050056
5	0.20%	101.5076156
6	-0.50%	101.0000775
7
8		1.0000775%
9
10		1.0000775%
% calculation

 

[shg]

Or =PRODUCT(1+A2:A6)-1 confirmed with Ctrl+Shift+Enter

 

[viquarshaikh]

Amazing!

Thanks Pete and SHG