Show formulas on an excel spreadsheet for each step.

Integrated Case Chapter 8 Your current employer, Reinzenstein & Jillian Services, an important investment advisory company located in Chicago, has many wealthy clients. One of these clients, Mr. Owen Hyness, is in your portfolio. He recently inherited some assets and has asked you to evaluate them and provide sound financial recommendations. The client presently owns a bond portfolio with $1 million invested in zero coupon Treasury bonds that mature in 10 years. The client also has $2 million invested in the stock of Paraninfo Inc. Unfortunately, taking into consideration the serious virus outbreak that is impacting the markets at a global scale and that is not expected to be solved in the short run, interest rates will suffer a huge impact one year from now. Your first task is to determine the risk of the client's bond portfolio. After conducting some preliminary analysis, you have specified five possible economic scenarios at the end of the year. For each scenario, you have estimated the probability of the scenario occurring and the impact on interest rates and bond prices if the scenario occurs. Given this information, you have calculated the rate of return on 10-year zero coupon Treasury Bond for each scenario. The probabilities and returns are shown below: Return on a 10-Year Zero Coupon Treasury Bond During the Next Scenario Worst Case Poor Case Most Likely Good Case Best Case Probability of Scenario 0.10 0.20 0.40 0.20 0.10 Year -13% -5% 4% 18% 25% 1.00 You have also gathered historical returns for the past 10 years for Paraninfo Inc., Octum Co., and the stock market where these companies are listed. Year 1 2 3 4 5 6 7 8 9 10 Average return: Standard deviation: Correlation with the market: Beta: Market 29% 7 18 -22 -16 10 26 -10 -4 38 7.6% 19.27% 1.00 1.00 Historical Stock Returns Paraninfo 27% 17 -15 -14 3 -18 41 30 -33 28 ? ? ? Octum 41% -55 15 7 -27 40 17 -23 -5 75 8.5% 36.13% 0.668 1.25 The risk-free rate is 3% and the market risk premium is 5%. a. What are investment returns? What is the return on an investment that costs $3,000 and is sold after 2 years for $3,160? b. Use the scenario data to calculate the expected rate of return for the 10-year zero coupon Treasury bonds during the next year. C. What is stand-alone risk? Use the scenario data to calculate the standard deviation of the bond's return for the next year. d. Your client has decided that the risk of the bond portfolio is acceptable and wishes to leave it as it is. Now your client has asked you to use historical returns to estimate the standard deviation of Paraninfo's stock returns by using the provided 10 annual returns. e. Your client is shocked at how much risk Paraninfo stock has and would like to reduce the level of risk. You suggest that the client sell 25% of the Paraninfo stock and create a portfolio with 75% Paraninfo stock and 25% in the high-risk Octum stock. How do you suppose the client will react to replacing some of the Paraninfo stock with high-risk stock? Show the client what the proposed portfolio return would have been in each of year of the sample. Then calculate the average return and standard deviation using the portfolio's annual returns. How does the risk of this two-stock portfolio compare with the risk of the individual stocks if they were held in isolation? f. Explain the concept of coefficient of variation. Estimate the coefficient of variation of the two-stock portfolio and compare with the coefficient of variation of the stocks if they were held in isolation. What is the interpretation of the two-stock portfolio's coefficient of variation? Integrated Case Chapter 8 Your current employer, Reinzenstein & Jillian Services, an important investment advisory company located in Chicago, has many wealthy clients. One of these clients, Mr. Owen Hyness, is in your portfolio. He recently inherited some assets and has asked you to evaluate them and provide sound financial recommendations. The client presently owns a bond portfolio with $1 million invested in zero coupon Treasury bonds that mature in 10 years. The client also has $2 million invested in the stock of Paraninfo Inc. Unfortunately, taking into consideration the serious virus outbreak that is impacting the markets at a global scale and that is not expected to be solved in the short run, interest rates will suffer a huge impact one year from now. Your first task is to determine the risk of the client's bond portfolio. After conducting some preliminary analysis, you have specified five possible economic scenarios at the end of the year. For each scenario, you have estimated the probability of the scenario occurring and the impact on interest rates and bond prices if the scenario occurs. Given this information, you have calculated the rate of return on 10-year zero coupon Treasury Bond for each scenario. The probabilities and returns are shown below: Return on a 10-Year Zero Coupon Treasury Bond During the Next Scenario Worst Case Poor Case Most Likely Good Case Best Case Probability of Scenario 0.10 0.20 0.40 0.20 0.10 Year -13% -5% 4% 18% 25% 1.00 You have also gathered historical returns for the past 10 years for Paraninfo Inc., Octum Co., and the stock market where these companies are listed. Year 1 2 3 4 5 6 7 8 9 10 Average return: Standard deviation: Correlation with the market: Beta: Market 29% 7 18 -22 -16 10 26 -10 -4 38 7.6% 19.27% 1.00 1.00 Historical Stock Returns Paraninfo 27% 17 -15 -14 3 -18 41 30 -33 28 ? ? ? Octum 41% -55 15 7 -27 40 17 -23 -5 75 8.5% 36.13% 0.668 1.25 The risk-free rate is 3% and the market risk premium is 5%. a. What are investment returns? What is the return on an investment that costs $3,000 and is sold after 2 years for $3,160? b. Use the scenario data to calculate the expected rate of return for the 10-year zero coupon Treasury bonds during the next year. C. What is stand-alone risk? Use the scenario data to calculate the standard deviation of the bond's return for the next year. d. Your client has decided that the risk of the bond portfolio is acceptable and wishes to leave it as it is. Now your client has asked you to use historical returns to estimate the standard deviation of Paraninfo's stock returns by using the provided 10 annual returns. e. Your client is shocked at how much risk Paraninfo stock has and would like to reduce the level of risk. You suggest that the client sell 25% of the Paraninfo stock and create a portfolio with 75% Paraninfo stock and 25% in the high-risk Octum stock. How do you suppose the client will react to replacing some of the Paraninfo stock with high-risk stock? Show the client what the proposed portfolio return would have been in each of year of the sample. Then calculate the average return and standard deviation using the portfolio's annual returns. How does the risk of this two-stock portfolio compare with the risk of the individual stocks if they were held in isolation? f. Explain the concept of coefficient of variation. Estimate the coefficient of variation of the two-stock portfolio and compare with the coefficient of variation of the stocks if they were held in isolation. What is the interpretation of the two-stock portfolio's coefficient of variation