Forum Discussion
Cumulate percentage returns.
More details and better yet a sample sheet of data would be more useful to help us understand exactly what you have and how to help. My guess as what you have is a columns of returns in the format of 0.15, 0.11, -0.05, .... meaning you earned 15% then 11% then lost 5% ....
If so I believe you can just use
=PRODUCT( 1 + [range of return values] ) - 1
to give you an overall return rate
BTW GEOMEAN() will give you the AVERAGE rate of return over those N compounds. so the above example of 0.15,0.11,-0.05 you would still add 1 to it and then take the GEOMEAN
maybe this example might help:
start with $100
| return | new value | ||
| 100 | 0.15 | 115 | |
| 0.11 | 127.65 | ||
| -0.05 | 121.2675 | ||
| 0.2 | 145.521 | ||
| -0.3 | 101.8647 | ||
| 0.018647 | net return =(net value - orig value)/orig value | ||
| 0.018647 | net return = PRODUCT(1 + returns) - 1 | ||
| 1.003702 | GEOMEAN | ||
| 101.8647 | net value = orig value * GEOMEAN ^ 5 | ||
and GEOMEAN-1 or 0.003702 in this example would have been the geometric AVERAGE compounding return rate.
Thank you so much for your response! I have already the percentage returns (see below) and would need to cumulate them. It doesn't allow me to paste a sample sheet, therefore I try the picture of the data I have.
so I believe what I previously posted is still correct. I think what you posted in the image is a column of dates (end of the month) and the corresponding return rate for that month. So if you started with $100 then on 20223-11-30 after the -3.84% return you would have about $96.16 (i.e. a loss of 3.84%). Here is the spreadsheet (also attached) I have laid it all out as I believe you need:
so in column C is the changing value based on those rates.
on row 16 is the net return rate based on that cumulative value
on row 18 is the formula I gave to calculate the cumulative return rate based just on those rates
on row 20 is what the geomean function gives you, which is the 'average' rate that if you earned each month would have been equal at the end and this is shown in columns G & H
I hope this helps but let me know if I'm still not understanding you
Hello, Thank you so much! That is solving everything. I truly appreciate your help with this! Best wishes, Pascal