You work for a large investment management firm. The analysts in your firm have made the following forecasts for the returns of stock One and stock Two: Stock Probability Stock Two One VERY VERY WEAK 4% 55% -54% VERY WEAK 12% 25% -44% WEAK 16% 18% 15% SOFT 18% 16% 17% STRONG 20% 15% 18% VERY STRONG 15% 10% 20% VERY VERY STRONG 10% -25% 46% ROBUST 5% -75% 66% 100.0% a) Calculate the expected returns, variances and standard deviations for Stock One and Stock Two. b) What is the covariance of returns for Stock One and Stock Two? What is the correlation coefficient between the returns of Stock One and Stock Two? c) What are the expected return and standard deviation of a portfolio where 30% of the portfolio is in Stock One and 70% of the portfolio is in Stock Two? d) Create a table (like the one shown below) showing the portfolio expected return and standard deviation for different weights in each stock. This can be done using an Excel data table. Start with 100% in Stock One and 0% in Stock Two, and complete the table considering increments of 10%. The last row will have 0% in Stock One and 100% in Stock Two. e) Based on your table, create a chart (or graph) with your results. Weight in Stock One 100% 90% 80% 70% 60% 50% 40% 30% 20% Portfolio standard deviation Portfolio expected return 10% 0% Note: All calculations should be rounded to two decimal places if you are using percentages. If you are using decimals, the answer should be rounded to four decimal places. You work for a small investment management firm. You have been provided with the following historical information for three stocks and the market index. The information is shown in the table below. The amounts are in dollars ($). X Inc. Y Inc. Z Inc. Market Stock Stock Stock Dividend Dividend Dividend Index Price Price Price 2014 36 28 131 21,720 2015 48 0.75 35 1.75 141 3.65 24,325 2016 35 0.80 32 1.75 101 3.65 19,920 2017 43 0.85 44 1.80 129 3.75 23,100 2018 55 0.90 56 2.10 148 3.80 24,500 2019 72 1.05 65 3.00 165 4.33 28,020 2020 78 1.10 98 3.20 184 4.85 31,572 2021 91 1.25 108 3.80 192 5.45 37,445 2022 80 1.25 115 4.00 181 5.45 33,283 2023 105 1.55 130 4.50 220 6.25 40,975 2024 120 1.75 150 4.75 204 6.25 43,220 Using the data provided, calculate the following for each of the stocks and the market index: a) What is the average annual return for the past ten years? b) What is the geometric average annual return for the past ten years? c) What is the population standard deviation and the sample standard deviation? d) What is the correlation coefficient between stocks X and Y, between stocks X and Z, and between stocks Y and Z, respectively? e) What is the sample covariance between stocks X and Y, between stocks X and Z. and between stocks Y and Z, respectively? f) What is the coefficient of variation for each of the stocks and the market index? You should consider the sample standard deviations in your calculations. g) What is the beta of each stock? You can use Excel's "slope" function to estimate this. h) Assume that the beta of stock X is 1.50, the beta of stock Y is 0.80, and the beta of stock Z is 1.20. What is the beta of a portfolio where the weight in stock X is 25%, the weight in stock Y is 35%, and the weight in stock Z is 40%? i) Assuming the risk-free rate is 4.25% and the expected return on the market is 8.10%, what is the expected return on the portfolio in part (h) above?
Q1:
Blossom is 30 years old. She plans on retiring in 25 years, at the age of 55. She believes she will live until she is 105.
In order to live comfortably, she needs a substantial retirement income. She wants to receive a weekly income of $5,000 during retirement. The payments will be made at the beginning of each week during her retirement.
Also, Blossom has pledged to make an annual donation to her favorite charity during her retirement. The payments will be made at the end of each year. There will be a total of 50 annual payments to the charity. The first annual payment will be for $20,000. Blossom wants the annual payments to increase by 3% per year. The payments will end when she dies.
In addition, she would like to establish a scholarship at Toronto Metropolitan University. The first payment would be $80,000 and would be made 3 years after she retires. Thereafter, the scholarship payments will be made every year. She wants the payments to continue after her death, therefore the payments will go on forever. To keep pace with inflation, Blossom would like the amount of the scholarship payments to increase by 2.5% each year.
Blossom has a niece, Maddy. Maddy is 15 years old. Blossom plans on giving Maddy $2,000,000 when Maddy turns 45 years old. (Hint: Consider the number of years between this payment and the time Blossom retires).
During retirement, Blossom expects to earn 6.5% per year compounded annually.
Blossom currently has $100,000 in investment account A, that earns 7% interest per year compounded quarterly. Blossom currently contributes $500 every month to investment account B. These contributions are made at the end of each month and will continue until she retires at age 55. Blossom expects to earn 9% per year compounded semi-annually on her monthly contributions to investment account B.
How much money does she need when she retires at the age of 55? Be certain to include all of her retirement goals.
How much money will she have when she retires?
How much is she short? [Hint: The shortfall is the difference between the amounts in parts (a) and (b)]
In order to finance any shortfall, Blossom will make monthly contributions into a new retirement account, investment account C, that will earn 8.5% per year compounded annually. The contributions will be made at the beginning of each month until she retires at age 55. How much must she contribute each month to the investment account C?
Q2:
You are an analyst in charge of valuing common stocks. You have been asked to value two stocks. The first stock NEWER Inc. just paid a dividend of $6.00. The dividend is expected to increase by 60%, 45%, 30% and 15% per year, respectively, in the next four years. Thereafter, the dividend will increase by 4% per year in perpetuity.
Calculate NEWER’s expected dividend for t = 1, 2, 3, 4 and 5.
The required rate of return for NEWER stock is 14% compounded annually.
What is NEWER’s stock price?
The second stock is OLDER Inc. OLDER Inc. will pay its first dividend of $10.00 three (3) years from today. The dividend will increase by 30% per year for the following four (4) years after its first dividend payment. Thereafter, the dividend will increase by 3% per year in perpetuity.
Calculate OLDER’s expected dividend for t = 1, 2, 3, 4, 5, 6, 7 and 8.
The required rate of return for OLDER stock is 16% compounded annually.
What is OLDER’s stock price?
Now assume that both stocks have a required rate of return of 40% per year compounded annually for the first six years, 25% per year compounded annually for the following five years, then 12% per year compounded annually thereafter.
What is NEWER’s stock price?
What is OLDER’s stock price?
(Hint: You may need to forecast more dividends than you did in parts a, and c.)
Note 1: You cannot use the NPV function to immediately value the stocks at time 0, as the required rate of return changes during the forecast period.
Note 2: All calculations should be rounded to the nearest cent. That is two decimal places.
Q3 and Q4 will be the first and second photo provided.
Please answer using google sheets/excel to confirm your calculations.
Please use finance functions included in google sheets/excel whereever it is deemed appropriate, such as PV, FV, EFFECT, PMT.. etc
Answer ALL the questions, any missed questions WILL get reported.
When rounding, DO NOT ROUND ANY CALCULATIONS (this can be easily done with functions in excel/google sheets), and round the final answers to 2 decimal places.
Please provide the google sheets/excel in your answer.
Thank you very much for assistance!!
Step by step
Solved in 2 steps
