Uncategorized

RealOptionsSpreadhSheetforHSM4301.xls

Copyright

Real Options Exercises
Harvard Business School Case 9-208-045
Courseware 9-208-703

Tables A & B

Real Options Exercises
Problem 2
Table A
Round 1
Year 0 1 2 3 4 5 6 7 8 9
After-tax cash flow 10.0 12.0 15.0 15.5 15.9 16.4 16.9 17.4 17.9
Investment -145.0
Net cash flow -145.0 10.0 12.0 15.0 15.5 15.9 16.4 16.9 17.4 17.9
Round 2
Year 0 1 2 3 4 5 6 7 8 9
After-tax cash flow 20.0 24.0 30.0 30.9 31.8 32.8
Investment -250.0
Net cash flow 0 0 0 -250.0 20.0 24.0 30.0 30.9 31.8 32.8
Round 3
Year 0 1 2 3 4 5 6 7 8 9
After-tax cash flow 40.0 48.0 60.0
Investment -450.0
Net cash flow 0 0 0 0 0 0 -450.0 40.0 48.0 60.0
Table B
Rounds 1-3
Year 0 1 2 3 4 5 6 7 8 9
After-tax cash flow 0.0 10.0 12.0 15.0 35.5 39.9 46.4 87.8 97.2 110.7
Investment -145.0 -250.0 -450.0
Net cash flow -145.0 10.0 12.0 -235.0 35.5 39.9 -403.6 87.8 97.2 110.7

Appendix

Black-Scholes-Merton Value of a European Call Option, Expressed as a Percentage of Underlying Asset Value
NPVq = (Underlying asset value) / PV(Exercise price)
0.30 0.35 0.40 0.45 0.50 0.55 0.60 0.65 0.70 0.75 0.80 0.82 0.84 0.86 0.88 0.90 0.92 0.94 0.96 0.98 1.00 1.02 1.04 1.06 1.08 1.10 1.12 1.14 1.16 1.18 1.20 1.25 1.30 1.35 1.40 1.45 1.50 1.75 2.00 2.50
0.05 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.1 0.3 0.6 1.2 2.0 3.1 4.5 6.0 7.5 9.1 10.7 12.3 13.8 15.3 16.7 20.0 23.1 25.9 28.6 31.0 33.3 42.9 50.0 60.0 0.05
0.10 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.1 0.2 0.3 0.5 0.8 1.2 1.7 2.3 3.1 4.0 5.0 6.1 7.3 8.6 10.0 11.3 12.7 14.1 15.4 16.8 20.0 23.1 25.9 28.6 31.0 33.3 42.9 50.0 60.0 0.10
0.15 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.1 0.2 0.5 0.7 1.0 1.3 1.7 2.2 2.8 3.5 4.2 5.1 6.0 7.0 8.0 9.1 10.2 11.4 12.6 13.8 15.0 16.2 17.4 20.4 23.3 26.0 28.6 31.1 33.3 42.9 50.0 60.0 0.15
0.20 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.1 0.4 0.8 1.5 1.9 2.3 2.8 3.4 4.0 4.7 5.4 6.2 7.1 8.0 8.9 9.9 10.9 11.9 13.0 14.1 15.2 16.3 17.4 18.5 21.2 23.9 26.4 28.9 31.2 33.5 42.9 50.0 60.0 0.20
0.25 0.0 0.0 0.0 0.0 0.0 0.1 0.2 0.5 1.0 1.8 2.8 3.3 3.9 4.5 5.2 5.9 6.6 7.4 8.2 9.1 9.9 10.9 11.8 12.8 13.7 14.7 15.7 16.7 17.7 18.7 19.8 22.3 24.7 27.1 29.4 31.7 33.8 42.9 50.0 60.0 0.25
0.30 0.0 0.0 0.0 0.1 0.1 0.3 0.7 1.2 2.0 3.1 4.4 5.0 5.7 6.3 7.0 7.8 8.6 9.4 10.2 11.1 11.9 12.8 13.7 14.6 15.6 16.5 17.4 18.4 19.3 20.3 21.2 23.5 25.8 28.1 30.2 32.3 34.3 43.1 50.1 60.0 0.30
0.35 0.0 0.0 0.1 0.2 0.4 0.8 1.4 2.3 3.3 4.6 6.2 6.8 7.5 8.2 9.0 9.8 10.6 11.4 12.2 13.0 13.9 14.8 15.6 16.5 17.4 18.3 19.2 20.1 21.0 21.9 22.7 24.9 27.1 29.2 31.2 33.2 35.1 43.5 50.2 60.0 0.35
0.40 0.0 0.1 0.2 0.5 0.9 1.6 2.4 3.5 4.8 6.3 8.0 8.7 9.4 10.2 11.0 11.7 12.5 13.4 14.2 15.0 15.9 16.7 17.5 18.4 19.2 20.1 20.9 21.8 22.6 23.5 24.3 26.4 28.4 30.4 32.3 34.2 36.0 44.0 50.5 60.1 0.40
0.45 0.1 0.2 0.5 1.0 1.7 2.6 3.7 5.0 6.5 8.1 9.9 10.6 11.4 12.2 12.9 13.7 14.5 15.3 16.2 17.0 17.8 18.6 19.4 20.3 21.1 21.9 22.7 23.5 24.3 25.1 25.9 27.9 29.8 31.7 33.5 35.3 37.0 44.6 50.8 60.2 0.45
0.50 0.2 0.5 1.0 1.7 2.6 3.7 5.1 6.6 8.2 10.0 11.8 12.6 13.4 14.2 14.9 15.7 16.5 17.3 18.1 18.9 19.7 20.5 21.3 22.1 22.9 23.7 24.5 25.3 26.1 26.8 27.6 29.5 31.3 33.1 34.8 36.4 38.1 45.3 51.3 60.4 0.50
0.55 0.5 1.0 1.7 2.6 3.8 5.1 6.6 8.3 10.0 11.9 13.8 14.6 15.4 16.1 16.9 17.7 18.5 19.3 20.1 20.9 21.7 22.4 23.2 24.0 24.8 25.5 26.3 27.0 27.8 28.5 29.2 31.0 32.8 34.5 36.1 37.7 39.2 46.1 51.9 60.7 0.55
0.60 0.9 1.6 2.5 3.7 5.1 6.6 8.3 10.1 11.9 13.8 15.8 16.6 17.4 18.1 18.9 19.7 20.5 21.3 22.0 22.8 23.6 24.3 25.1 25.8 26.6 27.3 28.1 28.8 29.5 30.2 30.9 32.6 34.3 35.9 37.5 39.0 40.4 47.0 52.5 61.0 0.60
0.65 1.4 2.4 3.6 4.9 6.5 8.2 10.0 11.9 13.8 15.8 17.8 18.6 19.3 20.1 20.9 21.7 22.5 23.2 24.0 24.7 25.5 26.2 27.0 27.7 28.4 29.1 29.8 30.5 31.2 31.9 32.6 34.2 35.8 37.4 38.9 40.3 41.7 48.0 53.3 61.4 0.65
0.70 2.1 3.3 4.7 6.3 8.1 9.9 11.9 13.8 15.8 17.8 19.8 20.6 21.3 22.1 22.9 23.6 24.4 25.2 25.9 26.6 27.4 28.1 28.8 29.5 30.2 30.9 31.6 32.3 32.9 33.6 34.2 35.8 37.3 38.8 40.3 41.6 43.0 49.0 54.0 61.9 0.70
0.75 3.0 4.4 6.1 7.9 9.8 11.7 13.7 15.8 17.8 19.8 21.8 22.5 23.3 24.1 24.8 25.6 26.3 27.1 27.8 28.5 29.2 29.9 30.6 31.3 32.0 32.7 33.3 34.0 34.6 35.3 35.9 37.4 38.9 40.3 41.7 43.0 44.3 50.0 54.9 62.4 0.75
0.80 4.0 5.7 7.5 9.5 11.5 13.6 15.7 17.7 19.8 21.8 23.7 24.5 25.3 26.0 26.8 27.5 28.3 29.0 29.7 30.4 31.1 31.8 32.4 33.1 33.8 34.4 35.1 35.7 36.3 36.9 37.5 39.0 40.4 41.8 43.1 44.4 45.6 51.1 55.8 63.0 0.80
0.85 5.1 7.1 9.1 11.2 13.3 15.5 17.6 19.7 21.8 23.8 25.7 26.5 27.2 28.0 28.7 29.4 30.2 30.9 31.6 32.2 32.9 33.6 34.2 34.9 35.5 36.2 36.8 37.4 38.0 38.6 39.2 40.6 41.9 43.3 44.5 45.8 46.9 52.2 56.7 63.6 0.85
0.90 6.4 8.5 10.7 13.0 15.2 17.4 19.6 21.7 23.8 25.8 27.7 28.4 29.2 29.9 30.6 31.3 32.0 32.7 33.4 34.1 34.7 35.4 36.0 36.6 37.3 37.9 38.5 39.1 39.6 40.2 40.8 42.1 43.5 44.7 46.0 47.1 48.3 53.3 57.6 64.3 0.90
0.95 7.8 10.1 12.5 14.8 17.1 19.4 21.6 23.7 25.7 27.7 29.6 30.4 31.1 31.8 32.5 33.2 33.9 34.6 35.2 35.9 36.5 37.2 37.8 38.4 39.0 39.6 40.1 40.7 41.3 41.8 42.4 43.7 45.0 46.2 47.4 48.5 49.6 54.5 58.6 65.0 0.95
1.00 9.3 11.8 14.3 16.7 19.1 21.4 23.6 25.7 27.7 29.7 31.6 32.3 33.0 33.7 34.4 35.1 35.7 36.4 37.0 37.7 38.3 38.9 39.5 40.1 40.7 41.2 41.8 42.4 42.9 43.4 44.0 45.2 46.5 47.6 48.8 49.9 50.9 55.6 59.5 65.7 1.00
1.05 10.9 13.6 16.1 18.6 21.0 23.3 25.6 27.7 29.7 31.6 33.5 34.2 34.9 35.6 36.2 36.9 37.6 38.2 38.8 39.4 40.0 40.6 41.2 41.8 42.4 42.9 43.5 44.0 44.5 45.0 45.5 46.8 48.0 49.1 50.2 51.2 52.2 56.7 60.5 66.5 1.05
1.10 12.6 15.4 18.0 20.6 23.0 25.3 27.5 29.6 31.6 33.5 35.4 36.1 36.7 37.4 38.1 38.7 39.3 40.0 40.6 41.2 41.8 42.3 42.9 43.5 44.0 44.5 45.1 45.6 46.1 46.6 47.1 48.3 49.4 50.5 51.6 52.6 53.5 57.9 61.5 67.2 1.10
1.15 14.4 17.2 20.0 22.5 25.0 27.3 29.5 31.6 33.6 35.4 37.2 37.9 38.6 39.2 39.9 40.5 41.1 41.7 42.3 42.9 43.5 44.0 44.6 45.1 45.6 46.2 46.7 47.2 47.7 48.2 48.6 49.8 50.9 51.9 52.9 53.9 54.9 59.0 62.5 68.0 1.15
1.20 16.2 19.1 21.9 24.5 27.0 29.3 31.5 33.6 35.5 37.3 39.1 39.7 40.4 41.0 41.7 42.3 42.9 43.5 44.0 44.6 45.1 45.7 46.2 46.7 47.3 47.8 48.3 48.7 49.2 49.7 50.1 51.3 52.3 53.3 54.3 55.2 56.1 60.2 63.5 68.8 1.20
1.25 18.1 21.1 23.9 26.5 29.0 31.3 33.5 35.5 37.4 39.2 40.9 41.5 42.2 42.8 43.4 44.0 44.6 45.2 45.7 46.3 46.8 47.3 47.8 48.4 48.8 49.3 49.8 50.3 50.7 51.2 51.6 52.7 53.7 54.7 55.7 56.6 57.4 61.3 64.5 69.6 1.25
1.30 20.0 23.0 25.9 28.5 31.0 33.3 35.4 37.4 39.3 41.0 42.7 43.3 43.9 44.5 45.1 45.7 46.3 46.8 47.4 47.9 48.4 48.9 49.4 49.9 50.4 50.9 51.3 51.8 52.2 52.7 53.1 54.1 55.1 56.1 57.0 57.9 58.7 62.4 65.5 70.4 1.30
1.35 21.9 25.0 27.9 30.5 33.0 35.2 37.3 39.3 41.1 42.8 44.4 45.1 45.7 46.3 46.8 47.4 47.9 48.5 49.0 49.5 50.0 50.5 51.0 51.5 52.0 52.4 52.9 53.3 53.7 54.1 54.6 55.6 56.5 57.4 58.3 59.1 59.9 63.5 66.5 71.1 1.35
1.40 23.9 27.0 29.9 32.5 34.9 37.1 39.2 41.1 42.9 44.6 46.2 46.8 47.4 47.9 48.5 49.0 49.6 50.1 50.6 51.1 51.6 52.1 52.6 53.0 53.5 53.9 54.3 54.8 55.2 55.6 56.0 56.9 57.9 58.7 59.6 60.4 61.2 64.6 67.5 71.9 1.40
1.45 25.8 29.0 31.9 34.5 36.9 39.1 41.1 43.0 44.7 46.4 47.9 48.5 49.0 49.6 50.1 50.7 51.2 51.7 52.2 52.7 53.2 53.6 54.1 54.5 55.0 55.4 55.8 56.2 56.6 57.0 57.4 58.3 59.2 60.0 60.9 61.6 62.4 65.7 68.4 72.7 1.45
1.50 27.8 31.0 33.8 36.4 38.8 40.9 42.9 44.8 46.5 48.1 49.6 50.1 50.7 51.2 51.8 52.3 52.8 53.3 53.7 54.2 54.7 55.1 55.6 56.0 56.4 56.8 57.2 57.6 58.0 58.4 58.8 59.7 60.5 61.3 62.1 62.9 63.6 66.8 69.4 73.5 1.50
1.55 29.8 33.0 35.8 38.4 40.7 42.8 44.8 46.6 48.2 49.8 51.2 51.8 52.3 52.8 53.3 53.8 54.3 54.8 55.3 55.7 56.2 56.6 57.0 57.4 57.8 58.2 58.6 59.0 59.4 59.7 60.1 61.0 61.8 62.6 63.3 64.1 64.7 67.8 70.3 74.3 1.55
1.60 31.8 35.0 37.8 40.3 42.6 44.6 46.5 48.3 49.9 51.4 52.8 53.4 53.9 54.4 54.9 55.4 55.9 56.3 56.8 57.2 57.6 58.0 58.5 58.9 59.2 59.6 60.0 60.4 60.7 61.1 61.4 62.3 63.1 63.8 64.5 65.2 65.9 68.8 71.3 75.1 1.60
1.65 33.8 36.9 39.7 42.2 44.4 46.4 48.3 50.0 51.6 53.1 54.4 54.9 55.4 55.9 56.4 56.9 57.3 57.8 58.2 58.6 59.1 59.5 59.9 60.2 60.6 61.0 61.4 61.7 62.1 62.4 62.7 63.5 64.3 65.0 65.7 66.4 67.0 69.9 72.2 75.9 1.65
1.70 35.8 38.9 41.6 44.0 46.2 48.2 50.0 51.7 53.2 54.7 56.0 56.5 57.0 57.5 57.9 58.4 58.8 59.2 59.7 60.1 60.5 60.9 61.2 61.6 62.0 62.3 62.7 63.0 63.4 63.7 64.0 64.8 65.5 66.2 66.9 67.5 68.2 70.9 73.1 76.6 1.70
1.75 37.7 40.8 43.5 45.9 48.0 50.0 51.7 53.4 54.8 56.2 57.5 58.0 58.5 58.9 59.4 59.8 60.2 60.7 61.1 61.5 61.8 62.2 62.6 62.9 63.3 63.6 64.0 64.3 64.6 64.9 65.3 66.0 66.7 67.4 68.0 68.7 69.2 71.9 74.0 77.4 1.75
2.00 47.3 50.1 52.5 54.6 56.5 58.2 59.7 61.1 62.4 63.6 64.6 65.0 65.4 65.8 66.2 66.6 66.9 67.3 67.6 67.9 68.3 68.6 68.9 69.2 69.5 69.8 70.0 70.3 70.6 70.8 71.1 71.7 72.3 72.9 73.4 73.9 74.4 76.5 78.3 81.0 2.00
2.25 56.1 58.6 60.7 62.5 64.1 65.6 66.8 68.0 69.1 70.0 70.9 71.3 71.6 71.9 72.2 72.5 72.8 73.1 73.4 73.7 73.9 74.2 74.4 74.7 74.9 75.2 75.4 75.6 75.8 76.0 76.3 76.8 77.2 77.7 78.1 78.5 78.9 80.6 82.1 84.3 2.25
2.50 64.0 66.1 67.9 69.4 70.8 72.0 73.1 74.0 74.9 75.7 76.4 76.7 77.0 77.2 77.5 77.7 78.0 78.2 78.4 78.7 78.9 79.1 79.3 79.5 79.7 79.9 80.0 80.2 80.4 80.6 80.7 81.1 81.5 81.9 82.2 82.6 82.9 84.3 85.4 87.2 2.50
2.75 70.9 72.7 74.2 75.4 76.6 77.5 78.4 79.2 79.9 80.5 81.1 81.4 81.6 81.8 82.0 82.2 82.4 82.6 82.7 82.9 83.1 83.3 83.4 83.6 83.7 83.9 84.0 84.2 84.3 84.4 84.6 84.9 85.2 85.5 85.8 86.0 86.3 87.4 88.3 89.7 2.75
3.00 76.9 78.3 79.5 80.5 81.4 82.2 82.9 83.5 84.1 84.6 85.1 85.3 85.4 85.6 85.8 85.9 86.1 86.2 86.4 86.5 86.6 86.8 86.9 87.0 87.1 87.3 87.4 87.5 87.6 87.7 87.8 88.1 88.3 88.5 88.8 89.0 89.2 90.0 90.7 91.8 3.00
3.50 86.0 86.9 87.6 88.3 88.8 89.3 89.7 90.1 90.5 90.8 91.1 91.2 91.3 91.4 91.5 91.6 91.6 91.7 91.8 91.9 92.0 92.1 92.1 92.2 92.3 92.4 92.4 92.5 92.6 92.6 92.7 92.8 93.0 93.1 93.3 93.4 93.5 94.0 94.4 95.1 3.50
4.00 92.0 92.5 92.9 93.3 93.6 93.9 94.2 94.4 94.6 94.8 94.9 95.0 95.0 95.1 95.2 95.2 95.3 95.3 95.4 95.4 95.4 95.5 95.5 95.6 95.6 95.7 95.7 95.7 95.8 95.8 95.8 95.9 96.0 96.1 96.2 96.2 96.3 96.6 96.8 97.2 4.00
4.50 95.7 96.0 96.2 96.4 96.6 96.7 96.9 97.0 97.1 97.2 97.3 97.3 97.3 97.4 97.4 97.4 97.5 97.5 97.5 97.5 97.6 97.6 97.6 97.6 97.6 97.7 97.7 97.7 97.7 97.8 97.8 97.8 97.9 97.9 97.9 98.0 98.0 98.2 98.3 98.5 4.50
5.00 97.8 97.9 98.1 98.2 98.3 98.3 98.4 98.5 98.5 98.6 98.6 98.6 98.6 98.7 98.7 98.7 98.7 98.7 98.7 98.7 98.8 98.8 98.8 98.8 98.8 98.8 98.8 98.8 98.8 98.9 98.9 98.9 98.9 98.9 99.0 99.0 99.0 99.1 99.1 99.2 5.00
6.00 99.5 99.5 99.6 99.6 99.6 99.6 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.7 99.8 99.8 99.8 99.8 99.8 99.8 99.8 99.8 99.8 99.8 99.8 6.00
Note: Values in the table represent percentages of underlying asset values: e.g., 39.3 denotes a call option worth 39.3% of the underlying asset value.
Values in the table were computed from the Black-Scholes option pricing model.
The format of the table was adapted from Brealey and Myers, Principles of Corporate Finance, 4th edition, McGraw-Hill, Inc., New York, 1991, Appendix Table 6, pp. AP12-13.
&C
&C
Cumulative Volatility s√t