problems21

UN-14PCHOOSING A SYNTHETIC PUT EXERCISE PRICEz 0.9500 Insurance levelS 100 Current stock priceX 106.473 Exercise pricer 6.00% Risk-free rate of interestT 1 Time to maturity of option (in years)Sigma 30% Stock volatilityd1 0.1409 0.0001If PutOption(High + Low) / 2, Exercise, Time, Interest, sigma) + _(High + Low) / 2 Target ThenHigh = (High + Low) / 2Else: Low = (High + Low) / 2End IfLoopImpliedAssetPrice = (High + Low) / 2End FunctionFunction dOne(Stock, Exercise, Time, Interest, sigma)dOne = (Log(Stock / Exercise) + Interest * Time) / (sigma * Sqr(Time) _+ 0.5 * sigma * Sqr(Time)End FunctionFunction CallOption(Stock, Exercise, Time, Interest, sigma)CallOption = Stock * Application.NormSDist(dOne(Stock, Exercise, _Time, Interest, sigma) - Exercise * Exp(-Time * Interest) * _Application.NormSDist(dOne(Stock, Exercise, Time, Interest, sigma) _- sigma * Sqr(Time)End FunctionPut pricing function uses put-call parity theoremFunction PutOption(Stock, Exercise, Time, Interest, sigma)PutOption = CallOption(Stock, Exercise, Time, Interest, sigma) _+ Exercise * Exp(-Interest * Time) - StockEnd FunctionThis line references cell B17, and checks that the function works as required.Exercise 3 中文Page 13隐含的看跌期权和资产的价值Va 94.89235该行对应单元格B17, 检查函数是否按照要求运行.X 90保证返还的资金r 10.00% 无风险利率T 1期权到期时间(年)sigma 30% 股票波动性d1 0.6598 0.0001If PutOption(High + Low) / 2, Exercise, Time, Interest, sigma) + _(High + Low) / 2 Target ThenHigh = (High + Low) / 2Else: Low = (High + Low) / 2End IfLoopImpliedAssetPrice = (High + Low) / 2End FunctionFunction dOne(Stock, Exercise, Time, Interest, sigma)dOne = (Log(Stock / Exercise) + Interest * Time) / (sigma * Sqr(Time) _+ 0.5 * sigma * Sqr(Time)End FunctionFunction CallOption(Stock, Exercise, Time, Interest, sigma)CallOption = Stock * Application.NormSDist(dOne(Stock, Exercise, _Time, Interest, sigma) - Exercise * Exp(-Time * Interest) * _Application.NormSDist(dOne(Stock, Exercise, Time, Interest, sigma) _- sigma * Sqr(Time)End FunctionPut pricing function uses put-call parity theoremFunction PutOption(Stock, Exercise, Time, Interest, sigma)PutOption = CallOption(Stock, Exercise, Time, Interest, sigma) _+ Exercise * Exp(-Interest * Time) - StockEnd FunctionExercise 4Page 15The solution uses the function defined in the previous exerciseTrue value of a share 42.025Target 55X 50T 0.5r 10%Sigma 80%Exercise 4 中文Page 16用前面的练习中定义的函数解每股的真实价值 42.025目标 55X 50T 0.5r 10%Sigma 80%Exercise 5Page 17COVERED CALL SIMULATIONX 45Mean stock return 12%Sigma 40%r 6%Initial stock price, S0 50Initial call price 11.7393 - =bscall(B6,B2,1,B5,B4)Initial investment incovered call 38.2607 - =B6-B7Simulated payoffof strategy 45.6927 - =K66Payoff of covered callstrategy 45.0000 - =J66-MAX(J66-B2,0)Week Time Stock price d1 Investedin stock Investedin bondTotalinvestmentat beginningof week0 0.0000 50.00 0.6134 13.4903 24.7705 38.26071 0.0192 49.72 0.5986 13.6599 24.5549 38.21492 0.0385 48.41 0.5292 14.4408 23.4403 37.88113 0.0577 48.54 0.5348 14.3873 23.5608 37.94824 0.0769 47.44 0.4737 15.0794 22.5701 37.64955 0.0962 45.21 0.3452 16.5017 20.4660 36.96776 0.1154 41.22 0.0959 19.0351 16.4981 35.53327 0.1346 38.10 -0.1217 20.8956 13.2165 34.11218 0.1538 39.86 -0.0074 20.0494 15.0459 35.09539 0.1731 39.57 -0.0352 20.3408 14.6241 34.964910 0.1923 38.10 -0.1487 21.3002 12.9237 34.223911 0.2115 36.63 -0.2688 22.1949 11.2225 33.417412 0.2308 36.10 -0.3213 22.5981 10.5115 33.109613 0.2500 35.01 -0.4219 23.2247 9.2130 32.437714 0.2692 38.15 -0.1836 21.8546 12.6814 34.536015 0.2885 41.10 0.0263 20.1174 16.1196 36.237016 0.3077 43.55 0.1928 18.4462 19.0105 37.456717 0.3269 45.17 0.2986 17.2831 20.8815 38.164618 0.3462 52.44 0.7563 11.7855 29.1870 40.972519 0.3654 48.73 0.5285 14.5489 25.6217 40.170620 0.3846 56.05 0.9745 9.2433 33.1444 42.387821 0.4038 58.39 1.1134 7.7515 35.0594 42.810822 0.4231 56.83 1.0342 8.5547 34.0903 42.645023 0.4423 57.09 1.0580 8.2794 34.4438 42.723224 0.4615 59.77 1.2236 6.6072 36.5437 43.150925 0.4808 58.72 1.1754 7.0412 36.0362 43.077426 0.5000 61.70 1.3634 5.3299 38.1466 43.476527 0.5192 60.78 1.3268 5.6098 37.8316 43.441528 0.5385 67.06 1.7060 2.9513 41.1133 44.064629 0.5577 69.74 1.8795 2.0984 42.1313 44.229730 0.5769 74.08 2.1434 1.1882 43.2207 44.4089()0012012Long stockShort callLong positionLong positionin bondin stock1rTrTSSNdXeNdSNdXeNd-+=-+14444244443142431442443Exercise 5Page 1831 0.5962 78.08 2.3901 0.6576 43.8654 44.523032 0.6154 73.08 2.1720 1.0911 43.4405 44.531633 0.6346 70.35 2.0594 1.3877 43.1531 44.540934 0.6538 69.43 2.0486 1.4060 43.1666 44.572635 0.6731 75.30 2.4514 0.5359 44.2055 44.741436 0.6923 72.27 2.3293 0.7171 44.0538 44.770837 0.7115 71.09 2.3162 0.7303 44.0797 44.809938 0.7308 67.85 2.1599 1.0443 43.7833 44.827639 0.7500 65.40 2.0446 1.3374 43.5031 44.840540 0.7692 69.09 2.3996 0.5671 44.3991 44.966241 0.7885 80.84 3.3454 0.0332 45.0807 45.113942 0.8077 81.00 3.5042 0.0185 45.1474 45.166043 0.8269 75.25 3.2353 0.0457 45.1711 45.216844 0.8462 81.02 3.8856 0.0041 45.2683 45.272445 0.8654 88.23 4.7157 0.0001 45.3250 45.325146 0.8846 94.68 5.5934 0.0000 45.3774 45.377447 0.9038 102.80 6.7692 0.0000 45.4298 45.429848 0.9231 95.70 6.8981 0.0000 45.4823 45.482349 0.9423 97.05 8.0834 0.0000 45.5348 45.534850 0.9615 95.33 9.6378 0.0000 45.5873 45.587351 0.9808 88.18 12.1761 0.0000 45.6400 45.6400Exercise 5Page 19Random Zat end ofweekStockprice atend ofweekInvestmentat the endof week-0.1414 49.7240 38.2149-0.5259 48.4060 37.88110.0082 48.5400 37.9482-0.4547 47.4405 37.6495-0.9084 45.2135 36.9677-1.7093 41.2185 35.5332-1.4599 38.1000 34.11210.7747 39.8648 35.0953-0.1749 39.5710 34.9649-0.7262 38.0966 34.2239-0.7505 36.6275 33.4174-0.3041 36.0981 33.1096-0.5957 35.0055 32.43771.5102 38.1522 34.53601.2984 41.0960 36.23701.0038 43.5495 37.45670.6167 45.1692 38.16462.6507 52.4445 40.9725-1.3674 48.7261 40.17062.4837 56.0527 42.38780.6936 58.3860 42.8108-0.5279 56.8321 42.64500.0401 57.0902 42.72320.7840 59.7654 43.1509-0.3601 58.7189 43.07740.8511 61.6997 43.4765-0.3110 60.7847 43.44151.7305 67.0632 44.06460.6631 69.7367 44.22971.0467 74.0763 44.40890.9063 78.0753 44.5230COVERED CALL SIMULATION()0012012Long stockShort callLong positionLong positionin bondin stock1rTrTSSNdXeNdSNdXeNd-+=-+14444244443142431442443Exercise 5Page 20-1.2325 73.0842 44.5316-0.7295 70.3479 44.5409-0.2784 69.4300 44.57261.4226 75.3045 44.7414-0.7831 72.2698 44.7708-0.3395 71.0854 44.8099-0.8823 67.8467 44.8276-0.7028 65.4034 44.84050.9475 69.0921 44.96622.7901 80.8435 45.1139-0.0072 80.9979 45.1660-1.3684 75.2507 45.21681.2909 81.0236 45.27241.4936 88.2257 45.32511.2312 94.6796 45.37741.4425 102.8039 45.4298-1.3334 95.6953 45.48230.2115 97.0483 45.5348-0.3639 95.3288 45.5873-1.4470 88.1797 45.6400-0.2500 87.1660 45.6927保看涨期权利润模式未来股票价格，ST 50看涨期权执行价格,X 45看涨期权价格 6T时现金流多头股票,+ST 50 - =B2空头看涨期权现金流,-MaxST-X,0 -5 - =-MAX(B2-B3,0)总现金流 45 - =B7+B8现金流的模拟运算表未来股票价格,ST 450 05 510 1015 1520 2025 2530 3035 3540 4045 4550 4555 4560 4565 4570 4575 450 5 10 15 20 25 30 35 40 45 50 55 60 65 70 7505101520253035404550股 票 价 格 , ST0 5 10 15 20 25 30 35 40 45 50 55 60 65 70 7501020304050Series1 Series2010203040506070800 5 10 15 20 25 30 35 40 45 50 55 60 65 70 7505101520253035404550股 票 价 格 , ST0 5 10 15 20 25 30 35 40 45 50 55 60 65 70 7501020304050Series1 Series2Exercise 5 中文Page 23保看涨期权模拟X 45平均股票收益 12%Sigma 40%r 6%初始股票价格, S0 50初始看涨期权价格, S0 11.7393 - =bscall(B6,B2,1,B5,B4)隐含看涨期权的初始投资 38.2607 - =B6-B7策略的模拟现金流 45.7032 - =K66保看涨期权策略的现金流 45.0000 - =J66-MAX(J66-B2,0)星期 时间 股票价格 d1 股票投资 债券投资 星期开始时的总投资0 0.0000 50.00 0.6134 13.4903 24.7705 38.26071 0.0192 46.77 0.4438 15.3671 22.0497 37.41682 0.0385 47.18 0.4638 15.1636 22.4144 37.57803 0.0577 54.50 0.8329 11.0331 28.9227 39.95584 0.0769 54.53 0.8360 10.9917 29.0037 39.99545 0.0962 54.28 0.8256 11.1003 28.8780 39.97836 0.1154 49.80 0.5983 13.6841 25.4110 39.09527 0.1346 45.49 0.3546 16.4410 21.5001 37.94118 0.1538 45.78 0.3684 16.3097 21.7596 38.06939 0.1731 45.73 0.3626 16.3929 21.6857 38.078710 0.1923 42.61 0.1628 18.5500 18.4354 36.985411 0.2115 41.99 0.1158 19.0588 17.6773 36.736012 0.2308 42.84 0.1665 18.5857 18.5551 37.140813 0.2500 44.78 0.2888 17.3007 20.7037 38.004414 0.2692 48.69 0.5295 14.5194 25.0198 39.539215 0.2885 48.70 0.5293 14.5261 25.0452 39.571316 0.3077 50.96 0.6651 12.8929 27.3829 40.275817 0.3269 54.60 0.8765 10.3955 30.8322 41.227718 0.3462 63.05 1.3258 5.8296 37.0423 42.871919 0.3654 68.42 1.5936 3.7981 39.6127 43.410920 0.3846 65.33 1.4625 4.6909 38.5942 43.285121 0.4038 67.55 1.5853 3.8131 39.6758 43.488922 0.4231 74.02 1.9040 2.1063 41.7941 43.900423 0.4423 72.29 1.8483 2.3337 41.5657 43.899324 0.4615 75.66 2.0271 1.6136 42.4426 44.056225 0.4808 79.40 2.2224 1.0423 43.1426 44.185026 0.5000 79.70 2.2683 0.9290 43.3096 44.238627 0.5192 86.92 2.6165 0.3861 43.9868 44.372928 0.5385 87.25 2.6743 0.3267 44.0984 44.425129 0.5577 93.38 2.9769 0.1359 44.3630 44.499030 0.5769 92.33 2.9899 0.1288 44.4198 44.548731 0.5962 90.65 2.9774 0.1317 44.4659 44.597632 0.6154 96.76 3.3031 0.0463 44.6115 44.657833 0.6346 100.51 3.5353 0.0205 44.6906 44.711134 0.6538 97.13 3.4751 0.0248 44.7372 44.7620()0012012Long stockShort callLong positionLong positionin bondin stock1rTrTSSNdXeNdSNdXeNd-+=-+14444244443142431442443Exercise 5 中文Page 2435 0.6731 103.88 3.8579 0.0059 44.8095 44.815436 0.6923 100.21 3.8023 0.0072 44.8597 44.866937 0.7115 102.10 4.0015 0.0032 44.9156 44.918838 0.7308 95.93 3.8287 0.0062 44.9643 44.970539 0.7500 100.59 4.1968 0.0014 45.0214 45.022740 0.7692 103.12 4.4836 0.0004 45.0744 45.074741 0.7885 114.12 5.2192 0.0000 45.1268 45.126842 0.8077 117.74 5.6369 0.0000 45.1789 45.178943 0.8269 114.35 5.7501 0.0000 45.2311 45.231144 0.8462 118.48 6.3076 0.0000 45.2833 45.283345 0.8654 114.73 6.5054 0.0000 45.3356 45.335646 0.8846 112.32 6.8506 0.0000 45.3879 45.387947 0.9038 110.30 7.3367 0.0000 45.4403 45.440348 0.9231 113.08 8.4026 0.0000 45.4928 45.492849 0.9423 108.58 9.2521 0.0000 45.5453 45.545350 0.9615 103.45 10.6797 0.0000 45.5979 45.597951 0.9808 108.75 15.9555 0.0000 45.6505 45.6505Exercise 5 中文Page 25星期结束时的随机数Z星期结束时的股票价格星期结束时的投资-1.2470 46.7661 37.41680.1170 47.1793 37.57802.5578 54.4968 39.9558-0.0314 54.5277 39.9954-0.1249 54.2763 39.9783-1.5950 49.7953 39.0952-1.6722 45.4891 37.94110.0714 45.7751 38.0693-0.0590 45.7308 38.0787-1.3154 42.6111 36.9854-0.3065 41.9894 36.73600.3184 42.8363 37.14080.7573 44.7773 38.00441.4678 48.6877 39.5392-0.0375 48.6986 39.57130.7779 50.9634 40.27581.2013 54.6011 41.22772.5522 63.0503 42.87191.4309 68.4165 43.4109-0.8745 65.3274 43.28510.5603 67.5454 43.48891.6092 74.0227 43.9004-0.4685 72.2903 43.89930.7801 75.6616 44.05620.8286 79.4035 44.18500.0247 79.6963 44.23861.5235 86.9244 44.37290.0259 87.2506 44.42511.1823 93.3798 44.4990-0.2459 92.3277 44.5487-0.3727 90.6474 44.59761.1344 96.7575 44.65780.6451 100.5144 44.7111-0.6592 97.1290 44.76201.1696 103.8791 44.8154保看涨期权模拟 ()0012012Long stockShort callLong positionLong positionin bondin stock1rTrTSSNdXeNdSNdXeNd-+=-+14444244443142431442443Exercise 5 中文Page 26-0.6904 100.2073 44.86690.2956 102.0994 44.9188-1.1651 95.9307 44.97050.8127 100.5864 45.02270.4069 103.1204 45.07471.7855 114.1196 45.12680.5221 117.7440 45.1789-0.5681 114.3548 45.23110.5973 118.4802 45.2833-0.6220 114.7264 45.3356-0.4242 112.3171 45.3879-0.3682 110.3003 45.44030.4069 113.0786 45.4928-0.7733 108.5810 45.5453-0.9148 103.4470 45.59790.8591 108.7469 45.65050.2561 110.5579 45.7032Exercise 5 templatePage 27COVERED CALL SIMULATIONInitial stock price, S0 50X 45Mean stock return 12%Sigma 40%Interest rate, r 6%Initial call price 11.7393 - =bscall(B2,B3,1,B6,B5)Initial investment incovered call 38.2607 - =B2-B8Simulated payoffof strategy ?Payoff of covered callstrategy ?Week Time Stock price d1 Investedin stock Investedin bondTotalinvestmentat beginningof week0 0.0000 50.00 0.6134 13.4903 24.7705 38.26071 0.0192 52.90 0.7547 11.9128 27.1578 39.07062 0.038548 0.923149 0.942350 0.961551 0.9808A B C D E F G12345678910111213141516171864656667Exercise 5 templatePage 28RandomZ at endof weekStockprice atend ofweekInvestmentat the end ofweek0.9733 52.8955 39.0706()0012012Long stockShort callLong positionLong positionin bondin stock1rTrTSSNdXeNdSNdXeNd-+=-+14444244443142431442443COVERED CALL SIMULATION H I J K L M N O P12345678910111213141516171864656667Exercise 5 templatePage 29()0012012Long stockShort callLong positionLong positionin bondin stock1rTrTSSNdXeNdSNdXeNd-+=-+14444244443142431442443Q R S12345678910111213141516171864656667Exercise 5 template 中文Page 30保看涨期权模拟初始股票价格, S0 50X 45平均股票收益 12%Sigma 40%利率, r 6%看涨期权初始价格 11.7393 - =bscall(B2,B3,1,B6,B5)保看涨期权的初始投资 38.2607 - =B2-B8策略的模拟现金流 ?保看涨期权策略的现金流?星期 时间 股票价格 d1 股票投资 债券投资 星期开始时的总投资0 0.0000 50.00 0.6134 13.4903 24.7705 38.26071 0.0192 50.46 0.6356 13.2465 25.1662 38.41272 0.038548 0.923149 0.942350 0.961551 0.9808A B C D E F G12345678910111213141516171864656667Exercise 5 template 中文Page 31星期结束时的随机数Z星期结束时的股票价格星期结束时的投资0.1225 50.4572 38.4127()0012012Long stockShort callLong positionLong positionin bondin stock1rTrTSSNdXeNdSNdXeNd-+=-+14444244443142431442443保看涨期权模拟 H I J K L M N O12345678910111213141516171864656667Exercise 5 template 中文Page 32()0012012Long stockShort callLong positionLong positionin bondin stock1rTrTSSNdXeNdSNdXeNd-+=-+14444244443142431442443P Q R S12345678910111213141516171864656667