How would this be calculated without the use of Excel?
You are asked to compare the following securities: a treasury bill, FTSE100 (market portfolio), JBond PLC (a utility company stock), Skyfall PLC (a high-tech company stock) and Thunderball PLC (a counter-cyclical company stock). You have collected annual prices of each of them and calculated the realized returns for 6 years. Realized returns data are included in Table 1 below. year Treasury bill FTSE100 Skyfall PLC 2% 2% -10% 1% JBond PLC 5% 7% 9% 10% 12% 7% 2014 2015 2016 2017 2018 2019 3% Thunderball PLC 16% 5% 3% -10% -16% -11% -5% 3% 9% 20% 16% 14% 1% 1% 1% 9% 14% 13% 1% a) Calculate: i. the excess return of each realized return for each security in Table 1; (2 marks) ii. the historical average excess returns and volatilities of each security, using the excess returns in part ai). Briefly comment on the results (6 marks) b) Assuming that each historical average excess return is a good proxy of the expected return of each security, using the information found in parts a), now calculate the beta of each security. Briefly interpret the results. (6 marks) c) Once you have calculated the required rate of return of each security using the formula beta x average excess return with the information in part b), indicate whether each security is undervalued or overvalued and explain why. (6 marks) You are asked to compare the following securities: a treasury bill, FTSE100 (market portfolio), JBond PLC (a utility company stock), Skyfall PLC (a high-tech company stock) and Thunderball PLC (a counter-cyclical company stock). You have collected annual prices of each of them and calculated the realized returns for 6 years. Realized returns data are included in Table 1 below. year Treasury bill FTSE100 Skyfall PLC 2% 2% -10% 1% JBond PLC 5% 7% 9% 10% 12% 7% 2014 2015 2016 2017 2018 2019 3% Thunderball PLC 16% 5% 3% -10% -16% -11% -5% 3% 9% 20% 16% 14% 1% 1% 1% 9% 14% 13% 1% a) Calculate: i. the excess return of each realized return for each security in Table 1; (2 marks) ii. the historical average excess returns and volatilities of each security, using the excess returns in part ai). Briefly comment on the results (6 marks) b) Assuming that each historical average excess return is a good proxy of the expected return of each security, using the information found in parts a), now calculate the beta of each security. Briefly interpret the results. (6 marks) c) Once you have calculated the required rate of return of each security using the formula beta x average excess return with the information in part b), indicate whether each security is undervalued or overvalued and explain why. (6 marks)
