Black-Scholes Problems

[mbshafe]

If anyone is familiar with the Black-Scholes option pricing model, I would greatly appreciate some help. I have replicated all of the calculation logic but I'm having trouble with time to maturity. It's supposed to be the number of days between trade date and expiry date divided by 365 to convert it to years. But when I do this my results become less accurate as time to maturity decreases. I have access to several other models that produce correct results, so I don't know what is wrong with my model.

Here are the inputs to a very simple example:

Cell M3 -- Underlying Price $102
M4 -- Exercise Price $100
M5 -- Today's Date 8/9/12
M6 -- Expiry Date 8/10/12
M7 -- Volatility 25%
M8 -- Risk Free Rate 5%
M9 -- Dividend Yield 1%
M10 -- Time to Expiry = 1 / 365 = 0.00274

I compute the following:

M12 -- d1 = -1.4984 = (LN(M4/M3)+(M8-M9+0.5*M7^2)*M10)/(M7*SQRT(M10))

M13 -- Nd1 = 0.1298 = EXP(-(M12^2)/2)/SQRT(2*PI())

M14 -- d2 = -1.5115 = M12-M7*SQRT(M10)

M15 -- Nd2 = NORMSDIST(M14)

M17 -- Call Price = 0.303 = EXP(-M9*M10)*M3*NORMSDIST(M12)-M4*EXP(-M8*M10)*NORMSDIST(M12-M7*SQRT(M10))

So, the call price of 0.303 makes no sense because the option is $2 in the money with 1 day until expiry. I have confirmed using several different calculators (that don't show me the calculation logic) that the price of the option should be pretty close to $2.

I'm busting my head against the wall with this. Can anyone help?

 

[shg]

I have no idea of this is correct, and don't understand the relevance of dividend yield

       ------K------- --L-- -----M----- -------------------------------N--------------------------------
   1        Desc      Name     Value                                                                    
   2     Strike Price     K    $ 102.00 M2: Input                                                       
   3       Spot Price     S    $ 100.00 M3: Input                                                       
   4     Today's Date       09 Aug 2012 M4: Input                                                       
   5      Expiry Date       10 Aug 2012 M5: Input                                                       
   6       Volatility   sig         24% M6: Input                                                       
   7   Risk Free Rate   rho          5% M7: Input                                                       
   8   Dividend Yield                1% What's this for?                                                
   9   Time to Expiry   TTE     0.00274 M9: =(M5-M4)/365                                                
  10                                                                                                    
  11               d1 dddd1     -1.5592 M11: = (LN(S/K) + (rho + sig^2/2) * TTE) / (sig*SQRT(TTE))      
  12               d2 dddd2     -1.5717 M12: = dddd1 - M6 * SQRT(M9)                                    
  13       Call Price            $ 2.02 M13: =NORMSDIST(-dddd2) * K * EXP(-rho*M9) - NORMSDIST(-dddd1)*S

 

[mbshafe]

I've been trying to do this on S&P index options and with those you have to adjust the index value using the dividend yield...I can't tell you how much I appreciate this!

 

[shg]

Sorted then? You're welcome.

 

[RonAkke]

The price should be equal to 2.025 for the call option. There must be a small error within your formula although it's kind of difficult to tell.

Try using the formula here