Question

1 Approved Answer

Posted on Jul 11, 2024

a) Based on two years of historic data provided by Bloomberg, calculate expected annual return, annual standard deviation and Sharpe ratio for each of the

a) Based on two years of historic data provided by Bloomberg, calculate expected annual return, annual standard deviation and Sharpe ratio for each of the four exchange-traded funds (ETFs) portfolios (QQQ Nasdaq 100, SPY S&P 500, HYG iBoxx High Yield Bond, XBT -- Bitcoin). Hint: build up from monthly returns to annual.

b) Calculate the beta, expected annual return, annual standard deviation and Sharpe ratio for an equally weighted portfolio ( QQQ, SPY, HYG, XBT).

c) Do any of the portfolios lie on the security market line? If not are they above or below the line? Correctly draw SML with ETFs for extra points.

d) Construct a portfolio with an expected return consistent with the equity market using iBoxx High Yield ETF (HYG) and US Treasury 10-year bond. Assume you can invest and borrow at the risk-free rate. image text in transcribed

Monthly Return SPY ETF HYG ETF QQQ ETF XBT / USD PERIOD DATE 24 23 22 21 20 19 18 17 16 15 14 13 12 11 10 NDX 100 S&P 500 Hi Yld Bond BITCOIN QQQ ETF SPY ETF HYG ETF XBT/USD Beta = 1.08 Beta = 1.01 Beta = 0.34 Beta = 0 10.2020 275.92 342.58 93.42 13,850 09.2020 284.30 350.64 93.03 10,707 08.2020 301.44 365.01 93.86 11,679 07.2020 271.43 340.51 93.83 11,346 06.2020 252.60 320.93 89.29 9,147 05.2020 237.83 316.09 89.76 9,505 04.2020 222.88 301.12 87.20 8,827 03.2020 193.66 266.66 83.18 6,481 02.2020 209.12 305.88 92.45 8,633 01.2020 222.38 331.52 93.63 9,354 12.2019 215.61 330.99 93.65 7,158 11.2019 207.78 322.58 92.17 7,710 10.2019 199.46 310.69 91.62 9,189 09.2019 190.90 303.36 91.58 8,241 08.2019 189.35 298.35 91.16 9,626 07.2019 192.83 302.83 90.51 10,027 06.2019 188.24 297.72 90.37 11,393 05.2019 175.17 279.15 87.61 8,503 04.2019 190.68 297.57 89.31 5,238 03.2019 180.56 285.32 88.43 4,093 02.2019 173.88 280.92 87.30 3,803 01.2019 168.66 271.55 86.26 3,417 12.2018 154.57 250.92 81.83 3,674 11.2018 169.54 276.20 83.98 3,930 10.2018 169.82 270.63 84.35 6,302 -2.95% -5.68% 11.06% 7.45% 6.21% 6.71% 15.09% -7.39% -5.96% 3.14% 3.77% 4.17% 4.48% 0.82% -1.80% 2.44% 7.46% -8.13% 5.60% 3.84% 3.09% 9.12% -8.83% -0.17% 0.00% -2.30% -3.94% 7.19% 6.10% 1.53% 4.97% 12.92% -12.82% -7.73% 0.16% 2.61% 3.83% 2.41% 1.68% -1.48% 1.71% 6.65% -6.19% 4.29% 1.57% 3.45% 8.22% -9.15% 2.06% 0.00% 0.43% -0.88% 0.03% 5.09% -0.53% 2.94% 4.83% -10.02% -1.26% -0.02% 1.61% 0.60% 0.04% 0.46% 0.72% 0.16% 3.15% -1.91% 1.00% 1.29% 1.21% 5.42% -2.55% -0.44% 0.00% 29.36% -8.32% 2.93% 24.04% -3.77% 7.69% 36.18% -24.93% -7.71% 30.68% -7.16% -16.10% 11.51% -14.39% -3.99% -11.99% 33.98% 62.34% 27.97% 7.64% 11.28% -7.00% -6.52% -37.64% 0.00% 9 8 7 wa 2 1 0 Answer all section of question 1&2 using the attached excel spreadsheet. Show all answers on this sheet and your work on paper or on excel file uploaded to Canvas. Calculate the following (600 pts): Beta Expected Return =R QQQ 1.08 ?? SPY 1.01 ?? HYG 0.34 ?? XBT 0.0 ?? US-Treasury 0.0 0.8% Equity Risk Premium 1.0 5.2% Expected Market Return 1.0 6.0% Monthly Return SPY ETF HYG ETF QQQ ETF XBT / USD PERIOD DATE 24 23 22 21 20 19 18 17 16 15 14 13 12 11 10 NDX 100 S&P 500 Hi Yld Bond BITCOIN QQQ ETF SPY ETF HYG ETF XBT/USD Beta = 1.08 Beta = 1.01 Beta = 0.34 Beta = 0 10.2020 275.92 342.58 93.42 13,850 09.2020 284.30 350.64 93.03 10,707 08.2020 301.44 365.01 93.86 11,679 07.2020 271.43 340.51 93.83 11,346 06.2020 252.60 320.93 89.29 9,147 05.2020 237.83 316.09 89.76 9,505 04.2020 222.88 301.12 87.20 8,827 03.2020 193.66 266.66 83.18 6,481 02.2020 209.12 305.88 92.45 8,633 01.2020 222.38 331.52 93.63 9,354 12.2019 215.61 330.99 93.65 7,158 11.2019 207.78 322.58 92.17 7,710 10.2019 199.46 310.69 91.62 9,189 09.2019 190.90 303.36 91.58 8,241 08.2019 189.35 298.35 91.16 9,626 07.2019 192.83 302.83 90.51 10,027 06.2019 188.24 297.72 90.37 11,393 05.2019 175.17 279.15 87.61 8,503 04.2019 190.68 297.57 89.31 5,238 03.2019 180.56 285.32 88.43 4,093 02.2019 173.88 280.92 87.30 3,803 01.2019 168.66 271.55 86.26 3,417 12.2018 154.57 250.92 81.83 3,674 11.2018 169.54 276.20 83.98 3,930 10.2018 169.82 270.63 84.35 6,302 -2.95% -5.68% 11.06% 7.45% 6.21% 6.71% 15.09% -7.39% -5.96% 3.14% 3.77% 4.17% 4.48% 0.82% -1.80% 2.44% 7.46% -8.13% 5.60% 3.84% 3.09% 9.12% -8.83% -0.17% 0.00% -2.30% -3.94% 7.19% 6.10% 1.53% 4.97% 12.92% -12.82% -7.73% 0.16% 2.61% 3.83% 2.41% 1.68% -1.48% 1.71% 6.65% -6.19% 4.29% 1.57% 3.45% 8.22% -9.15% 2.06% 0.00% 0.43% -0.88% 0.03% 5.09% -0.53% 2.94% 4.83% -10.02% -1.26% -0.02% 1.61% 0.60% 0.04% 0.46% 0.72% 0.16% 3.15% -1.91% 1.00% 1.29% 1.21% 5.42% -2.55% -0.44% 0.00% 29.36% -8.32% 2.93% 24.04% -3.77% 7.69% 36.18% -24.93% -7.71% 30.68% -7.16% -16.10% 11.51% -14.39% -3.99% -11.99% 33.98% 62.34% 27.97% 7.64% 11.28% -7.00% -6.52% -37.64% 0.00% 9 8 7 wa 2 1 0 Answer all section of question 1&2 using the attached excel spreadsheet. Show all answers on this sheet and your work on paper or on excel file uploaded to Canvas. Calculate the following (600 pts): Beta Expected Return =R QQQ 1.08 ?? SPY 1.01 ?? HYG 0.34 ?? XBT 0.0 ?? US-Treasury 0.0 0.8% Equity Risk Premium 1.0 5.2% Expected Market Return 1.0 6.0%