0

i am trying to calculate the implied volatility in excel. This is funaction i am using: 

=+_xlfn.STDEV.P(LN(INDEX($B$2:$B$486,MATCH(G3,$A$2:$A$486,0)):INDEX($B$2:$B$486,MATCH(G3,$A$2:$A$486,0)+30)/INDEX($B$2:$B$486,MATCH(G3,$A$2:$A$486,0)+1):INDEX($B$2:$B$486,MATCH(G3,$A$2:$A$486,0)+31))*SQRT(252))

G3 - price date

column A - dates

Columns B - Stock prices

Why is this not working?

user1607
  • 531
  • 7
  • 28
  • What result do you get? – barry houdini Dec 09 '17 at 19:42
  • @Barry houdini i get `#name? ` (i made a mistake in the code, just edited it) – user1607 Dec 09 '17 at 19:51
  • if you get xlfn.STDEV.P that indicates that STDEV.P function might not be recognised (hence also #NAME? error). Which version of Excel are you using - STEDV.P is only available in Excel 2010 or later? – barry houdini Dec 09 '17 at 20:02
  • I am using excel 2007. I tried to use the `=STDEVP((LN(INDEX($B$2:$B$486,MATCH(G3,$A$2:$A$486,0)):INDEX($B$2:$B$486,MATCH(G3,$A$2:$A$486,0)+30)/INDEX($B$2:$B$486,MATCH(G3,$A$2:$A$486,0)+1):INDEX($B$2:$B$486,MATCH(G3,$A$2:$A$486,0)+31))*SQRT(252)))` but i get `N/A` there – user1607 Dec 09 '17 at 21:08
  • OK, just to check - this is an array formula so you need to confirm with CTRL+SHIFT+ENTER. Also G3 date must have an exact match in A2:A486, is that the case? – barry houdini Dec 09 '17 at 21:23

1 Answers1

0

You might want to consider this alternative approach.

Cells F3 and F4 are inputs that determine the first day of the interval and the number of days in the stdevp calculation.

The formulas in the snapshot below are:

Cell C3 =LN(B2/B3)*SQRT(252) Cell F7 =STDEVP(OFFSET(A1,MATCH(F3,A:A,0)-1,2,F4,1)) Cell F5 checks to make sure the chosen range is valid =IF(OR(F3<MIN(A:A),F3+F4>MAX(A:A)),"out of range", "in range")

Cell F8 is a replica of the original formula.

Column B uses randbetween(1,1000) to generate test sets. enter image description here

John Goodhue
  • 131
  • 4