Basic Black-Scholes by garrickWilliams

VIEWS: 733 PAGES: 2

									                                   A                               B        C      D         E
 1                                                              Inputs
 2   Type of option (1 for call, 2 for put)                        1
 3   Stock price                                                  35
 4   Exercise price                                               40
 5   Duration (years)                                             0.5
 6   Riskfree interest rate                                      0.05
                                                                                   =IF(B2=1,NORMSDIST(B10),NORMSDIST(-B
 7   Volatility                                                   0.4
 8                                            =(LN(B3/B4)+(B6+B7^2/2)*B5)/(B7*SQRT(B5))
 9   Quantities for Black-Scholes formula
10   d1                                                         -0.242           N(d1)     0.404
11   d2                                                            -0.525        N(d2)     0.300
12                                                    =B10-SQRT(B7^2*B5)
                                                                                 =IF(B2=1,NORMSDIST(B11),NORMSDIST(-B
13   Option price                                                2.456
14
15              =IF(B2=1,B3*E10-B4*EXP(-B5*B6)*E11,-(B3*E10-B4*EXP(-B5*B6)*E11))
16
                    F        G           H
             1
             2
             3
             4
             5
             6
=IF(B2=1,NORMSDIST(B10),NORMSDIST(-B10))
             7
             8
             9
            10
            11
            12
IF(B2=1,NORMSDIST(B11),NORMSDIST(-B11))
            13
            14
            15
            16

								
To top