VIEWS: 13 PAGES: 6 CATEGORY: Business POSTED ON: 11/12/2010 Public Domain
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