Exponentially Weighted Moving Average in Excel

Discussion on Value-at-Risk Models
Forum rules
DISCLAIMER: We do not warrant or represent that this forum or its content is free of viruses, worms or other code that might be contaminating or destructive. We cannot guarantee that documents or files downloaded from the Site will be free from viruses and we do not accept any responsibility for any damage or loss caused by any virus. Accordingly, for your own protection, you must use virus-checking software when using the forum. You must not post or provide to us via the forum, any document or file which you believe may contain a virus. You must virus check any document or file which you intend to post or provide to us via the forum. You must ensure that any document or file you intend to post to the forum does not contravene any applicable laws or contravene any person's legal rights. We do not accept any responsibility for any damage or loss you may suffer.
NicolaiL
Posts: 2
Joined: Wed Mar 20, 2013 6:09 pm

Exponentially Weighted Moving Average in Excel

Postby NicolaiL » 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!
Last edited by NicolaiL on Sat Mar 23, 2013 5:25 pm, edited 1 time in total.

coalexander
Posts: 815
Joined: Sun Sep 28, 2008 10:30 pm

Re: Exponentially Weighted Moving Average in Excel

Postby coalexander » Thu Mar 21, 2013 9:30 pm

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.

NicolaiL
Posts: 2
Joined: Wed Mar 20, 2013 6:09 pm

Re: Exponentially Weighted Moving Average in Excel

Postby NicolaiL » Sat Mar 23, 2013 5:24 pm

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.


Return to “Volume IV”

Who is online

Users browsing this forum: No registered users and 1 guest