professional documents
home
Upload
docsters
Upload
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
flag this doc
680
64
not rated
0
3/26/2008
English
Preview

Bond and Stock Valuation Template

Rabia06 3/26/2008 | 1750 | 153 | 2 | financial
Preview

Zero Growth Model - Valuation template

Rabia06 3/26/2008 | 374 | 33 | 0 | financial
Preview

A Template for Bond Valuation and Risk

Rabia06 3/26/2008 | 1203 | 97 | 1 | financial
Preview

A template for Stock valuation

Rabia06 3/26/2008 | 691 | 76 | 0 | financial
Preview

Template Form for Stock Valuation

Rabia06 3/26/2008 | 732 | 65 | 0 | financial
Preview

Stock Valuation Investment Templates

Rabia06 3/26/2008 | 603 | 109 | 0 | financial
Preview

Discounted Dividend Valuation Model Template

Rabia06 3/26/2008 | 499 | 33 | 0 | financial
Preview

Bond Valuation Using Excel Template - Bond Pricing

Rabia06 3/26/2008 | 1256 | 77 | 0 | financial
Preview

Cost Per Student- Valuation Template

Rabia06 3/26/2008 | 181 | 6 | 0 | financial
Preview

VALUATION MEASURES Report

Rabia06 3/26/2008 | 307 | 23 | 0 | financial
Preview

A High Quality Template for Basic Bond Valuation

Rabia06 3/26/2008 | 302 | 37 | 0 | financial
Preview

Mine Stock Valuation from Aussieontop

Rabia06 3/26/2008 | 413 | 29 | 0 | financial
Preview

Financial Analysis Spreadsheet Template - Stock Valuation

Rabia06 3/26/2008 | 1663 | 67 | 0 | financial
Preview

Template for Valuation Model for Tech Transfer Licensing

Rabia06 3/26/2008 | 199 | 15 | 0 | financial
Preview

Common Stock Beta Rate of Return and Valuation Template

Rabia06 3/26/2008 | 313 | 40 | 0 | financial
Preview

USACE Master- In-house Data Call Template

Rabia06 3/27/2008 | 431 | 20 | 0 | financial
Preview

Pool Acquisition Template

Rabia06 3/27/2008 | 379 | 31 | 0 | financial
Preview

Pivot Point Release Notes

Rabia06 3/27/2008 | 342 | 27 | 0 | financial
Preview

PF Budget Plan Template

Rabia06 3/27/2008 | 714 | 69 | 0 | financial
Preview

Interest Shortfall Rec Template

Rabia06 3/27/2008 | 353 | 28 | 0 | financial
Preview

Interest Rate Template

Rabia06 3/27/2008 | 566 | 56 | 0 | financial
Preview

Electronic Remittance Template

Rabia06 3/27/2008 | 335 | 7 | 0 | financial
Preview

DATA TEMPLATE ON INTERNATIONAL RESERVES FOREIGN CURRENCY LIQUIDITY

Rabia06 3/27/2008 | 246 | 7 | 0 | financial
Preview

BTL Referral Template

Rabia06 3/27/2008 | 312 | 8 | 0 | financial
Preview

Bill Tracking Report Template

Rabia06 3/27/2008 | 448 | 35 | 0 | financial
 
review this doc