Constant Growth Valuation Template.

Financial Markets and Institutions, 6th Edition, by Jeff Madura Stock Valuation and Risk--Chapter 12 Constant Growth Valuation Template This program calculates PV of stock using constant growth model Enter all three variables in Column B, and only one variable in Column C of the input area (green cells). The program returns (red cells in the output area) present value of stock. The program also returns (blue cells in the conclusion area) the relationship logic between the stock price and the changed variable. Column B Input Area Original value Enter All Variables in Column B (Green Cells) and Value of Only One Variable in Column C (Green Cells) 1. Required rate of return k1 in cell B15 and k2 in cell C15 = 2. Constant growth rate g1 in cell B16 and g2 in cell C16 = D1 = 14.00% 4.00% $7.00 3. Expected dividend on stock Output Area Number of non-zero entries in Column C of Input Area (MUST BE 1) Original PV of stock = D1/(k1-g1) = $70.00 Conclusion: CALCULATION AREA: NEVER EVER TOUCH ANY CELL IN THIS AREA The price of the stock is reduced, because the cash flows would be discounted at a higher rate. The price of the stock is increased, because the cash flows would be discounted at a lower rate. The price of the stock is reduced, because the expected future cash flows in distant periods are reduced if the growth rate is revised downward. The price of the stock is increased, because the expected future cash flows in distant periods are increased if the growth rate is revised upward. Column C New (or Changed) Value n C (Green Cells) 0.00% 0.00% 0 Financial Markets and Institutions, 6th Edition, by Jeff Madura Stock Valuation and Risk--Chapter 12 CAPM Template This program calculates the required return on a stock using CAPM. Enter all three variables in Column B, and only one variable in Column C of the input area (green cells). The program returns (red cells in the output area) the required return on the stock. The program also returns (blue cells in the conclusion area) the relationship logic between the required return and the changed variable. Column B Input Area Original Value Enter All Variables in Column B (Green Cells) and Value of Only One Variable in Column C (Green Cells) Rf1 in Column B and Rf2 in Column C = 2. Market return Rm1 in Column B and Rm2 in Column C = 3. Beta of the stock j βj1 in Column B and βj2 in Column C = 1. Risk free rate Output Area Number of non-zero entries in Column C of Input Area (MUST BE 1) Original required return on stock j = 6.00% 13.00% 0.80 Rj = Rf1 + βj1 (Rm1-Rf1) Conclusion: CALCULATION AREA: NEVER EVER TOUCH ANY CELL IN THIS AREA The required rate of return would be lower, because it should reflect a premium above the risk-free rate (which is now lower). The required rate of return would be lower, because the premium that is added to the risk-free rate would now be lower. The required rate of return would be higher, because a given premium above the risk-free rate would now be higher. The required rate of return would be higher, because it should reflect a premium above the risk-free rate (which is now higher). The required rate of return would be higher, because the premium that is added to the risk-free rate would now be higher. The required rate of return would be lower, because a given premium above the risk-free rate would now be lower. ˆ k Column C New (or Changed) Value n C (Green Cells) 0.00% 0.00% 0.00 11.60% Input Error Input Error Input Error 0 Financial Markets and Institutions, 6th Edition, by Jeff Madura Stock Valuation and Risk--Chapter 12 Stock Portfolio Volatility Template This program calculates the volatility of a two-stock portfolio. Enter all four variables in Column B, and only one variable in Column C of the input area (green cells). WARNING: THE CORRELATION COEFFICIENT MUST BE GREATER THAN –1 OR LESS THAN +1. The program returns (red cells in the output area) the standard deviation of the portfolio. The program also returns (blue cells in the conclusion area) the relationship logic between the portfolio volatility and correlation coefficient between the stocks. Column B Input Area Original Value Enter All Variables in Column B (Green Cells) and New Value of CORR in Column C (Green Cell) It is Advised to Enter a Different Value for Item 1 in Column B and C in the Input Area 1. Correlation Coefficient between the ith and jth stocks = 2. Standard deviation of returns on the ith stock = CORRij 0.3000 10.00% 20.00% 40.00% 60.00% σi 3. Standard deviation of returns on the jth stock = σj 4. Proportion of funds invested in the ith stock = wi 5. Proportion of funds invested in the jh stock = wj (automatically 1-wi) Output Area Standard deviation of the portfolio consisting of ith and jth stock = σp = (wi2σi2 + wj2σi2 + 2 wiwjσiσjCORRij)1/2 Conclusion: It is Advised to Enter a Different Value for Item 1 in Column B and C in the Input Area 13.74% Estimated σp in output columns B and C corresponds to the CORR values in input columns B and C respectively. CALCULATION AREA: NEVER EVER TOUCH ANY CELL IN THIS AREA The stock portfolio volatility would be higher because the two individual stock returns move more in tandem now. The stock portfolio volatility would be lower because the two individual stock returns move less in tandem now. LESS THAN +1. Column C New (or Changed) Value 0.3000 13.74% ns B and C respectively. #VALUE! e in tandem now. in tandem now. 0 Financial Markets and Institutions, 6th Edition, by Jeff Madura ˆ ˆ Stock Valuation and Risk--Chapter 12 Value-at-Risk Template This program estimates the maximum expected loss (gain) based on expected return and standard deviation assuming a 95% confidence level Enter both variables in Column B, and only one variable in Column C of the input area (green cells). The program returns (red cells in the output area) the boundaries (lower and upper) of the return. The program also returns (blue cells in the conclusion area) the relationship logic between the return boundaries and changed variable. Column B Input Area Original Value Enter All Variables in Column B (Green Cells) and a Non-Zero Standard Deviation Value in Column C (Green Cell) 1. Standard deviation of returns in percent = σ 2.00% ˆ 2. Expected return in percent = R = 0.10% k Output Area Number of non-zero entries in Column C of Input Area (MUST BE 1) Lower boundary of the expected return = R - 1.65* σ Upper boundary of the expected return = R + 1.65* σ -3.20% 3.40% Estimated boundaries in output columns B and C correspond to the standard deviation values in input columns B and C respectively. Conclusion: For lower boundary For upper boundary CALCULATION AREA: NEVER EVER TOUCH ANY CELL IN THIS AREA The lower boundary would now be lower than before, because the larger standard deviation creates a greater deviation from (below) the expected outcome. The upper boundary would now be higher than before, because the larger standard deviation creates a greater deviation from (above) the expected outcome. Column C New (or Changed) Value in Column C (Green Cell) 0.00% 0