EVALUACIÓN Y VALIDACIÓN
OCTAVA PRÁCTICA Centro de estudios Universidad del Azuay
Derivative instruments are securities whose returns are derived – hence the name ‘derivative’ – from those of underlying securities. The primary aim of derivatives, which include options and futures, is to minimise financial risk while maximising expected return. An option on a security is a contractual agreement between two parties that gives one party the right (but not the obligation) to buy or sell an underlying asset from the other party at a price agreed at a future date or within a specified period of time. An option to buy is known as a ‘call’ while an option to sell an asset is called a ‘put’.
The term ‘to exercise’ refers to the process by which an option is used to purchase (or sell) the underlying security. Whether or not a holder exercises an option depends on the price of the specified stock at the option’s expiration. If the stock value is above the original agreed price, then the holder will exercise the call, otherwise the call will not be exercised. Because the right to exercise an option is contingent on how the underlying assets perform, options are often called contingent claims. Options are traded in organised markets similar to the stock market.
In 1973, Fischer Black and Myron Scholes published a paper, in which they presented a formula for the valuation of options. This formula – known as the Black–Scholes (B–S) option pricing model – had a major influence on modern finance and lead to the development of many other derivative instruments and hedging strategies. The basic premise of the B–S pricing model is that an option can be priced by forming a riskless portfolio consisting of shares and call options. The value of the call option is then equal to the expected value of the investments in the riskless portfolio minus the cost of this investment (Diacogiannis, Chapter 19).
The Black–Scholes pricing model requires five inputs as shown in Figure 4.16, namely the current stock price (S), the exercise price (E), the riskless (risk-free) interest rate (r), the time remaining before the option’s expiration date (T), and the standard deviation (σ) of the underlying stock price, usually known as its volatility. The B–S option pricing model is based on the following five assumptions:
1. The capital market is perfect, i.e., (i) there are no transaction or tax costs (ii) assets are infinitely divisible, i.e., there is no restriction on the size of the amount to be invested (iii) the same information is freely available to every investor (iv) no single investor can influence the market by buying or selling actions.
2. A risk-free interest rate exists and is constant over the lifetime of the option; investors can borrow or lend any amount at this fixed risk-free interest rate.
3. The underlying security does not pay dividends.
4. The underlying security’s rate of return follows a normal distribution, and it has a constant and known variance over the lifetime of the option.
5. The option is European. The difference between European and American options is that a European option can only be exercised at maturity, i.e., on the expiration date, whereas an American option can be exercised on any day during its life.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 A B C D E F G H
Example 4.12 - The Black-Scholes option pricing model Current stock price, S = £40.00 User input cells
Exercise price, E = £40.00 are shaded Risk-free interest rate, r = 7.00%
Time to expiration (in years), T = 0.40 Stock volatility, σ = 30.0%
d1 = 0.2424 d2 = 0.0527
N(d1) = 0.5958 N(d2) = 0.5210
N(−d1) = 0.4042 N(−d2) = 0.4790
Call price = £3.57 Put price = £2.46
Cell Formula Copied to
C9 (LN(D3/D4) + (D5 + D7*D7/2)*D6)/(D7*SQRT(D6)) D9 C9 − D7*SQRT(D6) C10 NORMSDIST(C9) F10 C11 NORMSDIST(−C9) F11 C13 F13 D3*C10 − D4*EXP(−D5*D6)*F10 23 −D3*C11 + D4*EXP(−D5*D6)*F11
Figure 4.16 The Black–Scholes option pricing model.
EXAMPLE 4.12 Using the Black–Scholes model for pricing options
The Black–Scholes option pricing model for a call option is given by the following formula: C= SN(d1)− Ee−rTN (d2) where d1= ln(S/E)+ (r + σ2/2)T σ√T d2= d1− σ √ T and C= the price of a call option
S= the current market price of the underlying stock
N (di)= the value of the cumulative normal distribution evaluated at di (i = 1,2)
E = the exercise price of the option
e= the base value in natural logarithms, i.e., 2.7183 r= the risk-free interest rate
T = the time remaining before the expiry date expressed as a fraction of a year ln= the natural logarithm operator (i.e., loge)
σ = the standard deviation of the continuously compounded annual rate of return, i.e., the stock’s volatility
Table 4.6 Sensitivity analysis using the Black–Scholes model.
Stock volatility Call price Put price Stock price Call price Put price
20% 2.59 1.49 £30 0.25 9.15
25% 3.08 1.97 £35 1.26 5.16
30% 3.57 2.46 £40 3.57 2.46
35% 4.06 2.95 £45 7.11 1.01
40% 4.55 3.44 £50 11.47 0.36
The B–S model can also be used to calculate the price of a put option. The formula for the value of a put option, P, is :
P= −SN(−d1)+ Xe−rTN (−d2)
The Black–Scholes model in Figure 4.16 can be used to carry out sensitivity analysis by varying each of the five inputs (see cells D3:D7). Table 4.6 contains option prices that were obtained by varying two of the inputs, namely stock volatility and price. It is clear that (i) as stock volatility increases, both call and put prices also increase, and (ii) as the stock price increases, the call price increases while the put price falls. The user should check what happens to option prices when the other three inputs are changed.
EXAMPLE 4.13 Estimating the implied volatility of a stock
Volatility is a key feature in options markets – the greater the volatility of the underlying stock, the greater the potential to increase profits. While volatility also implies that stock prices can fall as well as rise, any loss to the call-holder is restricted to the exercise price. In fact, the holder does not care how low the value of the stock falls below the exercise price. Because high stock volatility is more attractive to buyers, estimating a stock’s volatility (i.e., its standard deviation,σ) is important.
There are two methods for estimating stock volatility: historical volatility and implied volatility. Historical volatility is defined as the standard deviation of a security that is ob- tained by estimation from historical data taken over a recent period of time. Implied volatil- ity is found by calculating the standard deviation that – when used in the Black–Scholes model – makes the model’s calculated price equal to today’s actual price. Thus, the im- plied volatility is the volatility of the stock as implied by today’s market price of the option.
In Figure 4.17, which is a modified version of the Black–Scholes model, Excel’s Solver tool is used to find the implied volatility. Since there is no objective to either maximise or minimise, the volatility model does not have a target cell. For a call price of £3.08, volatil- ity has been calculated correctly as 25.03% (cell D8) – see the Stock Volatility section in Table 4.6.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 A B C D E F G H
Example 4.13 - Estimating implied volatility using the Black-Scholes model Current stock price, S = £40.00 User input cells
Exercise price, X = £40.00 are shaded Risk-free interest rate, r = 7.00%
Time to expiration (in years), T = 0.40
Stock's implied volatility, σ = 25.03% Enter any value into cell D8 Actual Estimated
Call option's price = 3.08 3.08
d1 = 0.2560 d2 = 0.0977
N(d1) = 0.6010 N(d2) = 0.5389
Solver Parameters
Set Target Cell: Leave Blank
Equal to: Value of
By Changing Cells: D8
Subject to Constraints: D11 = E11 = Actual call price = Estimated call price
D8 >=0 = Answer must be positive
Cell Formula Copied to
E11 D3*C14 − D4*EXP(−D6*D5)*F14 C13 (LN(D3/D4) + (D5 + D8*D8/2)*D6)/(D8*SQRT(D6)) F13 C13 − D8*SQRT(D6) C14 NORMSDIST(C13) F14 28 29
Figure 4.17 Estimating implied volatility.
EXCEL FUNCTIONS USED IN MODEL-BUILDING
The models developed in this chapter introduce ten Excel functions for the first time, each of which is explained below. If any of the functions is not available, and returns the #NAME? error, then install and load the Analysis ToolPak add-in.
1. DURATION: DURATION (settlement, maturity, coupon, yield, frequency, basis)
returns a security’s annual duration. Duration is defined as the time-weighted average of the present value of the cash flows. Duration is used as a measure of the sensitivity of a security’s price to changes in the yield to maturity.
settlement= the security’s date of purchase, expressed as a serial date number. maturity = the security’s date of maturity, expressed as a serial date number. coupon = the security’s annual coupon rate.
yield = the security’s annual yield to maturity.
frequency= the number of coupon payments per year, i.e., for annual payments, frequency= 1; for semi-annual payments, frequency = 2; and for quarterly payments, frequency= 4.
basis= the type of day count basis to use. If basis = 0 or omitted, US (NASD) 30/360; basis= 1, Actual/actual; basis = 2, Actual/360; basis = 3, Actual/365; basis= 4, European/360.
Example: A five-year bond, which was purchased today, has a coupon rate of 8% and a yield of 9%. Find the bond’s duration, given that payments are semi-annual. If cells B3 and B4 contain the settlement and maturity dates, then the answer is DURATION(B3, B4, 8%, 9%, 2, 4)= 4.198878 where cells B3, B4 contain the formulae TODAY() and B3 + 365*5 + 5/4 respectively.
2. INTERCEPT: INTERCEPT (y-values, x-values)
calculates the point at which a line will intersect the y-axis by using known x-values and y-values. The intercept point is based on a best-fit regression line plotted through the known x-values and y-values.
y-values= an array of known y-values. x-values= an array of known x-values.
Example: Cell ranges (X1:X5) contain (5, 8, 10, 4, 2) and (Y1:Y5) contains (9, 3, 7, 6, 4). The formula INTERCEPT(Y1:Y5, X1:X5) returns an intercept value of 5.401961.
3. MDURATION: MDURATION (settlement, maturity, coupon, yield, frequency, basis) returns the modified Macaulay duration of a security. The modified Macaulay duration is defined as
MDuration= Duration 1+ (yield/frequency)
settlement= the security’s date of purchase, expressed as a serial date number. maturity = the security’s date of maturity, expressed as a serial date number. coupon = the security’s annual coupon rate.
yield = the security’s annual yield to maturity.
frequency= the number of coupon payments per year, i.e., for annual payments, frequency= 1; for semi-annual payments, frequency = 2; and for quarterly payments, frequency= 4.
basis = the type of day count basis to use. If basis = 0 or omitted, US (NASD) 30/360; basis= 1, Actual/actual; basis = 2, Actual/360; basis = 3, Actual/365; basis= 4, European/360.
Example: An eight-year bond has a coupon rate of 8% and a yield of 9%. Find the bond’s modified Macaulay duration, given that payments are semi-annual. If cells B3 and B4 contain the settlement and maturity dates, then the answer is MDURATION(B3, B4, 8%, 9%, 2, 4)= 5.73567 where cells B3, B4 contain the formulae TODAY() and B3 + 365*8 + 8/4 respectively.
4. NORMSDIST: NORMSDIST(Z)
returns the standard normal cumulative distribution function. The function computes the area or probability less than a given Z value and is used in place of a table of areas under the standard normal curve
Z = the value for which the distribution is required
Example: Sales of an item are known to be normally distributed with a mean of 12 and a standard deviation of 5. What is the probability of selling 14 or more items? P(X≥ 14) = 1 − NORMSDIST([14 - 12]/5) = 1 − 0.6554 = 0.3446 = 34.46%.
5. PRICE: PRICE (settlement, maturity, coupon, yield, redemption, frequency, basis) returns the price per £100 face value of a security that pays periodic interest. settlement = the security’s date of purchase, expressed as a serial date number. maturity = the security’s date of maturity, expressed as a serial date number. coupon = the security’s annual coupon rate.
yield = the security’s annual yield to maturity.
redemption= the security’s redemption value per £100 face value.
frequency = the number of coupon payments per year, i.e., for annual payments, frequency= 1; for semi-annual payments, frequency = 2; and for quarterly payments, frequency= 4.
basis = the type of day count basis to use. If basis = 0 or omitted, US (NASD) 30/360; basis= 1, Actual/actual; basis = 2, Actual/360; basis = 3, Actual/365; basis= 4, European/360.
Example: A bond, which has a face value of £100, has eight years remaining to maturity. The annual coupon rate is 8% and the investor’s annual required rate of return on the bond is 10%. Assuming that interest is paid at the end of each year, what is the bond’s present value? If cells B3 and B4 contain the settlement and maturity dates, then the answer is PRICE(B3, B4, 8%, 10%, 100, 1, 4)= 89.33015 = £89.33 where cells B3, B4 contain the formulae TODAY() and B3+ 365*8 + 8/4 respectively.
6. SLOPE: SLOPE (y-values, x-values)
returns the slope of the linear regression line through data points in known y-values and x-values. The slope is the vertical distance divided by the horizontal distance between any two points on the line, which is the rate of change along the regression line. y-values= an array of known y-values.
x-values= an array of known x-values.
Example: Cell ranges (X1:X5) contain (5,8,10,4,2) and (Y1:Y5) contains (9,3,7,6,4). The formula SLOPE(Y1:Y5, X1:X5) returns the slope of the regression line as 0.068627. 7. STDEV: STDEV (array)
estimates the standard deviation based on a sample. array= cell range containing the sample values
Example: STDEV(C4:C7) returns the value 14.91 where cells C4, C5, C6, and C7 contain the values 10, 25, 8, and 40 respectively.
8. STEYX: STEYX (y-values, x-values)
returns the standard error of the predicted y-value for each x in the regression. The standard error is also known as the standard deviation of the estimation errors (i.e., residuals) in linear regression analysis.
y-values = an array of known y-values. x-values = an array of known x-values.
Example: Cell ranges (X1:X5) contain (5, 8, 10, 4, 2) and (Y1:Y5) contains (9, 3, 7, 6, 4). The formula STEYX(Y1:Y5, X1:X5) returns a standard error of 2.745168.
9. YIELD: YIELD (settlement, maturity, coupon, price, redemption, frequency, basis) returns the price per £100 face value of a security that pays periodic interest. settlement = the security’s date of purchase, expressed as a serial date number. maturity = the security’s date of maturity, expressed as a serial date number. coupon = the security’s annual coupon rate.
price = the security’s price per £100 face value.
redemption= the security’s redemption value per £100 face value.
frequency = the number of coupon payments per year, i.e., for annual payments, frequency= 1; for semi-annual payments, frequency = 2; and for quarterly payments, frequency= 4.
basis = the type of day count basis to use. If basis = 0 or omitted, US (NASD) 30/360; basis= 1, Actual/actual; basis = 2, Actual/360; basis = 3, Actual/365; basis= 4, European/360.
Example: A six-year bond has a coupon rate of 7% and a redemption value of £100. If the bond’s price is £95.30 and coupon payments are semi-annual, what is the bond’s yield to maturity? The answer is YIELD(B3, B4, 7%, 95.30, 100, 2, 4)= 0.080016 = 8% where cells B3, B4 contain the settlement and redemption dates, given by the formulae TODAY() and B3+ 365*6 + 6/4 respectively.
10. XIRR: XIRR (values, dates, guess)
returns the internal rate of return (yield-to-maturity) for a schedule of cash flows that is not necessarily periodic.
values= a series of cash flows that correspond to a schedule of payment dates. The first payment is optional. It corresponds to a cost or payment that occurs at the beginning of the investment and must be entered as a negative value. All succeeding payments are discounted and based on a 365-day year. The series of values must contain at least one positive and one negative value.
dates = a series of payment dates that corresponds to cash flow payments. All dates should be entered into cells which are formatted as dates.
guess= a number that you guess is close to the result of XIRR. If omitted, guess is assumed to be 0.1 (10 percent).
Example: Cell range (A1:A5) contains values (−500, 100, 120, 200, 200) and cells (B1:B5) contain dates (1-Jan-06, 1-Mar-06, 15-Feb-07, 30-Oct-07, 1-Jan-08). XIRR(A1:A5, B1:B5) returns a internal rate of return (yield) of 16.05%. Note that ‘guess’ has been omitted.
EXERCISES
4.1 Joe Bloggs, who is risk-averse, is a contestant on the ‘Open the Box’ quiz show. The worst and best outcomes on the show are £1,000 and £9,000. What is the lower bound for Joe’s utility U value of £4000? It is known that Joe is indifferent between an offer of £4000 from the quiz-master and the show offering probabilities of 0.3 and 0.7 for the worst and best outcomes respectively. What is the lower bound of Joe’s utility value for an offer of £5000 from the quiz-master? (Hint: utility U values for risk-averse are greater than those for risk-indifferent)
(Answer: U(4000)≥ 0.375; U(5000) ≥ 0.76 )
4.2 Fred Flint recently inherited £20,000 from a wealthy relative. His stockbroker has advised him to invest in three particular stocks. Because of the volatility of the market, Fred requests the stockbroker to provide him with historical data for these stocks over the past four years, as shown in Table 4.7.
Table 4.7
Year Stock A Stock B Stock C
1 20.2% 10.9% 15.3%
2 2.4% 18.7% 6.8%
3 15.9% 9.8% 18.5%
4 4.6% 12.1% 5.7%
Fred decides to build a simple security analysis model using three of Excel’s functions – SUMPROD- UCT, AVERAGE, and STDEV. He wants to perform ‘what-if’ analysis to see what happens when different amounts are invested in each stock. Being risk-averse, Fred knows that a lower standard deviation (σ-value), means a less risky stock. He thus wants the highest expected return that can be achieved without increasing the portfolio’s riskiness. Set up Fred’s ‘what-if’ model and then use it to find an acceptable solution.
(Answer: Investing 10%, 70% and 20% in stocks A, B, and C respectively gives Fred an expected return of 12.41% with aσ -value of 1.9%. Other combinations may give higher returns but also higher σ -values)
4.3 Fred Flint has been talking to his neighbour Barney Briggs about investment options. Barney explains that he has several investment analysis models, which Fred is welcome to borrow. Fred decides to install Barney’s portfolio analysis model (see Example 3.5) and use his own historical data given in Table 4.7. He wants to find out if Barney’s model produces an answer close to the solution
derived by his simple ‘what-if’ model. Use the model of Exercise 3.5 to verify (or otherwise) that the answer to Exercise 4.2 is acceptable.
(Answer: Barney’s model confirms that Fred’s ‘what-if’ model has found an optimal solution.)
4.4 Having successfully implemented Barney Briggs’s portfolio analysis model, Fred Flint is now feeling more adventurous. He asks Barney if it is possible to find a range of portfolios based on the two objectives of (i) maximising expected returns and (ii) minimising risk. Barney tells Fred that his ‘efficient frontier’ model will provide the required information. Modify the SIM model of Figure 4.8 in order to utilise Fred’s stock returns (see Table 4.7) as input. Next, use the results derived from this Markowitz model as input data to Figure 4.6. Since Fred would like a return of around 12% on his investment, run the model seven times in order to create the graph of the efficient frontier.
(Answer: Expected returns of 12.1%, 12.3% and 12.5% produceσ-values of 2.22, 2.24 and 2.44 respectively.)
4.5 Merlene Olonga, who is studying financial modelling, has been given an assignment by her tutor. She is required to calculate the covariance matrix using the single index model (SIM) and then check the accuracy of the SIM covariance matrix against the Markowitz matrix. Merlene’s input (see Table 4.8) contains returns for four companies over the past eight years, as well as the market returns for the same period.
Table 4.8
Year Company 1 Company 2 Company 3 Company 4 Market
1 7.6% 14.4% 20.3% 19.7% 17.2% 2 −3.4% 8.7% 33.6% 8.8% 9.1% 3 23.7% 28.1% 5.9% −3.1% 10.8% 4 11.3% 15.7% 4.6% 16.2% 9.5% 5 −7.8% 8.5% 9.4% 21.5% 8.9% 6 10.5% 1.7% −3.2% 29.7% 5.7% 7 25.9% 13.8% 28.5% −4.3% 13.3% 8 20.1% 9.2% 15.9% 12.6% 11.8% (Answer:
Covariance matrix (using SIM) Covariance matrix (using Markowitz)
0.01710 0.00116 0.00232 −0.00149 0.01484 0.00397 −0.00101 −0.00744 0.00116 0.00678 0.00206 −0.00132 0.00397 0.00596 −0.00003 −0.00560 0.00232 0.00206 0.01778 −0.00264 −0.00101 −0.00003 0.01582 −0.00646 −0.00149 −0.00132 −0.00264 0.01607 −0.00744 −0.00560 −0.00646 0.01402 4.6 Merlene Olonga’s next assignment requires her to modify the capital asset pricing model (CAPM) in order to find beta and expected return values for each of the four companies, using the data in Table 4.8 as input. The risk-free rate, Rf, is to be taken as 3.3% and the market risk premium, (E (RM)− Rf), as 6.3%.
(Answer: CAPM beta values and expected returns for companies 1–4 are (i) 0.9232, 0.8185, 1.6337, −1.0527 (ii) 9.12%, 8.46%, 13.59%, −3.33%.)
4.7 Bert Namagong has decided to invest £5,000 in some bonds, each having a face value of
amount that he should pay for each of the four bonds shown in Table 4.9. He will use Excel’s function, PRICE, and the simple formula for the price of a zero-coupon bond, to help in his bond valuation.
Table 4.9
Bond Maturity Coupon rate Interest payments
A 2 years 8.2% Semi-annual
B 5 years 8.5% Semi-annual
C 10 years 9.2% Annual
D 5 years 10.2% Zero coupon
(Answer: The prices of the bonds A, B, C, and D are £968.09, £942.09, £950.84, and £615.31 respec- tively.)
4.8 Using the formulae for bond duration and the price of a zero-coupon bond, Bz, show that the duration of bond, Bz, is equal to its maturity.
4.9 Bert Namagong has been told recently that a bond’s duration can be used as a measure of its volatility to changes in yield. The larger its duration, the more volatile is the bond. Bert would like to find the most volatile bond in Table 4.9. He will first use Excel’s DURATION function and the information given in Exercise 4.8 to find each bond’s duration. He will then use the volatility- duration equation, V = D/(1 + r ), to find bond volatility.
(Answer: The durations of bonds A, B, C, and D are 1.88, 4.15, 6.86, and 5.0 respectively – showing that bond C is the most volatile. The bonds’ volatilities (1.71, 3.77, 6.24, and 4.55) confirm this fact.) 4.10 Fiona Brown, who regularly invests in the options market, knows that a higher stock price usually leads to a higher call price and a lower put price. She would like to see how an increase in the exercise price, E, of a stock will effect call and put options. Fiona has already built a worksheet of the Black–Scholes option pricing model (see Figure 4.16) containing data of her favourite stock. She will use this model to perform some sensitivity analysis, varying the current exercise price of