### Exponentially Weighted Moving Average in Excel

Posted:

**Wed Mar 20, 2013 6:35 pm**Dear Carol,

Thank you for a great site and a very helpful book!

Still, I have encountered a problem I hope you can assist me with.

I am comparing the performance of equally and exponentially weighted moving average models on the SP 500 index under both 95% and 99% confidence levels. The plan is to see how they both behave during a crisis and a tranquil period.

After transforming the daily data into log-returns I apply the EWMA equation to calculate a new variance and STD each day. However, I am unsure on where to go from there. The resulting figures are only positive so the percentile function in Excel does not work. I tried to circumvent the problem by multiplying the STD with -1. That did not work very well. My windows are producing what seems to be very inaccurate results and there is almost no difference between the 95% and 99% confidence levels.

Therefore. I am wondering what I am doing wrong and I have been unable to find help anywhere else. I think the answer is trivial and that I am missing how to turn variance into adjusted return. But when i tried this, the models severely overestimated risk.

I have attached the Excel sheet where sheet 1 is the exponentially weighted and sheet 2 are the equally weighted, but the latter should be correct.

I really hope you can help, but I know that you are extremely busy!

Thank you and have a great night!

Kind regards,

Nicolai

Ps. Hope you are enjoying Sussex!

Thank you for a great site and a very helpful book!

Still, I have encountered a problem I hope you can assist me with.

I am comparing the performance of equally and exponentially weighted moving average models on the SP 500 index under both 95% and 99% confidence levels. The plan is to see how they both behave during a crisis and a tranquil period.

After transforming the daily data into log-returns I apply the EWMA equation to calculate a new variance and STD each day. However, I am unsure on where to go from there. The resulting figures are only positive so the percentile function in Excel does not work. I tried to circumvent the problem by multiplying the STD with -1. That did not work very well. My windows are producing what seems to be very inaccurate results and there is almost no difference between the 95% and 99% confidence levels.

Therefore. I am wondering what I am doing wrong and I have been unable to find help anywhere else. I think the answer is trivial and that I am missing how to turn variance into adjusted return. But when i tried this, the models severely overestimated risk.

I have attached the Excel sheet where sheet 1 is the exponentially weighted and sheet 2 are the equally weighted, but the latter should be correct.

I really hope you can help, but I know that you are extremely busy!

Thank you and have a great night!

Kind regards,

Nicolai

Ps. Hope you are enjoying Sussex!