Forum Discussion
Cumulate percentage returns.
Hello, I have investment returns which I would like to cumulate. I have positive and negative returns in percentage figures. I am struggling to find the formula, i.e. GEOMEAN doesn't work well with negative returns. I apprecieta every tips and hints! Best wishes, Pascal
4 Replies
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
