UN-11IASIAN OPTION PICTUREInitial stock price 30Up 1.4Down 0.8R, 1+interest rate 1.08Exercise price 50State pricesqu 0.4321 - =(B5-B4)/(B5*(B3-B4)qd 0.4938 - =(B3-B5)/(B5*(B3-B4)Risk-neutral probabilitiespu 0.4667 - =B9*$B$5pd 0.5333 - =B10*$B$5115.25 - =G18*B3Stock price 82.3258.80 65.86 - =G18*B442.00 47.0430.00 33.60 37.63 - =G20*B424.00 26.8819.20 21.50 - =G22*B415.3612.29 - =G24*B41.3605 - =G29*$B$5Bond price 1.25971.1664 1.3605 - =G31*$B$51.0800 1.25971.0000 1.1664 1.36051.0800 1.25971.1664 1.36051.25971.3605A B C D E F G H I J123456789101112131415161718192021222324252627282930313233343536UN-11I亚洲式期权定价初始股票价格 30上涨 1.4下跌 0.8R, 1+利率 1.08执行价格 50状态价格qu 0.4321 - =(B5-B4)/(B5*(B3-B4)qd 0.4938 - =(B3-B5)/(B5*(B3-B4)风险中性概率pu 0.4667 - =B9*$B$5pd 0.5333 - =B10*$B$5115.25 - =G18*B3股票价格 82.3258.80 65.86 - =G18*B442.00 47.0430.00 33.60 37.63 - =G20*B424.00 26.8819.20 21.50 - =G22*B415.3612.29 - =G24*B41.3605 - =G29*$B$5债券价格 1.25971.1664 1.3605 - =G31*$B$51.0800 1.25971.0000 1.1664 1.36051.0800 1.25971.1664 1.36051.25971.3605A B C D E F G H I J123456789101112131415161718192021222324252627282930313233343536PRICING AN ASIAN OPTION BY PRICING ALL THE PATHSInitial stock price 30Up 1.40Down 0.80Interest 1.08Option exercise price 30Formula in cell O16: =$B$114State price, Up: qU 0.4321 - =(B5-B4)/(B5*(B3-B4) Formula in cell O18: =$B$113*$B$12State price, Down, qD 0.4938 - =(B3-B5)/(B5*(B3-B4)Formula in cell M16 =AVERAGE(G16:K16)Risk-neutral prob., Up 0.4667 - =B8*B5Risk-neutral prob., Down 0.5333 - =B9*B5 Formula in cell N16 =MAX(M16-$B$6,0)STOCK PRICEPathsPeriod1Period2Period3Period4Period0Period1Period2Period3Period4 Averagestock price OptionpayoffPathrisk-neutralprobabilityAll up (1 path) up up up up 30.00 42.00 58.80 82.32 115.25 65.67 35.67 0.0474One down (4 paths) down up up up 30.00 24.00 33.60 47.04 65.86 40.10 10.10 0.0542up down up up 30.00 42.00 33.60 47.04 65.86 43.70 13.70 0.0542up up down up 30.00 42.00 58.80 47.04 65.86 48.74 18.74 0.0542up up up down 30.00 42.00 58.80 82.32 65.86 55.80 25.80 0.0542Two down (6 paths) down down up up 30.00 24.00 19.20 26.88 37.63 27.54 0.00 0.0619down up down up 30.00 24.00 33.60 26.88 37.63 30.42 0.42 0.0619down up up down 30.00 24.00 33.60 47.04 37.63 34.45 4.45 0.0619up down down up 30.00 42.00 33.60 26.88 37.63 34.02 4.02 0.0619up up down down 30.00 42.00 58.80 47.04 37.63 43.09 13.09 0.0619up down up down 30.00 42.00 33.60 47.04 37.63 38.05 8.05 0.0619Three down (4 paths) up down down down 30.00 42.00 33.60 26.88 21.50 30.80 0.80 0.0708down up down down 30.00 24.00 33.60 26.88 21.50 27.20 0.00 0.0708down down up down 30.00 24.00 19.20 26.88 21.50 24.32 0.00 0.0708down down down up 30.00 24.00 19.20 15.36 21.50 22.01 0.00 0.0708Four down (1 path) down down down down 30.00 24.00 19.20 15.36 12.29 20.17 0.00 0.0809Option value 5.3756Formula in cell N37 =SUMPRODUCT(N16:N35,O16:O35)/B5430A B C D E F G H I J K L M N O12345678910111213141516171819202122232425262728293031323334353637383940414243444546通过所有路径的定价来定价一个亚洲式期权初始股票价格 30上涨 1.40下跌 0.80利率 1.08期权执行价格 30Formula in cell O16: =$B$114状态价格, 上涨: qU 0.4321 - =(B5-B4)/(B5*(B3-B4) Formula in cell O18: =$B$113*$B$12状态价格, 下跌: qD 0.4938 - =(B3-B5)/(B5*(B3-B4)Formula in cell M16 =AVERAGE(G16:K16)风险中性概率,上涨 0.4667 - =B8*B5风险中性概率, 下跌 0.5333 - =B9*B5 Formula in cell N16 =MAX(M16-$B$6,0)股票价格路径时期1时期2时期3时期4时期0时期1时期2时期3时期4 平均股票价格 期权收益路径风险中性概率全部上涨(1条路径) 上涨 上涨 上涨 上涨 30.00 42.00 58.80 82.32 115.25 65.67 35.67 0.0474有一个下跌(4条路径) 下跌 上涨 上涨 上涨 30.00 0.00 0.00 0.00 0.00 6.00 0.00 0.0542上涨 下跌 上涨 上涨 30.00 0.00 0.00 0.00 0.00 6.00 0.00 0.0542上涨 上涨 下跌 上涨 30.00 0.00 0.00 0.00 0.00 6.00 0.00 0.0542上涨 上涨 上涨 下跌 30.00 0.00 0.00 0.00 0.00 6.00 0.00 0.0542有两个下跌(6条路径) 下跌 下跌 上涨 上涨 30.00 0.00 0.00 0.00 0.00 6.00 0.00 0.0619下跌 上涨 下跌 上涨 30.00 0.00 0.00 0.00 0.00 6.00 0.00 0.0619下跌 上涨 上涨 下跌 30.00 0.00 0.00 0.00 0.00 6.00 0.00 0.0619上涨 下跌 下跌 上涨 30.00 0.00 0.00 0.00 0.00 6.00 0.00 0.0619上涨 上涨 下跌 下跌 30.00 0.00 0.00 0.00 0.00 6.00 0.00 0.0619上涨 下跌 上涨 下跌 30.00 0.00 0.00 0.00 0.00 6.00 0.00 0.0619有三个下跌(4条路径) 上涨 下跌 下跌 下跌 30.00 0.00 0.00 0.00 0.00 6.00 0.00 0.0708下跌 上涨 下跌 下跌 30.00 0.00 0.00 0.00 0.00 6.00 0.00 0.0708下跌 下跌 上涨 下跌 30.00 0.00 0.00 0.00 0.00 6.00 0.00 0.0708下跌 下跌 下跌 上涨 30.00 0.00 0.00 0.00 0.00 6.00 0.00 0.0708有四个下跌 (1条路径) 下跌 下跌 下跌 下跌 30.00 0.00 0.00 0.00 0.00 6.00 0.00 0.0809Option value 1.2436Formula in cell N37 =SUMPRODUCT(N16:N35,O16:O35)/B5430A B C D E F G H I J K L M N O12345678910111213141516171819202122232425262728293031323334353637383940414243444546PATH PRICE EXAMPLE: Up, Down, Up, UpInitial stock price 30Up 1.40Down 0.80Interest 1.08Option exercise price 30State price, Up: qU 0.4321State price, Down, qD 0.4938Risk-neutral prob., Up 0.4667Risk-neutral prob., Down 0.5333DatePrice atbeginning ofperiod0 30.0001 42.0002 33.6003 47.0404 65.856Average price alongpath 43.699Option payoff at path end 13.699Path risk-neutral price 0.0542Value of path: Payoff *risk-neutral price *discount factor 0.546A B1234567891011121314151617181920212223- =(B5-B4)/(B5*(B3-B4)- =(B3-B5)/(B5*(B3-B4)- =B8*B5- =B9*B5Price movement:Up or DownUpDownUpUp- =AVERAGE(B15:B19)- =MAX(B20-$B$6,0)- =B11COUNTIF(C16:C19,Up)*B12COUNTIF(C16:C19,Down)- =B21*B22/B54PATH PRICE EXAMPLE: Up, Down, Up, UpC1234567891011121314151617181920212223路径定价例子: 上涨, 下跌, 上涨, 上涨初始股票价格 30上涨 1.40下跌 0.80利率 1.08期权执行价格 30状态价格,上涨: qU 0.4321 - =(B5-B4)/(B5*(B3-B4)状态价格,下跌, qD 0.4938 - =(B3-B5)/(B5*(B3-B4)风险中性概率, 上涨 0.4667 - =B8*B5风险中性概率, 下跌 0.5333 - =B9*B5日期 期初股票价格 价格运动:上涨或下跌0 30.0001 24.000 上涨2 19.200 下跌3 15.360 上涨4 12.288 上涨路径上的平均价格 20.170 - =AVERAGE(B15:B19)路径末期权收益 0.000 - =MAX(B20-$B$6,0)路径风险中性价格 1.0000 - =B11COUNTIF(C16:C19,Up)*B12COUNTIF(C16:C19,Down)路径价值: 收益 *风险中性价格 * 折现因素 0.000 - =B21*B22/B54- =B11COUNTIF(C16:C19,Up)*B12COUNTIF(C16:C19,Down)PATH PRICE EXAMPLE: Up, Up, Down, UpInitial stock price 30Up 1.40Down 0.80Interest 1.08Option exercise price 30State price, Up: qU 0.4321State price, Down, qD 0.4938Risk-neutral prob., Up 0.4667Risk-neutral prob., Down 0.5333DatePrice atbeginning ofperiod0 30.0001 42.0002 58.8003 47.0404 65.856Average price alongpath 48.739Option payoff at path end 18.739Path risk-neutral price 0.0542Value of path: Payoff *risk-neutral price *discount factor 0.747A B1234567891011121314151617181920212223- =(B5-B4)/(B5*(B3-B4)- =(B3-B5)/(B5*(B3-B4)- =B8*B5- =B9*B5Price movement:Up or DownUpUpDownUp- =AVERAGE(B15:B19)- =MAX(B20-$B$6,0)- =B11COUNTIF(C16:C19,Up)*B12COUNTIF(C16:C19,Down)- =B21*B22/B54PATH PRICE EXAMPLE: Up, Up, Down, UpC1234567891011121314151617181920212223路径定价例子: 上涨, 上涨, 下跌, 上涨初始股票价格 30上涨 1.40下跌 0.80利率 1.08期权执行价格 30状态价格, 上涨: qU 0.4321 - =(B5-B4)/(B5*(B3-B4)状态价格, 下跌, qD 0.4938 - =(B3-B5)/(B5*(B3-B4)风险中性概率, 上涨 0.4667 - =B8*B5风险中性概率, 下跌 0.5333 - =B9*B5日期 期初股票价格 价格运动:上涨或下跌0 30.0001 24.000 上涨2 19.200 上涨3 15.360 下跌4 12.288 上涨沿着路径的平均价格 20.170 - =AVERAGE(B15:B19)路径末期权收益 0.000 - =MAX(B20-$B$6,0)路径风险中性价格 1.0000 - =B11COUNTIF(C16:C19,Up)*B12COUNTIF(C16:C19,Down)路径价值: 收益 *风险中性价格 * 折现因素 0.000 - =B21*B22/B54- =B11COUNTIF(C16:C19,Up)*B12COUNTIF(C16:C19,Down)PRICING AN ASIAN OPTION BY MONTE CARLOUp 1.4Down 0.8Interest 1.08Initialprice 30Periods 20Exercise 30Runs 500Asian call value 9.4484 - =MCAsian(B5,B7,B2,B3,B4,B6,B8)A B C123456789用Monte Carlo定价亚洲式期权上涨 1.4下跌 0.8利率 1.08初始价格 30时期 20执行价格 30运行次数 500亚洲式看涨期权 10.1802 - =MCAsian(B5,B7,B2,B3,B4,B6,B8)PRICING AN ASIAN OPTION-VBA FUNCTIONPrices an Asian option with 4 periods and 100 runs for each simulationUp 1.4Down 0.8Interest 1.08Initialprice 30Periods 4Exercise 30Runs 1005.4842 5.4099 6.0295 4.8358 4.2958 5.99174.0923 6.3881 5.6730 5.4129 4.8765 6.17296.0235 5.8602 6.6810 7.2343 4.6384 5.83245.6166 5.1628 6.3122 5.4818 6.4283 4.67394.7999 5.7679 5.6881 5.8788 4.1928 4.49205.2174 4.8458 3.8410 5.9422 4.6223 6.51645.8747 4.8664 4.5613 5.9849 5.1515 4.08545.1372 4.6520 5.2864 5.5266 5.8404 5.6223Average of MC simulations 5.3959 - =AVERAGE(A10:F17)True value 5.3756 - From Section 23.53.8410 - =MIN(A10:F17)7.2343 - =MAX(A10:F17)0.7590 - =STDEV(A10:F17)A B C D E F123456789101112131415161718192021222324- =MCAsian($B$5,$B$7,$B$2,$B$3,$B$4,$B$6,$B$8)PRICING AN ASIAN OPTION-VBA FUNCTIONPrices an Asian option with 4 periods and 100 runs for each simulationG123456789101112131415161718192021222324定价亚洲式期权-VBA函数定价4时期亚洲式期权和每次模拟运行100次上涨 1.4下跌 0.8利率 1.08初始价格 30时期 4执行价格 30运行次数 1004.9063 4.6332 4.6521 5.5227 6.0926 6.0872 - =MCAsian($B$5,$B$7,$B$2,$B$3,$B$4,$B$6,$B$8)5.1518 5.8252 5.3694 4.7338 4.9161 5.13665.7348 6.3072 5.3080 4.8956 4.7691 3.47344.3863 6.4721 5.3316 4.5266 4.4090 4.74376.1843 7.1251 4.9664 5.1082 5.2907 4.80095.6427 4.0516 5.2521 6.2299 5.2485 4.75546.8269 6.0458 4.5872 5.3813 5.0329 5.40544.7612 5.8483 5.4775 5.6871 6.2377 4.8643MC模拟的平均 5.2957 - =AVERAGE(A10:F17)正确的价值 5.3756 - 来自第23.5节3.4734 - =MIN(A10:F17)7.1251 - =MAX(A10:F17)0.7248 - =STDEV(A10:F17)- =MCAsian($B$5,$B$7,$B$2,$B$3,$B$4,$B$6,$B$8)PRICING AN ASIAN OPTION-VBA FUNCTIONPrices an Asian option with 4 periods and 500 runs for each simulationUp 1.4Down 0.8Interest 1.08Initialprice 30Periods 4Exercise 30Runs 5005.6677 5.2199 5.9997 5.4267 5.8145 5.58735.5054 5.4224 5.1495 5.2688 5.6717 4.98595.6181 5.4527 5.2545 5.6696 5.8652 5.68825.0774 5.0153 4.9860 5.1649 5.2995 4.98835.0958 5.3495 5.1898 5.7911 5.3608 5.23005.1337 5.3441 5.2490 5.5103 5.7316 5.09905.2835 5.4058 5.2684 5.3098 5.5030 4.32055.3115 5.6745 5.3938 6.0123 5.0025 5.0864Average of MC simulations 5.3637 - =AVERAGE(A10:F17)True value 5.3756 - From Section 23.54.3205 - =MIN(A10:F17)6.0123 - =MAX(A10:F17)0.3131 - =STDEV(A10:F17)A B C D E F123456789101112131415161718192021222324- =MCAsian($B$5,$B$7,$B$2,$B$3,$B$4,$B$6,$B$8)PRICING AN ASIAN OPTION-VBA FUNCTIONPrices an Asian option with 4 periods and 500 runs for each simulationG123456789101112131415161718192021222324定价亚洲式期权-VBA函数定价4时期亚洲式看涨期权和每次模拟运行500次上涨 1.4下跌 0.8利率 1.08初始价格 30时期数 4执行价格 30运行次数 5004.9417 5.2052 5.3724 4.7591 5.6878 5.3974 - =MCAsian($B$5,$B$7,$B$2,$B$3,$B$4,$B$6,$B$8)5.1696 4.9492 5.3470 5.2819 5.8235 5.49955.2510 5.5442 4.8955 5.6695 5.2680 5.36955.4582 5.5391 5.2097 5.4603 5.7330 5.05624.7071 5.3702 5.6943 5.8280 5.5784 5.29725.2769 5.1035 5.3178 5.1197 5.8088 5.40355.9314 5.4812 5.3001 5.3182 5.4302 5.29405.3688 5.5638 5.1643 5.5939 5.4582 5.4680MC模拟的平均 5.3701 - =AVERAGE(A10:F17)正确的价值 5.3756 - From Section 23.54.7071 - =MIN(A10:F17)5.9314 - =MAX(A10:F17)0.2704 - =STDEV(A10:F17)- =MCAsian($B$5,$B$7,$B$2,$B$3,$B$4,$B$6,$B$8)PRICING AN ASIAN OPTION-VBA FUNCTIONEach time interval is divided into n subintervals. In this simulation the initial stock price = 50.00, theexercise price = 45.00, the time to maturity = 0.40, and the unit time interval is divided into 80subintervals. The stock price process has mean return = 15.00% and standard deviation = 1.25%, andthe interest rate = 8.00%.There are 100 runs in each Monte Carlo simulationS0, current stock price 50X, exercise price 45T, time to option exercise 0.4r, interest rate 8%m, mean stock return 15%s, standard deviation of stock return 22%n, number of sub-intervals of T 80Delta t 0.0125 - =1/B9Up over 1 sub-interval 1.0268 - =EXP(B6*B10+B7*SQRT(B10)Down over 1 sub-interval 0.9775 - =EXP(B6*B10-B7*SQRT(B10)Interest over 1 sub-interval 1.0010 - =EXP(B5*B10)Runs 1006.2409 5.9107 5.2993 5.9240 5.6887 5.2933 - =MCAsian($B$2,$B$3,$B$12,$B$13,$B$14,$B$4*$B$9,$B$16)5.6692 5.5351 6.4359 5.3044 5.1691 6.25805.2912 5.9722 5.6046 5.1419 5.7701 5.31975.5721 6.4302 5.6997 5.5385 5.8714 5.18785.4885 6.1108 5.5020 5.5535 5.9044 5.43925.8171 5.5510 5.3851 6.9461 5.4935 5.47815.7870 6.1115 5.7614 6.2868 5.4895 6.35095.5330 6.0383 5.7236 5.5504 6.2562 5.6149Average of above 5.7354 - =AVERAGE(A18:F25)Minimum 5.1419 - =MIN(A18:F25)Maxmimum 6.9461 - =MAX(A18:F25)Standard deviation 0.3911 - =STDEV(A18:F25)Data table: Sensitivity of results on number of runsRunsAverageof 48MCAsianMinimum MaximumStandarddeviation- =B30 , data table header (hidden)50 5.7399 4.1641 6.9573 0.5727100 5.6250 4.6283 6.3578 0.3672150 5.6572 4.9230 6.1428 0.2829200 5.7483 5.2027 6.2811 0.2787250 5.7288 5.2286 6.4007 0.2800300 5.6721 5.1972 6.1940 0.2142350 5.7466 5.2496 6.1119 0.21374 8 M C A s ia n S im u la tio n s fo r V a r io u s N u m b e r s o f R u n s5 5 05 0 04 5 04 0 03 5 03 0 02 5 02 0 01 5 01 0 05 00 .0 0 01 .0 0 02 .0 0 03 .0 0 04 .0 0 05 .0 0 06 .0 0 07 .0 0 08 .0 0 0R u n s p e r s im u la tio nA v e r a g eo f 4 8 M C A s ia nM in im u mM a x im u mS ta n d a rdd e v ia tio nA B C D E F G1234567891011121314151617181920212223242526272829303132333435363738394041400 5.7512 5.3201 6.2248 0.2197450 5.7079 5.2769 6.1493 0.1690500 5.7141 5.2231 6.0914 0.2098550 5.7173 5.3921 6.0089 0.15224 8 M C A s ia n S im u la tio n s fo r V a r io u s N u m b e r s o f R u n s5 5 05 0 04 5 04 0 03 5 03 0 02 5 02 0 01 5 01 0 05 00 .0 0 01 .0 0 02 .0 0 03 .0 0 04 .0 0 05 .0 0 06 .0 0 07 .0 0 08 .0 0 0R u n s p e r s im u la tio nA v e r a g eo f 4 8 M C A s ia nM in im u mM a x im u mS ta n d a rdd e v ia tio nA B C D E F G42434445464748495051525354555657PRICING AN ASIAN OPTION-VBA FUNCTIONEach time interval is divided into n subintervals. In this simulation the initial stock price = 50.00, theexercise price = 45.00, the time to maturity = 0.40, and the unit time interval is divided into 80subintervals. The stock price process has mean return = 15.00% and standard deviation = 1.25%, andthe interest rate = 8.00%.There are 100 runs in each Monte Carlo simulation- =MCAsian($B$2,$B$3,$B$12,$B$13,$B$14,$B$4*$B$9,$B$16)- =B30 , data table header (hidden)4 8 M C A s ia n S im u la tio n s fo r V a r io u s N u m b e r s o f R u n s5 5 05 0 04 5 04 0 03 5 03 0 02 5 02 0 01 5 01 0 05 00 .0 0 01 .0 0 02 .0 0 03 .0 0 04 .0 0 05 .0 0 06 .0 0 07 .0 0 08 .0 0 0R u n s p e r s im u la tio nA v e r a g eo f 4 8 M C A s ia nM in im u mM a x im u mS ta n d a rdd e v ia tio nH I J K12345678910111213141516171819202122232425262728293031323334353637383940414 8 M C A s ia n S im u la tio n s fo r V a r io u s N u m b e r s o f R u n s5 5 05 0 04 5 04 0 03 5 03 0 02 5 02 0 01 5 01 0 05 00 .0 0 01 .0 0 02 .0 0 03 .0 0 04 .0 0 05 .0 0 06 .0 0 07 .0 0 08 .0 0 0R u n s p e r s im u la tio nA v e r a g eo f 4 8 M C A s ia nM in im u mM a x im u mS ta n d a rdd e v ia tio nH I J K42434445464748495051525354555657定价亚洲式期权-VBA函数每个时间间隔划分为n个区间。模拟中初始股票价格=50.00,执行价格 = 45.00, 到期时间 = 0.40, 单位时间间隔划分为 80个子区间。股票价格过程平均收益 = 15.00% 标准差 = 1.25%,利率 = 8.00%。每次Monte Carlo模拟运行100次S0, 当前股票价格 50X, 执行价格 45T, 期权执行时间 0.4r, 利率 8%m, 平均股票收益 15%s, 股票收益的标准差22%n, T的子时间段数