How do you calculate portfolio return, portfolio beta and the expected return of the portfolio based on the CAPM model for a stock portfolio?

FIN3030 Assignment No.2 Your Name here *All assignments must be submitted in Word Doc or PDF format via Moodle, NO email/hard copy submission will be accepted. *All solutions must be typed. You must build tables and models in Excel to solve the problems; and then you need to copy and paste the result back to the Word Document. Please work with your classmates because this assignment is going to be challenging. However, when you accomplish this, it will be major milestone on your way to become a professional financial analyst. Good luck! Your mother, knowing that you are in finance class, has asked you to help a friend of hers, Mrs. Fong. Mrs. Fong's husband recently passed away. He left her two portfolios, one of bonds, one of stocks. Unfortunately, Mrs. Fong does not know anything about investments, so your mother has volunteered you to help her understand her portfolios. Look at the information below and analyze her portfolios. Do all calculations in excel. NO TYPING INTO FORMULAS. Use text boxes to answer word questions. Bond Portfolio The bond portfolio has seven bonds: A $20,000 par value bond from 3M Co. with a coupon of 3.00 %. The bond is a senior secured bond that matures in 2025 and has a Standard and Poors rating of AA-. The bond is currently selling for $20,232. Mr. Fong purchased the bond when it was issued in 2005 for 95.0% of par. A $30,000 par value bond from General Motors Financial, rated BB, with a 4.25% coupon, maturing in 2023. This bond is selling for 95.00% of par. A Starwood bond with a par of $35,000, rated BBB and maturing in 2019. It has a coupon of 7.15% and is selling for 117.90% of par. An Amgen $60,000 par value bond, rated BBB with a 6.40% coupon and maturing in 2039. This bond is selling for $72,120. The bond is callable in 2024 for a 3.20% call premium. A Kellogg Co. bond rated A with a $50,000 par and a 2.750% coupon. The bond matures in 2023. The bond has a yield to maturity of 2.86% A Safeway bond with a CCC rating. The bond has a par value of $40,000 and a coupon of 7.450%. It matures in 2027 and is yielding 7.758% (YTM). A US Treasury bond with a par of $25,000 and a 3.125% coupon. It matures in 2041 and is yielding 2.332% (YTM). UST bonds are rated AAA. 1. Build a table of the bonds that shows all their information including a. market price, b. current yield and c. yield to maturity. 4. Explain the difference between current yield and yield to maturity. 5. What is the market value and yield of the bond portfolio? 6. How much cash flow can Mrs. Fong expect from this portfolio next year? 7. If Mrs. Fong were to sell the 3M bond today, what would be her realized yield? 8. What is the yield to call of the Amgen bond? 9. Mrs. Fong wants to know how risky her bonds are. Explain risk using her bonds as an example. (30 - 60 words) 10. If the Federal Reserve raises interest rates this year, what will happen to the value, yield and cash flow of Ms. Fong's portfolio? FIN3030 Assignment No.2 Your Name here *All assignments must be submitted in Word Doc or PDF format via Moodle, NO email/hard copy submission will be accepted. 11. You think the Kellogg bond may be downgraded to BBB due to falling profitability and concerns about the company's financial health. What would be expected to happen to the value, yield and cash flow of this bond if it is downgraded? _________________________________________________________________________________ Stock Portfolio Below is a table of information on the stocks in Mrs. Fong's stock portfolio. Name Progressive Corp. Medtronic Starbucks Berkshire Hath -B Procter & Gamble Visa, Inc. PACCAR Microsof Rangold Pioneer Nat Res Amgen Coca-Cola Bristol-Meyers Squibb Maximus Starwood J2 Global Walmart General Mills Apple Home Depot Monsanto Exxon Global Robotics Nike Toyota Pfizer Shares Price 7/1/2014 Price 7/1/2015 EPS Dividen d Beta 300 $25.48 $28.44 $2.24 $0.69 0.87 250 600 $63.91 $39.10 $74.08 $53.89 $2.15 $1.78 $1.52 $0.64 1.44 0.87 100 $127.24 $137.52 $7.28 $0.00 0.29 200 $79.28 $79.72 $2.44 $2.65 1.00 125 200 100 100 200 150 400 $53.17 $63.78 $41.87 $84.59 $228.75 $120.31 $42.29 $67.85 $64.59 $44.45 $65.03 $136.23 $154.99 $39.49 $2.42 $4.47 $1.48 $2.23 $3.40 $7.55 $1.71 $0.48 $0.96 $1.44 $0.60 $0.08 $3.16 $1.32 0.85 1.32 0.87 0.11 0.52 1.01 0.96 475 $48.12 $67.67 $1.07 $1.48 1.06 125 200 200 300 150 250 450 250 125 200 200 425 200 $42.85 $81.50 $52.03 $75.28 $52.90 $93.52 $81.82 $124.30 $101.36 $28.22 $78.00 $122.26 $30.08 $66.14 $82.00 $68.01 $71.88 $57.09 $126.60 $112.07 $107.66 $82.37 $26.05 $109.43 $133.60 $33.69 $2.37 $3.29 $2.50 $4.79 $2.11 $8.65 $5.13 $5.41 $5.62 $0.00 $3.95 $11.76 $1.41 $0.18 $1.50 $1.23 $1.96 $1.76 $2.08 $2.36 $2.16 $2.92 $0.00 $1.12 $4.02 $1.12 3.66 0.91 1.44 0.87 0.71 1.09 1.04 1.08 0.93 1.95 0.72 0.55 1.08 The return on the market has been 8.15% and the current risk free rate is 2.05%. FIN3030 Assignment No.2 Your Name here *All assignments must be submitted in Word Doc or PDF format via Moodle, NO email/hard copy submission will be accepted. 1. Compute the total holding period return, capital gain return, current return (cash flow return) for each stock. 2. Compute the expectation of each stock's performance based on the CAPM model. Show whether each stock under- or over-performed. 3. Compute the beginning and ending market value of the portfolio. 4. Compute the portfolio return, the portfolio beta and the expected return of the portfolio based on the CAPM model. Did the portfolio over or under perform? 5. How much cash flow did the Fong's receive from their stock portfolio in this year? 6. Since General Mills and Proctor and Gamble are mature companies in stable markets, it is likely that they have approximately constant growth at about the rate of GDP growth. GDP is expected to grow at 3.5% this year. Based on this, what would the dividend growth model predict the price of each of these should be? 7. The PE ratio for the Agricultural Products industry is 18.5x. Based on Monsanto's most recent earnings, what is the expected price of Monsanto stock