Discrete Distribution: Expected Return and Variance Calculation
As you can see we have recreated the discrete distribution here in Excel.
The probabilities must sum to 1 or 100% and when we multiply the individual expected returns in each state by the associated probability we generate the contribution that state has to the overall expected return.
We then use the expected return to generate the variance of .00703 or a standard deviation of 8.38% (0.0838) associated with the 9.00% return.
Refer to ‘VW13E-05b.xlsx’ on tab ‘Discrete’. We can also graph as above. You may chance the probabilities and possible returns to view impact.

15 trang |

Chia sẻ: nguyenlinh90 | Lượt xem: 819 | Lượt tải: 0
Bạn đang xem nội dung tài liệu **Bài giảng Financial Management - Chapter 5 – Support: Risk and Return**, để tải tài liệu về máy bạn click vào nút DOWNLOAD ở trên

Chapter 5 – SupportRisk and ReturnRemember? Determining the Expected Return/Standard Deviation Stock BW Ri Pi (Ri)(Pi) –0.15 0.10 –0.015 –0.03 0.20 –0.006 0.09 0.40 0.036 0.21 0.20 0.042 0.33 0.10 0.033 Sum 1.00 0.090 The expected return, R, for Stock BW is 0.09 or 9%Discrete Distribution: Expected Return and Variance CalculationAs you can see we have recreated the discrete distribution here in Excel. The probabilities must sum to 1 or 100% and when we multiply the individual expected returns in each state by the associated probability we generate the contribution that state has to the overall expected return.We then use the expected return to generate the variance of .00703 or a standard deviation of 8.38% (0.0838) associated with the 9.00% return.Refer to ‘VW13E-05b.xlsx’ on tab ‘Discrete’. We can also graph as above. You may chance the probabilities and possible returns to view impact.Remember? Distribution DiscussionsSo how do we create graphs that represent something akin to a normal distribution that is continuous?In file ‘VW13E-05b.xlsx’ on tab ‘Standard Normal’ we can create our own distributions with our own defined means and standard deviations!Discrete Distribution: Expected Return and Variance CalculationWe can recreate our own distributions using Excel. While the process can be used for many different simulations and analysis techniques, we will focus on the creation of the distribution graph for an individual firm.Assume a stock has an expected mean return of 10% and a standard deviation of 30%. Together, we can create a standard normal distribution as above.Note that we f(x) creates the data for a normal distribution graph and F(x) creates the data for the cumulative probability (F(x): probability totals 100%)Refer to ‘VW13E-05b.xlsx’ on tab ‘Standard Normal’Discrete Distribution: Expected Return and Variance CalculationThe f(x) graph to the left is created from the data described earlier. Refer to ‘VW13E-05b.xlsx’ on tab ‘Standard Normal’Note that the x-axis indicates the range of returns for this stock with the height representing the likelihood of the return occurring.The F(x) graph to the left is created from the data described earlier. Also refer to ‘VW13E-05b.xlsx’ on tab ‘Standard Normal’Note that the x-axis indicates the probability of a return being that rate or lower. For example, the probability of earning a return that is 0% or less (negative) is 36.9%.Remember? Characteristic LineEXCESS RETURNON STOCKEXCESS RETURNON MARKET PORTFOLIOBeta =RiseRunNarrower spreadis higher correlationCharacteristic LineHow do you create a Characteristic Line in Excel?Step 1: Collect the data. We generated monthly data using Finance.Yahoo.com. We chose to use the S&P 500 to represent the “market” portfolio, GE to represent our individual stock asset, and the US Treasury 30-year bond yield to represent our risk-free asset.We started by downloading the prices on a monthly basis for GE and the S&P 500 and yields on the Treasury for the period September 2005 through March 2008.Refer to ‘VW13E-05b.xlsx’ on tab ‘Excess Returns’How do you create a Characteristic Line in Excel?Step 2: Calculation of monthly excess returns. We used the price data and calculated a monthly return. For example, for March 2008 GE’s return was $37.01/$33.14 - 1 = .11678 or 11.678%. We did this for both the S&P and GE.The yield on the 30-year is an annual yield, so we divided the annual rate by 12 to generate a monthly rate and adjusted it to the same format as the other returns (%). For March it is .00359.We then subtracted the monthly risk-free rate from the monthly return to generate 30 excess return data points for each GE and the S&P. This gives us the .1132 or 11.32% return in March 2008.Refer to ‘VW13E-05b.xlsx’ on tab ‘Excess Returns’How do you create a Characteristic Line in Excel?Step 3: Plot the excess returns for the S&P (on the x-axis) and for GE (on the y-axis) for each monthly data point.Click on the “Insert” tab in Excel and then choose the “Scatter” graph option.Upon generating the graph, right-click on any one of the data points. Choose “Add trendline ” from the options available.Choose “linear” and click “Close”.You may choose to change some formatting, but you have created a characteristic line. The slope of that line is an estimate of beta.Beta is estimated to be 0.756 for GERefer to ‘VW13E-05b.xlsx’ on tab ‘Excess Returns’How do you find beta?Trendline (as shown on the previous slide) calculates the beta estimate to be 0.756 for GE.Use the formula approach as Beta = [Covariance S&P, GE ] / [Variance S&P]0.000487/0.000644 = 0.756 beta estimate for GEThe same as the trendline approach!A third alternative is to us the “slope” function in Excel.Use ‘=slope(excess return array for GE, excess return array for S&P) = 0.756 beta estimate for GE.Again, same as others!!Remember? Determining Portfolio Standard Deviationmj=1mk=1sP = S S Wj Wk sjk Wj is the weight (investment proportion) for the jth asset in the portfolio,Wk is the weight (investment proportion) for the kth asset in the portfolio,sjk is the covariance between returns for the jth and kth assets in the portfolio.Remember? Determining Portfolio Standard DeviationWhy don’t we go through an example.Assume we take the raw S&P 500 and GE returns that we used in the previous example and assume they are ‘Stock 1’ and ‘Stock 2’ respectively (returns are to the left).Let us determine a portfolio standard deviation based on weights of 40% in ‘Stock 1’ and 60% ‘Stock 2’ respectively.Our first step will be to calculate a variance-covariance matrix for our two assets. This is shown above. Refer to ‘VW13E-05b.xlsx’ on tab ‘Diversification’BCD2PeriodStock 1Stock 231-0.60%11.68%42-3.48%-5.42%53-6.12%-4.63%64-0.86%-2.34%75-4.40%-6.97%861.48%-0.59%973.58%7.22%1081.29%0.29%119-3.20%1.26%1210-1.78%2.61%13113.25%1.93%14124.33%4.26%15131.00%1.27%1614-2.18%-2.39%17151.41%-3.13%18161.26%6.27%19171.65%0.48%20183.15%-0.53%21192.46%4.40%22202.13%4.16%23210.51%-0.80%24220.01%-3.07%2523-3.09%-0.95%26241.22%-0.55%27251.11%5.79%28260.05%1.14%29272.55%-6.57%3028-0.10%-1.18%31293.52%5.35%3230-1.77%0.70%Remember? Determining Portfolio Standard DeviationWe take the weights and appropriately multiply through with the associated variances and covariances to generate a portfolio variance of 0.000968. Refer to ‘VW13E-05b.xlsx’ on tab ‘Diversification’This equates to a standard deviation (SD) of 3.11% (note we assumed the individual stocks were a population).If we multiple 0.4(2.59% SD for Stock 1) + 0.6(4.25% SD for Stock 2) we would get 3.59%. As such, diversification has reduced the SD by 0.48% which is a significant reduction for adding just a single additional stock. Stock 1 Stock 2 PortfolioMonthly Return 0.278% 0.656% 0.505%Stand. Dev 2.587% 4.253% 3.111%Coefficient of Variation 9.30 6.48 6.16The portfolio has the LOWEST coefficient of variation due to diversification!As such, we have reduced risk per unit of return.Summary of the Portfolio Return and Risk Calculation