Work must be shown in excel using formulas. Case Approach to Financial Planning

Question

Work must be shown in excel using formulas.
Case Approach to Financial Planning a Johnsorn Uma Johnson has been tracking the performance of her portfolio benchmarked against a ssive index. Here are her returns over the past few years along with the return of dex: Market Index Portfolio Year 12% 8% 19% 4 -4% 5% 1990 15% -4% 9% 2% 15% -6% 11% 8 d on Uma's data, answer the following questions: a. What is the standard deviation of Uma's portfolio and the market index? b. What is the geometric mean for her portfolio and the market index? How does this compare to the arithmetic mean? c. What is the correlation between Uma's portfolio and the market index? d. Based on the calculations from above, what is the beta of Uma's portfolio? e. Assuming a risk-free rate of 2 percent, what is Uma's CAPM? . Has her portfolio done worse, the same, or better, on a risk-adjusted basis, compared to he market index? How do you know? Given your calculations, what should Uma do with her portfolio?

Explanation / Answer

Year

Uma Portfolio return

Market Index

1

12%

9%

2

9%

8%

3

22%

19%

4

-4%

-1%

5

2%

5%

6

25%

19%

7

15%

15%

8

-6%

-4%

9

11%

9%

1-

Standard deviation = using stdevp function in MS excel =stdevp(12,9,22,-4,2,25,15,-6,11)

0.1069

0.0807

2-

Arithmatic Mean= Using average function in MS excel =average(12,9,22,-4,2,25,15,-6,11)

9.56%

8.78%

geometric mean take into account compounding while arithmatic average do not take compoundng effect.

Geometric mean =(no 1 * No 2 * No.3)^(1/n)

9.20%

7.44%

3-

correlation between portfolio and market index =Using correlation function in MS excel =correl(array1, array2)

0.98

4-

Beta of UMA portfolio = (standard deviation of portfolio/standard deviation of market)*correlation

1.34

5-

UMA CAPM required return= risk free rate+(market return-risk free rate)*beta

.02+(.0877-.02)*1.34

11.07%

6-

Her portfolio is doing worse as required rate of return is 11.07% while average return over the period is 10.69%

7-

UMA should revise his portfolio to earn return equivalent to risk involved into portfolio so return at least equal to 11.07%

Year

Uma Portfolio return

Market Index

1

12%

9%

2

9%

8%

3

22%

19%

4

-4%

-1%

5

2%

5%

6

25%

19%

7

15%

15%

8

-6%

-4%

9

11%

9%

1-

Standard deviation = using stdevp function in MS excel =stdevp(12,9,22,-4,2,25,15,-6,11)

0.1069

0.0807

2-

Arithmatic Mean= Using average function in MS excel =average(12,9,22,-4,2,25,15,-6,11)

9.56%

8.78%

geometric mean take into account compounding while arithmatic average do not take compoundng effect.

Geometric mean =(no 1 * No 2 * No.3)^(1/n)

9.20%

7.44%

3-

correlation between portfolio and market index =Using correlation function in MS excel =correl(array1, array2)

0.98

4-

Beta of UMA portfolio = (standard deviation of portfolio/standard deviation of market)*correlation

1.34

5-

UMA CAPM required return= risk free rate+(market return-risk free rate)*beta

.02+(.0877-.02)*1.34

11.07%

6-

Her portfolio is doing worse as required rate of return is 11.07% while average return over the period is 10.69%

7-

UMA should revise his portfolio to earn return equivalent to risk involved into portfolio so return at least equal to 11.07%

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.