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.

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.

Helpful and informative!!

ReplyDelete"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

Hello, very good job.

ReplyDeleteI want to ask why dollar ($) is included in EMA formula calculation?

Thank you in adnance

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