I am trying to reproduce the values of the EWMA function in a spreadsheet, but I can't get the values to match. I also subtracted the sample mean from the values before I calculated the EWMA values, but the values are still a bit off. What am I missing here?
Answer: The EWMA function in NumXL calculates only the out-of-sample volatility forecast. The function uses all the data available up to the forecast horizon/date.
As the selection of the sample data changes for every new step, it affects the calculation of the EWMA forecast values. As a result, each returned values can't be interpreted or considered as an intermediate calculation for the next step. Instead, the returned values should be interpreted and considered as a different out-of-sample next-day forecast.
Note: See the attached spreadsheet for example and illustration.