CFIN6
Spreadsheet Problem Solution
Chapter 7
1.
INPUT DATA:
KEY OUTPUTS:
Nonconstant growth
0.00%
Current price (P0)
$21.60
Normal (constant) growth
3.00%
Price at the end of Year
3
$23.41
Req. rate of return
14.00%
Dividend yield in Year
1
11.57%
Last dividend (D0)
Dividend yield in Year
3
11.00%
Supernormal period
3
years
Cap. gains yield in Year
1
2.43%
Cap. gains yield in Year
3
3.00%
14.00%
MODEL-GENERATED DATA:
Expected dividends:
PV of dividends:
Year
Year
1
2.50
1
2.19
2
2.50
2
1.92
3
2.50
3
1.69
4
4
5
5
3
Yields in Year
3
Yields in Year
1
Dividend
11.00%
Dividend
11.57%
Capital Gain
3.00%
Capital Gain
2.43%
Total
14.00%
Total
14.00%
CFIN6
2. a. & b.
INPUT DATA:
KEY OUTPUTS:
Nonconstant growth
15.00%
Current price (P0)
$39.44
Normal (constant) growth
5.00%
Price at the end of Year
5
$52.80
Req. rate of return
12.00%
Dividend yield in Year
1
Last dividend (D0)
$1.75
Dividend yield in Year
5
Supernormal period
years
1
5
MODEL-GENERATED DATA:
Expected dividends:
PV of dividends:
Year
Year
1
2.01
1
1.80
2
2.31
2
1.85
3
2.66
3
1.89
4
3.06
4
1.95
5
3.52
5
2.00
Stock price:
Stock price:
End of Year
5
52.80
Today, P0
39.44
5
1
Dividend
7.00%
Dividend
5.10%
Capital Gain
5.00%
Capital Gain
6.90%
Total
12.00%
Total
12.00%
c.
INPUT DATA:
KEY OUTPUTS:
Nonconstant growth
12.00
%
Current price (P0)
$31.50
Normal (constant) growth
4.00%
Price at the end of Year
5
$40.09
Req. rate of return
%
1
6.22%
Last dividend (D0)
$1.75
5
8.00%
Supernormal period
5
years
1
5.78%
5
4.00%
12.00
Total return both yrs
12.00
%
MODEL-GENERATED DATA:
Expected dividends:
PV of dividends:
Year
Year
1
1.96
1
1.75
2
2.20
2
1.75
3
2.46
3
1.75
4
2.75
4
1.75
Stock price:
Stock price:
End of Year
5
40.09
Today, P0
31.50
Yields in Year
5
Yields in Year
1
Dividend
8.00%
Dividend
6.22%
Capital Gain
4.00%
Capital Gain
5.78%
Total
12.00%
Total
%
INPUT DATA:
KEY OUTPUTS:
Nonconstant growth
15.00%
Current price (P0)
$34.30
Normal (constant) growth
5.00%
Price at the end of Year
5
$46.20
Req. rate of return
13.00%
Dividend yield in Year
1
5.87%
Last dividend (D0)
$1.75
Dividend yield in Year
5
8.00%
Supernormal period
5
years
1
7.13%
5
5.00%
13.00%
MODEL-GENERATED DATA:
Expected dividends:
PV of dividends:
Year
Year
1
2.01
1
1.78
2
2.31
2
1.81
3
2.66
3
1.84
CFIN6
4
3.06
4
1.88
5
3.52
5
1.91
Stock price:
End of Year
5
46.20
Today, P0
34.30
5
Yields in Year
1
Dividend
8.00%
Dividend
5.87%
Capital Gain
5.00%
Capital Gain
7.13%
Total
13.00%
Total
13.00%
(2) r = 15%
INPUT DATA:
KEY OUTPUTS:
Nonconstant growth
15.00%
Current price (P0)
$27.13
Normal (constant) growth
5.00%
Price at the end of Year
5
$36.96
Req. rate of return
15.00%
1
7.42%
Last dividend (D0)
$1.75
5
10.00%
Supernormal period
5
years
Cap. gains yield in Year
1
7.58%
Cap. gains yield in Year
5
5.00%
15.00%
MODEL-GENERATED DATA:
Expected dividends:
PV of dividends:
Year
Year
1
2.01
1
1.75
2
2.31
2
1.75
3
2.66
3
1.75
4
3.06
4
1.75
5
3.52
5
1.75
Stock price:
Stock price:
End of Year
5
36.96
Today, P0
27.13
Yields in Year
5
Yields in Year
1
Dividend
10.00%
Dividend
7.42%
Capital Gain
5.00%
Capital Gain
7.58%
CFIN6
Total
15.00%
Total
15.00%
(3) r = 20%
INPUT DATA:
KEY OUTPUTS:
Nonconstant growth
15.00%
Current price (P0)
$17.62
Normal (constant) growth
5.00%
Price at the end of Year
5
$24.64
Req. rate of return
20.00%
1
11.42%
Last dividend (D0)
$1.75
5
15.00%
Supernormal period
5
years
Cap. gains yield in Year
1
8.58%
Cap. gains yield in Year
5
5.00%
20.00%
MODEL-GENERATED DATA:
Expected dividends:
PV of dividends:
Year
Year
1
2.01
1
1.68
2
2.31
2
1.61
3
2.66
3
1.54
4
3.06
4
1.48
5
3.52
5
1.41
Stock price:
Stock price:
End of Year
5
24.64
Today, P0
17.62
Yields in Year
5
Yields in Year
1
Dividend
15.00%
Dividend
11.42%
Capital Gain
5.00%
Capital Gain
8.58%
Total
20.00%
Total
20.00%