excel

profiledlc523
fin350.week7_.xlsx
Home>Business & Finance homework help>Accounting homework help>excel

P 8-9

Requirement a
Year Beginning price End price Increase in price Rate of return
2012 $ 14.36 $ 21.55 $ 7.19 50.07%
2013 $ 21.55 $ 64.78 $ 43.23 200.60%
2014 $ 64.78 $ 72.38 $ 7.60 11.73%
2015 $ 72.38 $ 91.80 $ 19.42 26.83%
289.24%
Requirement b
Total return 289.24%
Number of years 4
Average return 72.31%
Requirement c
Year Rate of return (Rate of return - Average return)^2
2012 50.07% 0.0495
2013 200.60% 1.6459
2014 11.73% 0.3670
2015 26.83% 0.2068
2.2692
Sample divisor 3
Variance 0.7564
Standard deviation 86.97%
Requirement d
Standard deviation 86.97%
Average return 72.31%
Coefficient of variation 1.20

P 8-14

Alternative 1
Year Return
2016 16%
2017 17%
2018 18%
2019 19%
70%
Requirement a
Expected return 17.50%
Requirement b
Year Return (Return - Expected return)^2
2016 16% 0.000225
2017 17% 0.000025
2018 18% 0.000025
2019 19% 0.000225
70% 0.000500
Sample divisor 3
Variance 0.0001666667
Standard deviation 1.29%
Requirement c
Standard deviation 1.29%
Expected return 17.50%
Coefficient of variation 0.0738
Alternative 2
Asset F Asset G Portfolio return
Year Return Weight Weighted return Return Weight Weighted return
2016 16% 50% 8.00% 17% 50% 8.50% 16.50%
2017 17% 50% 8.50% 16% 50% 8.00% 16.50%
2018 18% 50% 9.00% 15% 50% 7.50% 16.50%
2019 19% 50% 9.50% 14% 50% 7.00% 16.50%
66.00%
Requirement a
Expected return 16.50%
Requirement b
Year Return (Return - Expected return)^2
2016 16.50% - 0
2017 16.50% - 0
2018 16.50% 0.000000
2019 16.50% - 0
66% 0.000000
Sample divisor 3
Variance 0
Standard deviation 0.00%
Requirement c
Standard deviation 0.00%
Expected return 16.50%
Coefficient of variation - 0
Alternative 3
Asset F Asset H Portfolio return
Year Return Weight Weighted return Return Weight Weighted return
2016 16% 50% 8.00% 14% 50% 7.00% 15.00%
2017 17% 50% 8.50% 15% 50% 7.50% 16.00%
2018 18% 50% 9.00% 16% 50% 8.00% 17.00%
2019 19% 50% 9.50% 17% 50% 8.50% 18.00%
66.00%
Requirement a
Expected return 16.50%
Requirement b
Year Return (Return - Expected return)^2
2016 15.00% 0.000225
2017 16.00% 0.000025
2018 17.00% 0.000025
2019 18.00% 0.000225
66% 0.000500
Sample divisor 3
Variance 0.00
Standard deviation 1.29%
Requirement c
Standard deviation 1.29%
Expected return 16.50%
Coefficient of variation 0.0782
Requirement d
It is recommended to go for Alternative 2 because it has lowest coefficient of variation. Lowest coefficient of variation means that alternative 2 has lowest risk.

P 8-27

Requirement a
Asset Cost Weights Beta Weighted beta
A $ 20,000 0.20 0.80 0.16
B $ 35,000 0.35 0.95 0.33
C $ 30,000 0.30 1.50 0.45
D $ 15,000 0.15 1.25 0.19
$ 100,000 1.13
Portfolio Beta 1.13
Requirement b
Asset Cost Value today Increase in price Income Total return Percentage return
A $ 20,000 20000 $ 0 1600 $ 1,600 8.00%
B $ 35,000 36000 $ 1,000 1400 $ 2,400 6.86%
C $ 30,000 34500 $ 4,500 0 $ 4,500 15.00%
D $ 15,000 16500 $ 1,500 375 $ 1,875 12.50%
Requirement c
Asset Cost Weights Percentage return Weighted return
A $ 20,000 0.20 8.00% 1.60%
B $ 35,000 0.35 6.86% 2.40%
C $ 30,000 0.30 15.00% 4.50%
D $ 15,000 0.15 12.50% 1.88%
$ 100,000 10.38%
Percentage return of portfolio 10.38%
Requirement d
Expected return = Risk free rate + (Beta * Market risk premium)
Market risk premium
Asset Beta Market return Risk free rate Market return-Risk free rate Expected return
A 0.80 10% 4% 6.00% 8.80%
B 0.95 10% 4% 6.00% 9.70%
C 1.50 10% 4% 6.00% 13.00%
D 1.25 10% 4% 6.00% 11.50%
Requirement e
Asset Actual return CAPM Return
A 8.00% 8.80%
B 6.86% 9.70%
C 15.00% 13.00%
D 12.50% 11.50%
From the above table table, it can be seen that asset A and Asset B have underperformed as compared to the return estimated by CAPM. This can be due to error is estimation of CAPM factors. CAPM uses beta for calculation of expected return. In case there is error in beta estimated, the CAPM shall provide incorrect return.

P 9-5

Alternative A Alternative B Alternative C Alternative D
Par value of Bond $ 1,000 $ 1,000 $ 1,000 $ 1,000
Premium on issue of bond $ 250 $ 50 $ 0 $ 0
Discount on issue of bond $ 0 $ 0 $ 0 $ -75
Flotation costs $ -30 $ -30 $ -30 $ -30
Issue price of bond $ 1,220 $ 1,020 $ 970 $ 895
Coupon rate 9% 7% 6% 5%
Annual coupon amount $ 90 $ 70 $ 60 $ 50
Years to maturity 16 5 7 10
Preta cost of debt 6.71% 6.52% 6.55% 6.46%
Tax rate 40% 40% 40% 40%
After tax cost of debt 4.03% 3.91% 3.93% 3.87%

P 9-7

Requirement a
Par value $ 100
Dividend rate 12%
Annual dividend $ 12
Selling price $ 97.50
Flotation cost $ 2.50
Net proceeds per share $ 95.00
Cost of the preferred stock Annual dividend/Net proceeds 12.63%
Requirement b
Par value $ 100
Dividend rate 10%
Annual dividend $ 10
Net proceeds per share $ 90.00
Cost of the preferred stock Annual dividend/Net proceeds 11.11%

P 9-9

Requirement a
Market return 11%
Risk free rate 6%
Beta 1.2
Risk premium (Market return - Risk free rate)*Beta 6%
Requirement b
Risk free rate 6%
Risk premium 6%
Required return Risk free rate + Risk premium 12%
Requirement c
Cost of common stock equity using the CAPM. 12%

P 9-10

Requirement a
Dividend for 2015 (D4) $ 3.10
Dividend for 2011 (D1) $ 2.12
Number of years (n) 4
Total growth in 4 years (D4/D1)^(1/n) 1.0997
Growth rate Total growth - 1 9.97%
Requirement b
Net proceeds $ 52.00
Requirement c
Next dividend $ 3.40
Current price $ 57.50
Growth rate 9.97%
Cost of retained earnings (Next dividend/Current price)+Growth rate 15.88%
Requirement d
Next dividend $ 3.40
Net proceeds $ 52.00
Growth rate 9.97%
Cost of new common stock (Next dividend/Net proceeds)+Growth rate 16.50%

P 9-17

Requirement a
Par value of Bond $ 1,000
Issue price of bond $ 980
Flotation costs $ -30
Net proceeds on issue of bond $ 950
Coupon rate 10%
Annual coupon amount $ 100
Years to maturity 10
Preta cost of debt 10.84%
Tax rate 40%
After tax cost of debt 6.51%
Requirement b
Par value per share $ 100
Sale price per share $ 65
Underwriter's fee $ 2
Net proceeds on issue of share $ 63
Dividend rate 8%
Dividend amount $ 8.00
Cost of preferred stock (Dividend amount/Net proceeds) 12.70%
Requirement c
Dividend for 2015 (D4) $ 3.75
Dividend for 2011 (D1) $ 2.85
Number of years (n) 4
Total growth in 4 years (D4/D1)^(1/n) 1.0710
Growth rate Total growth - 1 7.10%
Next dividend $ 4
Selling price per share $ 50
Underpricing of share $ 5
Flotation costs $ 3
Net proceeds $ 42
Cost of new common stock (Next dividend/Net proceeds)+Growth rate 16.63%
Requirement d
Component of capital Weights Cost of capital Weighted cost
Debt 40% 6.51% 2.60%
Preferred stock 10% 12.70% 1.27%
Common stock 50% 16.63% 8.31%
12.19%
Weighted average cost of capital 12.19%