Annual return - Geometric yield in stockmarket and make a diagram

If you want to calculate the geometric return, which in this case is the compound annualized growth rate (CAGR), then you can use this formula:

Return = (Ending value/Beginning Value)^(1/n)-1

To implement that in a spreadsheet you can do something like this:

The formula in D3, filled down the column, is:

=D2*(1+C3)

The formula in E3, filled down, is:

=(D3/D$2)^(1/B3)−1

Not that I listed n, the period number, in column B, so I could just fill the formula down. It also makes it easier if want to derive the n for different periods (you can just subtract period numbers)

The chart is a 2D 2Axis but if you just want the growth of 100 and not the annual returns you could change it accordingly.

You could add a linear trend line but I'm not sure that accurately reflects the large stock market fluctuations.

Note that my region uses . as the decimal separator. Judging from your screenshot you will want to enter the values in the table with , instead.

SG

The formula you used is different from mine! Note where the ( ) go.

Also on a linear scale you should expect a curved line, not a straight line, for compounding at the geometric rate for return. You can switch the Y-axis to Logarithmic if you want to show straight lines, as shown in the chart on the right.

Here is my Numbers document (Dropbox download).

If you have access to a Mac then you can switch the region to your region by going to File > Advanced > Language and Region... to the number formatting you are accustomed to using. You may need to switch the language there first before picking a different region.

SG

Any luck on your end?

To make the formula a little easier to understand I tried the Insert > Equation ... feature in Numbers, resulting in this:

I've added it to the linked Numbers document.

Ending Value is the value of the index (the Growth of 100 column is really an index) at the last period you want and Beginning Value is the value of the index at the beginning. In the example at time 0 the index is 100. But you could pick another period if you want and use the index value for that period as the Beginning Value. You can use the formula to calculate the compound return between any two periods.

SG

henric224 wrote:

see at a detailed level how the present value deviates from history.

Not sure I entirely understand what you are looking for here. One can always look at the difference between the values in the Growth of 100 and Growth at CAGR columns in the table.

BTW, the term "present value" has a specific meaning for finance problems in English. Probably not the right term here.😀

Glad I was able to help.

SG