Page 1 of 1

Exponentially Weighted Moving Average in Excel

Posted: Wed Mar 20, 2013 6:35 pm
by NicolaiL
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,


Ps. Hope you are enjoying Sussex!

Re: Exponentially Weighted Moving Average in Excel

Posted: Thu Mar 21, 2013 9:30 pm
by coalexander
You appear to be rather mixed up. EWMA std is for a normal (or student t) VaR formula. You cannot multiply this by -1 and take into a percentile calculation, which is for historical VaR.

Column E should not be multiplied by -1. Just use normal VaR formula with this sigma.

Percentile is applied to log returns data.

Suggest you read Vol IV more closely.

Re: Exponentially Weighted Moving Average in Excel

Posted: Sat Mar 23, 2013 5:24 pm
by NicolaiL
Thank you for answering.

Yes, I severely misunderstood, sorry.

I will transform the EWMA estimates into ln returns again by applying the formula found in the sheet "RtnAGG" in case study IV.3.3.1 scale index before using the percentile function.

Have a good weekend.