# Financial Management Concepts and Cases by lmd11395

VIEWS: 13 PAGES: 6

• pg 1
```									         A             B               C                D              E             F             G           H            I         J
1   CH18MODEL                       UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT
2
3                                                  Chapter 18 -- Financial Risk Management
4
5   This spreadsheet model performs some of the calculations contained in Chapter 18. We recommend that you use the model in the
6   following manner:
7
8   1. First, recognize that you do not have to use this model at all to understand financial risk management concepts. However,
9   if you do use the model and experiment with it, this will increase your understanding of the concepts, and it will surely help you
10   when you use spreadsheet models for other purposes, especially any cases assigned for this course.
11
12   2. Assuming you decide to use this model, start by reading the chapter and working the examples as you come to them with a
13   financial calculator.
14
15   3. Now place the text along side your computer with this model on the screen. When you come to an explanation of a calculation
16   in the text, see if the model has a matching calculation.
17
18   4. We assume that you know the basics of Excel, but that you have not encountered some of its features or that you may need a
19   refresher or two. So, we have built in explanations of how to do some of the functions in the model. As a result, you will learn
20   more about Excel at the same time you learn about the time value analysis.
21
22   5. Throughout this model, page numbers of the matching text calculations are provided in pink. Input data are in red on a
23   yellow background and output data are in green on a beige background. You are encouraged to change the input data to learn
24   more about the calculations in the model.
25
26
27
28 DEBT PORTFOLIO IMMUNIZATION (PAGE 615)
29
30 One method to immunize (protect against interest rate risk) a bond portfolio is to match the duration of the portfolio to
31 the investment horizon (holding period). This section calculates the duration, which is a type of weighted average maturity
32 that includes coupon payments as well as the principal repayment, of a bond.
33
34                 Principal amount                     \$10,000    Change some of the input values to see the effects on duration. Note
35                 Maturity (years)                           10   that higher coupons payments shorten duration while longer
36                 Annual coupon (\$)                        \$600   maturities lengthen duration.
37                 Required return                         6.0%
38
39           Current value =    \$10,000.00
40                 Duration =                  7.8 years
41
42         Year     Coupon          Principal       Total CF           PV        PV/Value t x PV/Value This section does most
43             1 \$ 600.00       \$          -        \$   600.00     \$    566.04     0.05660      0.05660    of the calculation.
44             2      600.00                    0       600.00          534.00     0.05340      0.10680
45             3      600.00                    0       600.00          503.77     0.05038      0.15113
46             4      600.00                    0       600.00          475.26     0.04753      0.19010
47             5      600.00                    0       600.00          448.35     0.04484      0.22418
48             6      600.00                    0       600.00          422.98     0.04230      0.25379
49             7      600.00                    0       600.00          399.03     0.03990      0.27932
50             8      600.00                    0       600.00          376.45     0.03764      0.30116
A           B            C            D              E              F           G           H            I          J
51           9     600.00              0      600.00         355.14      0.03551      0.31963
52          10     600.00         10,000   10,600.00        5,918.98     0.59190      5.91898
53          11       0.00              0        0.00            0.00     0.00000      0.00000
54          12       0.00              0        0.00            0.00     0.00000      0.00000
55          13       0.00              0        0.00            0.00     0.00000      0.00000
56          14       0.00              0        0.00            0.00     0.00000      0.00000
57          15       0.00              0        0.00            0.00     0.00000      0.00000
58          16       0.00              0        0.00            0.00     0.00000      0.00000
59          17       0.00              0        0.00            0.00     0.00000      0.00000
60          18       0.00              0        0.00            0.00     0.00000      0.00000
61          19       0.00              0        0.00            0.00     0.00000      0.00000
62          20       0.00              0        0.00            0.00     0.00000      0.00000
63          21       0.00              0        0.00            0.00     0.00000      0.00000
64          22       0.00              0        0.00            0.00     0.00000      0.00000
65          23       0.00              0        0.00            0.00     0.00000      0.00000
66          24       0.00              0        0.00            0.00     0.00000      0.00000
67          25       0.00              0        0.00            0.00     0.00000      0.00000
68          26       0.00              0        0.00            0.00     0.00000      0.00000
69          27       0.00              0        0.00            0.00     0.00000      0.00000
70          28       0.00              0        0.00            0.00     0.00000      0.00000
71          29       0.00              0        0.00            0.00     0.00000      0.00000
72          30       0.00              0        0.00            0.00     0.00000      0.00000
73
74
75 FACTORS THAT AFFECT THE VALUE OF A CALL OPTION (PAGE 623)
76
77 Consider the case of West Coast Genetics, Inc. (WCG) whose common stock is currently trading at \$21. Since it was issued,
78 its stock price has fluctuated wildly. Here some historical option values for WCG's call option having a strike price of \$20.
A            B             C              D                E         F                      G                H              I          J
79
80                                    Option Value
Market Value versus Exercise Value
81      Price of     Strike       Exercise     Market
82     the stock     Price         Value        Value           Premium
\$80.00
83        \$0        \$20.00         \$0.00           \$4.50         \$4.50
84       \$10        \$20.00         \$0.00           \$6.00         \$6.00
85      \$20.00      \$20.00         \$0.00           \$9.00         \$9.00                             \$60.00

Option Value
86      \$21.00      \$20.00         \$1.00           \$9.75         \$8.75
87      \$22.00      \$20.00         \$2.00         \$10.50          \$8.50                             \$40.00
88      \$35.00      \$20.00        \$15.00         \$21.00          \$6.00
Market Value
89      \$42.00      \$20.00        \$22.00         \$26.00          \$4.00                             \$20.00
90      \$50.00      \$20.00        \$30.00         \$32.00          \$2.00
91      \$73.00      \$20.00        \$53.00         \$54.00          \$1.00                                                      Exercise Value
\$0.00
92      \$98.00      \$20.00        \$78.00         \$78.50          \$0.50                                      \$0       \$20        \$40        \$60   \$80   \$100
93                                                                                                                              Stock Price
94    The exercise value is defined as the difference between the
95    current stock price and the strike (exercise) price, except that
96    it cannot be negative. The premium is the difference between
97    between the market value of the option and its exercise value. There are three factors that affect the size
98    of the premium. First, the longer the term to maturity, the higher the premium. Second, the greater the
99    variability of the stock price, the higher the premium. Finally, the higher the risk-free rate, the higher
100   the premium. However, the magnitudes of these effects was very difficult to estimate prior to the 1973
101   development of the Black-Scholes Option Pricing Model.
102
103   BLACK-SCHOLES OPTION PRICING MODEL (OPM) (NO MATCHING TEXT SECTION)
104
105   The derivation of the Black-Scholes model rests on the concept of a riskless hedge. By buying shares of a
106   stock and simultaneously selling call options on that stock, an investor can create a risk-free investment
107   position, where gains on the stock are exactly offset by losses on the option. Because a riskless investment
108   must return the risk-free rate, a model for call option valuation can be developed. The Black-Scholes
109   consists of these three equations:
110
111             Equation 1: V = P[ N (d1) ] - Xe-RF( t) [ N (d2) ].
112
113             Equation 2: d1 = { ln (P/X) + [krf + s2 /2) ] t } / 2.
114
115             Equation 3: d2 = d1 - s (t 1 / 2).
116
117   In these equations, V is the value of the option. P is the current price of the stock. N(d1) is the area beneath
118   the standard normal distribution corresponding to (d1). X is the strike price. RF is the risk-free rate. t is the
119   time to maturity. N(d2) is the area beneath the standard normal distribution corresponding to (d2). S, or
120   usually sigma, is the volatility of the stock price, as measured by the standard deviation.
121
122   Looking at these equations we see that you must first solve d1 and d2 before you can proceed to value the option.
123
124   This model is widely used by options traders and is generally considered to be the standard for option
125   pricing. Many hand-held calculators and financial softwares have this formula preprogrammed in.
126   We now use Excel to write a "program", if you will, for the Black-Scholes model. We will construct this
127   "program" to price the call option on a stock that has a current market price of \$20, a strike price of
128   \$20, a time to maturity of 3 months (0.25 years). The risk-free rate is assumed to be 12% and the annual
129   variance of the stock price is 0.16.
130
131                P                \$20        = Stock price.                       V=                  \$1.883       This result has been copied from below.
132                X                \$20        = Strike price.
133                RF               12%        = Risk-free rate.           Try changing the input values to see the effect on option value.
A           B            C             D             E               F            G             H             I   J
134               t                0.25      = Time to maturity.
135               s2               0.16      = Stock price variance.
136
137   First, we will use Equation 2 to solve for d1.
138
139                        d1 =    0.250
140
141   Having solved for d1, we will now use this value along with Equation 3 to find d2.
142
143                        d2 =    0.050
144
145   At this point, we have all of the necessary inputs for solving for the value of the call option. We will use the
146   formula for V from above to find the value. The only complication arises when entering N(d1) and N(d2).
147   Remember, these are the areas under the normal distribution. Fortunately, Excel has a function that
148   that can determine cumulative probabilities of the normal distribution. This function is located
149   in the list of statistical functions, as "NORMDIST". For both N(d1) and N(d2), we will follow the same
150   procedure of using this function in the value formula. The data entries for N(d1) are shown below. (Nd2)
151   would be identical except that Cell C143 would be entered for "X" rather than "C139".
A            B            C             D              E             F            G            H             I         J
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167   By applying this method to get the cumulative distribution values, we can solve for option value using Equation 1.
168
169                        V=    \$1.883
170
171   We see that although the value if executed today (the exercise value) is \$0, the actual market value of the option is \$1.883.
172
173   EFFECTS OF OPM FACTORS ON THE VALUE OF A CALL OPTION (NO MATCHING TEXT SECTION)
174
175   Out of curiosity, let us now turn our attention to determining how sensitive call option value is to the
176   five factors of the Black-Scholes model. We will set up data tables for each factor determining the call value
177   if the specified input is changed plus or minus 15% and 30%.
178
179   % change         P         \$1.883                                   % change          X         \$1.883
180    -30%           \$14        0.0705                                    -30%            \$14        6.4470
181    -15%           \$17        0.5512                                    -15%            \$17        3.8263
182     0%            \$20        1.8827                                      0%            \$20        1.8827
183    15%            \$23        4.0554                                     15%            \$23        0.7719
184    30%            \$26        6.7341                                     30%            \$26        0.2715
185
186   % change         t         \$1.883                                   % change        RF          \$1.883
187    -30%          0.175       1.5379                                    -30%          8.4%         1.7930
188    -15%          0.213       1.7162                                    -15%          10.2%        1.8376
189     0%           0.250       1.8827                                      0%          12.0%        1.8827
190    15%           0.288       2.0397                                     15%          13.8%        1.9284
191    30%           0.325       2.1892                                     30%          15.6%        1.9747
192
193   % change         s2        \$1.883
194    -30%          0.112       1.6304
195    -15%          0.136       1.7620
196     0%           0.160       1.8827
197    15%           0.184       1.9947
198    30%           0.208       2.0996
A            B             C           D             E              F           G           H            I   J
199
200
201                      OPM Factors Effect on Option Value
202                                          \$7
203
204                                          \$6
Stock Price
Option Value

205                      Strike Price
\$5
206
207                                          \$4
208                                          \$3
209                                                              Time to Maturity
210               Risk-Free Rate             \$2
211                                          \$1
212
\$0
213
214            -30% -20% -10%                   0%        10%       20%        30%
215
% Change
216
217
218
219 From this graph, we see that the strongest influences on option value are the stock and the exercise prices.
220 Meanwhile, time to maturity, the risk-free rate, and variance have only marginally positive correlations with
221 the value of the option
222

```
To top