# Constant Growth Valuation Template by Rabia06

VIEWS: 1,612 PAGES: 10

• pg 1
```									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)
k1 in cell B15 and k2 in cell C15 =
1. Required rate of return                                                                           14.00%

2. Constant growth rate g1 in cell B16 and g2 in cell C16 =                                           4.00%

3. Expected dividend on stock   D1 =                                                                  \$7.00

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)
1. Risk free rate Rf1 in Column B and Rf2 in Column C =                                      6.00%

2. Market return Rm1 in Column B and Rm2 in Column C =                                      13.00%

3. Beta of the stock j bj1 in Column B and bj2 in Column C =                                   0.80

Output Area
Number of non-zero entries in Column C of Input Area (MUST BE 1)
Original required return on stock j =   Rj = Rf1 + bj1 (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 =   CORRij                                       0.3000

2. Standard deviation of returns on the ith stock = si                                                     10.00%

3. Standard deviation of returns on the jth stock = sj                                                     20.00%

4. Proportion of funds invested in the ith stock = wi                                                      40.00%

5. Proportion of funds invested in the jh stock = wj (automatically 1-wi)                                  60.00%

Output Area
Standard deviation of the portfolio consisting of ith and
jth stock = sp = (wi2si2 + wj2si2 + 2 wiwjsisjCORRij)1/2                                                   13.74%
Estimated sp in output columns B and C corresponds to the CORR values in input columns B and C respectively.

Conclusion:
It is Advised to Enter a Different Value for Item 1 in Column B and C in the Input Area
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
ˆ
k
ˆ
Stock Valuation and Risk--Chapter 12
k
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 = s                                                        2.00%
2. Expected return in percent = R =                                                      ˆk            0.10%

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* s                                         -3.20%
Upper boundary of the expected return = R + 1.65* s                                          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

```
To top