title: Calculating an exponentially weighted moving average
body: <p>I am trying a fairly simple function to calculate an exponentially weighted moving average volatility in Excel VBA, <a href="http://www.value-at-risk.net/exponentially-weighted-moving-average-ewma/" rel="nofollow">following</a>. However, I think there is some error in my function that I can't pinpoint, because I don't get the correct solution.</p>
  
  <pre><code> Function EWMA(numbers As Range, Lambda As Single) As Double
  
  Dim mean As Double
  
  Dim x As Double
  
  Dim c As Range
  
  Dim n As Integer
  
  mean = WorksheetFunction.Average(numbers)
  
  n = WorksheetFunction.Count(numbers)
  
  For Each c In numbers
  
      x = x + (Lambda ^ (n - c.Count)) * ((c.Value - mean) ^ 2)
  
  Next c
  
  EWMA = (1 - Lambda) * x
  
  End Function
  </code></pre>
  
  <p>The values I am using and the target volatility (calculated using a spreadsheet EWMA) are <a href="http://www.filedropper.com/ewma" rel="nofollow">here</a>. </p>
  
  <p>What am I doing wrong?</p>
  
  <p><strong>Update:</strong> using Ron Rosenfeld's solution: </p>
  
  <pre><code>Option Explicit
  
  Function EWMA(Zero As Range, Lambda As Double) As Double
  
      Dim vZero As Variant
      Dim SumWtdRtn As Double
      Dim I As Long
      Dim vPrices As Variant
  
      Dim LogRtn As Double, RtnSQ As Double, WT As Double, WtdRtn As Double
  
      vZero = Zero
  
  For I = 2 To UBound(vZero, 1)
  
      vPrices = 1 / ((1 + vZero(I, 1)) ^ (3 / 12))
      LogRtn = Log(vPrices(I - 1, 1) / vPrices(I, 1))
      RtnSQ = LogRtn ^ 2
      WT = (1 - Lambda) * Lambda ^ (I - 2)
      WtdRtn = WT * RtnSQ
      SumWtdRtn = SumWtdRtn + WtdRtn
  
  Next I
  
  EWMA = SumWtdRtn ^ (1 / 2)
  
  End Function
  </code></pre>