project for fina 462 class
touramirou
OptionStrategyAnalyzer10e.xlsmStrategy Inputs
| General Inputs: | |||||||||||
| Asset price when transaction is initiated | (necessary only if buying or selling the asset) | ||||||||||
| # of units of asset (>0 Þ buy, < 0 Þ sell) | 0 | (necessary only if buying or selling the asset) | |||||||||
| Asset price at expiration | 125 | (required for centering graph) | |||||||||
| Lowest asset price to graph | 85 | (required) | |||||||||
| Highest asset price to graph | 165 | (required) | |||||||||
| Option-Specific Inputs: | |||||||||||
| Option 1 | Option 2 | Option 3 | Option 4 | Option 5 | Option 6 | Asset | |||||
| Choose "Call", "Put" or "No option" | Call | No option | No option | No option | No option | No option | NA | ||||
| Exercise Price (X) | 125 | NA | |||||||||
| Original option price | 13.5000 | NA | |||||||||
| # of units (> 0 Þ buy, < 0 Þ sell) | 100 | 0 | |||||||||
| Time to expiration when position closed | NA | ||||||||||
| Risk-free rate (rc) | NA | ||||||||||
| Volatility (s) | NA | ||||||||||
| Dividend yield (dc) | NA | ||||||||||
| Output: Option value and profit at close of position for asset price chosen above | |||||||||||
| Value at close of position | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 125.0000 | ||||
| Profit at close of position | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | ||||
| Overall profit at close of position | -1,350.00 | ||||||||||
| Data Table for Graph Results | |||||||||||
| Asset price at end of holding period | Profitabilty of Each Component of the Strategy | ||||||||||
| Option 1 | Option 2 | Option 3 | Option 4 | Option 5 | Option 6 | Asset | Overall | ||||
| 85.00 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 86.63 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 88.27 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 89.90 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 91.53 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 93.16 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 94.80 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 96.43 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 98.06 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 99.69 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 101.33 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 102.96 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 104.59 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 106.22 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 107.86 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 109.49 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 111.12 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 112.76 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 114.39 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 116.02 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 117.65 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 119.29 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 120.92 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 122.55 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 124.18 | -1,350.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,350.00 | |||
| 125.82 | -1,268.37 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,268.37 | |||
| 127.45 | -1,105.10 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -1,105.10 | |||
| 129.08 | -941.84 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -941.84 | |||
| 130.71 | -778.57 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -778.57 | |||
| 132.35 | -615.31 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -615.31 | |||
| 133.98 | -452.04 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -452.04 | |||
| 135.61 | -288.78 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -288.78 | |||
| 137.24 | -125.51 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | -125.51 | |||
| 138.88 | 37.76 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 37.76 | |||
| 140.51 | 201.02 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 201.02 | |||
| 142.14 | 364.29 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 364.29 | |||
| 143.78 | 527.55 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 527.55 | |||
| 145.41 | 690.82 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 690.82 | |||
| 147.04 | 854.08 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 854.08 | |||
| 148.67 | 1,017.35 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,017.35 | |||
| 150.31 | 1,180.61 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,180.61 | |||
| 151.94 | 1,343.88 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,343.88 | |||
| 153.57 | 1,507.14 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,507.14 | |||
| 155.20 | 1,670.41 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,670.41 | |||
| 156.84 | 1,833.67 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,833.67 | |||
| 158.47 | 1,996.94 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 1,996.94 | |||
| 160.10 | 2,160.20 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2,160.20 | |||
| 161.73 | 2,323.47 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2,323.47 | |||
| 163.37 | 2,486.73 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2,486.73 | |||
| 165.00 | 2,650.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 2,650.00 | |||
| Reference cells - do not change | |||||||||||
| Call | Call | Call | Call | Call | Call | ||||||
| Put | Put | Put | Put | Put | Put | ||||||
| No option | No option | No option | No option | No option | No option |
OPTION STRATEGY ANALYZER 10e
The information to the left is a table of the profit for a range of 50 asset prices at expiration between your specified maximum and minimum. Two graphs are provided on accompanying worksheet tabs. One is called Component Graph, which contains graphs of the profits of all of the options and the underlying asset used in the strategy, as well as the overall profit. Because graphs of all of the components can look cluttered, another tab called Overall Graph is also provided, which shows only the overall profit. Note: It is not possible to design a spreadsheet that will provide perfectly displayed graphs for all conceivable option strategies. Some graphs may have unusual curved lines or kinks. These result from ticks not centered precisely on the exercise prices. If this occurs, you may wish to try different maximum and minimum prices.
Component Graph
Profit from Strategy -
Overall and by Component
85 86.632653061224488 88.265306122448976 89.897959183673464 91.530612244897952 93.16326530612244 94.795918367346928 96.428571428571416 98.061224489795904 99.693877551020393 101.32653061224488 102.95918367346937 104.59183673469386 106.22448979591834 107.85714285714283 109.48979591836732 111.12244897959181 112.7551020408163 114.38775510204079 116.02040816326527 117.65306122448976 119.28571428571425 120.91836734693874 122.55102040816323 124.18367346938771 125.8163265306122 127.44897959183669 129.08163265306118 130.71428571428567 132.34693877551015 133.97959183673464 135.61224489795913 137.24489795918362 138.87755102040811 140.51020408163259 142.14285714285708 143.77551020408157 145.40816326530606 147.04081632653055 148.67346938775503 150.30612244897952 151.93877551020401 153.5714285714285 155.20408163265299 156.83673469387747 158.46938775510196 160.10204081632645 161.73469387755094 163.36734693877543 164.99999999999991 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1268.36734693878 -1105.1020408163311 -941.83673469388225 -778.57142857143344 -615.30612244898464 -452.04081 632653583 -288.77551020408703 -125.51020408163822 37.755102040810584 201.02040816325939 364.2857142857082 527.551020408157 690.81632653060581 854.08163265305461 1017.3469387755034 1180.6122448979522 1343.8775510204009 1507.1428571428498 1670.4081632652988 1833.6734693877474 1996.9387755101961 2160.2040816326453 2323.469387755094 2486.7346938775427 2649.9999999999914 85 86.632653061224488 88.265306122448976 89.897959183673464 91.530612244897952 93.16326530612244 94.795918367346928 96.428571428571416 98.061224489795904 99.693877551020393 101.32653061224488 102.95918367346937 104.59183673469386 106.22448979591834 107.85714285714283 109.48979591836732 111.12244897959181 112.7551020408163 114.38775510204079 116.02040816326527 117.65306122448976 119.28571428571425 120.91836734693874 122.55102040816323 124.18367346938771 125.8163265306122 127.44897959183669 129.08163265306118 130.71428571428567 132.34693877551015 133.97959183673464 135.61224489795913 137.24489795918362 138.87755102040811 140.51020408163259 142.14285714285708 143.77551020408157 145.40816326530606 147.04081632653055 148.67346938775503 150.30612244897952 151.93877551020401 153.5714285714285 155.20408163265299 156.83673469387747 158.46938775510196 160.10204081632645 161.73469387755094 163.36734693877543 164.99999999999991 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 85 86.632653061224488 88.265306122448976 89.897959 183673464 91.530612244897952 93.16326530612244 94.795918367346928 96.428571428571416 98.061224489795904 99.693877551020393 101.32653061224488 102.95918367346937 104.59183673469386 106.22448979591834 107.85714285714283 109.48979591836732 111.12244897959181 112.7551020408163 114.38775510204079 116.02040816326527 117.65306122448976 119.28571428571425 120.91836734693874 122.55102040816323 124.18367346938771 125.8163265306122 127.44897959183669 129.08163265306118 130.71428571428567 132.34693877551015 133.97959183673464 135.61224489795913 137.24489795918362 138.87755102040811 140.51020408163259 142.14285714285708 143.77551020408157 145.40816326530606 147.04081632653055 148.67346938775503 150.30612244897952 151.93877551020401 153.5714285714285 155.20408163265299 156.83673469387747 158.46938775510196 160.10204081632645 161.73469387755094 163.36734693877543 164.99999999999991 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 85 86.632653061224488 88.265306122448976 89.897959183673464 91.530612244897952 93.16326530612244 94.795918367346928 96.428571428571416 98.061224489795904 99.693877551020393 101.32653061224488 102.95918367346937 104.59183673469386 106.22448979591834 107.85714285714283 109.48979591836732 111.12244897959181 112.7551020408163 114.38775510204079 116.02040816326527 117.65306122448976 119.28571428571425 120.91836734693874 122.55102040816323 124.18367346938771 125.8163265306122 127.44897959183669 129.08163265306118 130.71428571428567 132.34693877551015 133.97959183673464 135.61224489795913 137.24489795918362 138.877551 02040811 140.51020408163259 142.14285714285708 143.77551020408157 145.40816326530606 147.04081632653055 148.67346938775503 150.30612244897952 151.93877551020401 153.5714285714285 155.20408163265299 156.83673469387747 158.46938775510196 160.10204081632645 161.73469387755094 163.36734693877543 164.99999999999991 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 85 86.632653061224488 88.265306122448976 89.897959183673464 91.530612244897952 93.16326530612244 94.795918367346928 96.428571428571416 98.061224489795904 99.693877551020393 101.32653061224488 102.95918367346937 104.59183673469386 106.22448979591834 107.85714285714283 109.48979591836732 111.12244897959181 112.7551020408163 114.38775510204079 116.02040816326527 117.65306122448976 119.28571428571425 120.91836734693874 122.55102040816323 124.18367346938771 125.8163265306122 127.44897959183669 129.08163265306118 130.71428571428567 132.34693877551015 133.97959183673464 135.61224489795913 137.24489795918362 138.87755102040811 140.51020408163259 142.14285714285708 143.77551020408157 145.40816326530606 147.04081632653055 148.67346938775503 150.30612244897952 151.93877551020401 153.5714285714285 155.20408163265299 156.83673469387747 158.46938775510196 160.10204081632645 161.73469387755094 163.36734693877543 164.99999999999991 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 85 86.632653061224488 88.265306122448976 89.897959183673464 91.530612244897952 93.16326530612244 94.795918367346928 96.428571428571416 98.061224489795904 99.693877551020393 101.32653061224488 102.95918367346937 104.59183673469386 106.22448979591834 107.85714285714283 109.48979591836732 111.12244897959181 112.7551020408163 114.38775510204079 116.02040816326527 117.65306122448976 119.28571428571425 120.91836734693874 122.55102040816323 124.18367346938771 125.8163265306122 127.44897959183669 129.08163265306118 130.71428571428567 132.34693877551015 133.97959183673464 135.61224489795913 137.24489795918362 138.87755102040811 140.51020408163259 142.14285714285708 143.77551020408157 145.40816326530606 147.04081632653055 148.67346938775503 150.30612244897952 151.93877551020401 153.5714285714285 155.20408163265299 156.83673469387747 158.46938775510196 160.10204081632645 161.73469387755094 163. 36734693877543 164.99999999999991 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 85 86.632653061224488 88.265306122448976 89.897959183673464 91.530612244897952 93.16326530612244 94.795918367346928 96.428571428571416 98.061224489795904 99.693877551020393 101.32653061224488 102.95918367346937 104.59183673469386 106.22448979591834 107.85714285714283 109.48979591836732 111.12244897959181 112.7551020408163 114.38775510204079 116.02040816326527 117.65306122448976 119.28571428571425 120.91836734693874 122.55102040816323 124.18367346938771 125.8163265306122 127.44897959183669 129.08163265306118 130.71428571428567 132.34693877551015 133.97959183673464 135.61224489795913 137.24489795918362 138.87755102040811 140.51020408163259 142.14285714285708 143.77551020408157 145.40816326530606 147.04081632653055 148.67346938775503 150.30612244897952 151.93877551020401 153.5714285714285 155.20408163265299 156.83673469387747 158.46938775510196 160.10204081632645 161.73469387755094 163.36734693877543 164.99999999999991 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 85 86.632653061224488 88.265306122448976 89.897959183673464 91.530612244897952 93.16326530612244 94.795918367346928 96.428571428571416 98.061224489795904 99.693877551020393 101.32653061224488 102.95918367346937 104.59183673469386 106.22448979591834 107.85714285714283 109.48979591836732 111.12244897959181 112.7551020408163 114.38775510204079 116.02040816326527 117.65306122448976 119.28571428571425 120.91836734693874 122.55102040816323 124.18367346938771 125.8163265306122 127.44897959183669 129.08163265306118 130.71428571428567 132.34693877551015 133.97959183673464 135.61224489795913 137.24489795918362 138.87755102040811 140.51020408163259 142.14285714285708 143.77551020408157 145.40816326530606 147.04081632653055 148.67346938775503 150.30612244897952 151.93877551020401 153.5714285714285 155.20408163265299 156.83673469387747 158.46938775510196 160.10204081632645 161.73469387755094 163.36734693877543 164.99999999999991 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1268.36734693878 -1105.1020408163311 -941.83673469388225 -778.57142857143344 -615.30612244898464 -452.04081632653583 -288.77551020408703 -125.51020408163822 37.755102040810584 201.02040816325939 364.2857142857082 527.551020408157 690.81632653060581 854.08163265305461 1017.3469387755034 1180.6122448979522 1343.8775510204009 1507.1428571428498 1670.4081632652988 1833.6734693877474 1996.9387755101961 2160.2040816326453 2323.469387755094 2486.7346938775427 2649.9999999999914Asset Price at Expiration ($)
Profit ($)
Overall Graph
Profit from Strategy
85 86.632653061224488 88.265306122448976 89.897959183673464 91.530612244897952 93.16326530612244 94.795918367346928 96.428571428571416 98.061224489795904 99.693877551020393 101.32653061224488 102.95918367346937 104.59183673469386 106.22448979591834 107.85714285714283 109.48979591836732 111.12244897959181 112.7551020408163 114.38775510204079 116.02040816326527 117.65306122448976 119.28571428571425 120.91836734693874 122.55102040816323 124.18367346938771 125.8163265306122 127.44897959183669 129.08163265306118 130.71428571428567 132.34693877551015 133.97959183673464 135.61224489795913 137.24489795918362 138.87755102040811 140.51020408163259 142.14285714285708 143.77551020408157 145.40816326530606 147.04081632653055 148.67346938775503 150.30612244897952 151.93877551020401 153.5714285714285 155.20408163265299 156.83673469387747 158.46938775510196 160.10204081632645 161.73469387755094 163.36734693877543 164.99999999999991 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1350 -1268.36734693878 -1105.1020408163311 -941.83673469388225 -778.57142857143344 -615.30612244898464 -452.04081632653583 -288.77551020408703 -125.51020408163822 37.755102040810584 201.02040816325939 364.2857142857082 527.551020408157 690.81632653060581 854.08163265305461 1017.3469387755034 1180.6122448979522 1343.8775 510204009 1507.1428571428498 1670.4081632652988 1833.6734693877474 1996.9387755101961 2160.2040816326453 2323.469387755094 2486.7346938775427 2649.9999999999914Asset Price at Expiration ($)
Profit ($)
Instructions
Instructions: Fill in the information for the General Inputs in the highlighted cells. Choose a maximum and minimum asset price for graphing purposes. Enter a positive number of units of the asset and/or options if you are buying and a negative number if you are selling. You must fill in an "Asset price at expiration," but this can be any asset price that is between your maximum and minimum. For Option-Specific Inputs, enter information on up to six different options in cells D to I. These options can be either calls or puts (choose from the pull down list), can have different exercise prices and different times to expiration but must be on the same underlying asset. Make sure you correctly supply the time remaining until expiration and the yield. The risk-free rate and the standard deviation when the position is closed are required only if closing prior to expiration. You do not need to enter any input information information for the underlying asset in this section in Column K. For positions closed prior to expiration, option values are estimated using the Black-Scholes-Merton model; therefore, European options are assumed. Hit F9 to recalculate. One tab contains the Component Graph, which is a graph of the profits of all of the options and the underlying asset used in the strategy, as well as the overall profit. Because graphs of all of the components can look cluttered, another tab called Overall Graph is also provided, which shows only the overall profit. The overall profit line is the thick dark line in both graphs.
About
Written by Don M. Chance and Robert Brooks For use with An Introduction to Derivatives and Risk Management, 10th ed. (Mason, Ohio: Cengage, 2015) Date: 7/09 Last updated: 3/18/14 © 2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
