AmazonH

Saturday, July 7, 2012

How to calculate Exponential Moving Average in Excel

How to calculate Exponential Moving Average illustrated with an excel example.

Have a look at the spreadsheet below. Once it opens to download simply press Ctrl+S

https://docs.google.com/open?id=0B1Gms0A3xDrLQU0tQXc4ZklpOEU



Now the formulas used.


1. Exponent function value - this is essentially the weight used to weigh each new period.

= 2/(1+ no of periods)
= 2/(1+20) for a 20 period EMA
=2/21
=0.0952380952380952


2. Actual calculation of EMA

= Previous EMA * (1-Exponent) + Current Close * Exponent

One clarification here. For calculation of the first EMA we simply use a SMA as value of EMA needs a previous value and thus we need to start somewhere. This does not affect the value of the EMA after the first 5-6 values.


For any clarifications or queries, please leave a comment.

3 comments:

  1. Helpful and informative!!

    "For calculation of the first EMA we simply use a SMA as value of EMA needs a previous value and thus we need to start somewhere. This does not affect the value of the EMA after the first 5-6 values." This was really the key.
    Thnx

    ReplyDelete
  2. Hello, very good job.
    I want to ask why dollar ($) is included in EMA formula calculation?
    Thank you in adnance

    ReplyDelete
  3. Hi, the '$' is used in Excel to reference the same cell over and over ( i.e., an "absolute reference" rather than a relative ref. ) for example, $A$1 ALWAYS refers to cell A1,even if you copy and paste that reference to another cell. Does that make sense ?

    ReplyDelete

AmazonH