I have calculated the inflation adjusted S&P 500 and have also created the exponential regression line in Excel.
Here is the chart:
The formula for the exponential regression line is :
With this formula I have calculated the exponential regression line, so I have for each month (I use monthly data) the actual value of the exponential regression line and not only a red line on the chart.
I would now like to create a standard deviation band with +1 and -1 standard deviations of the exponential regression line. But I have some problems doing that.
The standard deviation of the of the exponential regression line is 773.22.
If i now calculate the -1 standard deviation by subtracting 773 from the exponential regression line, I would get a negative number at the beginning of the time series. So, this calculation is wrong.
There must be some kind of relative calculation to accomplish the task. But I have no idea how to do it.
I am grateful for any help and thank you in advance.
Perhaps I have found the solution. According to the bell curve distribution one standard deviation is around 34.13% plus and minus the mean (average).
In my example the mean is the exponential regression line.
Therefore, I deduct and add 34.13% to each value of the exponential regression line and get the +1 and -1 standard deviation band.
thanks for your replay. This matter has developed much more extensively than originally thought. But I think I have solved it.
Here are my thoughts:
Using the Linear Regression Band tool is not the best choice in my opinion because the tool uses a linear regression BUT the S&P 500 has been moving exponentially since the early 1950’s so using a linear regression is not appropriate.
As for my thoughts on bell distribution in the post above, they seem to be correct. I made some testing using the Linear Regression Band tool, although it is not appropriate for this job, it is notwithstanding appropriate to get some insights about how the bands are calculated.
I did some rough calculations with the Linear Regression Band tool and have found that the channels are 34.13% (1 Std Dev) and 47.72% (2 Std Dev) (34.13 + 13.59) away from the average (=regression line), as described in the Bell curve.
When using a logarithmic scale, a somewhat strange issue can be observed. The bands are not equidistant from each other! BUT if you think about this issue, it is logical, because the same percentage value causes a different absolute price change depending on the price level.
