Part One: - First Question (8 Marks) Given the data of the below selected 10 stocks...

Transcribed Image Text:

Part One: - First Question (8 Marks) Given the data of the below selected 10 stocks from EGX, please answer the following questions in Excel format. Stock 2019 2020 2021 2022 2023 Stock Price 13.67 12.67 13.79 18.38 29.98 Telecom Egypt Dividends 0.26 0.26 0.79 1.05 1.31 Stock Price 0.51 0.63 2.78 2.50 2.59 Raya Holding Dividends 0.41 0.20 0.05 0.06 0.04 Stock Price 10.98 6.66 7.46 8.741 13.98 TMG Holding Dividends 0.09 0.09 0.15 0.18 0.00 Stock Price 39.24 34.79 31.88 32.24 60.24 CIB Egypt Dividends 1.05 1.31 0.00 1.42 1.05 Stock Price 7.93 5.48 9.44 12.45 34.22 MOPCO Dividends 2.36 2.63 3.68 10.50 15.75 Stock Price 77.20 55.09 44.60 35.73 35.11 EPICIO ELSWEDY Dividends 4.20 4.27 3.10 3.15 2.10 Stock Price 14.96 8.67 9.23 8.58 21.68 ELECTRIC Dividends 0.84 0.84 0.42 0.00 0.42 Stock Price 7.09 5.95 6.52 6.10 10.28 Obour Land Dividends 0.26 0.53 0.63 0.68 1.00 Stock Price: 7.43 6.36 8.24 8.16 15.94 Oriental Weavers Dividends 1.58 0.68 1.05 1.05 0.63 Stock Price 8.89 8.79 13.69 11.52 20.76 B Investments Dividends 0.53 0.42 1.31 0.53 0.14. EGX Index 2019 2020 2021 2022 2023 2472 2521 3511 3243 5799 Q1. Calculate the Average Rate of Return for each stock and the market Q2. Calculate the Variance and Standard Deviation for each stock and the market Q3. Calculate the Covariance between each pair Q4. Calculate the Correlation between each pair Q5. Calculate the beta for each stock and the market Q6. Given Risk Free Rate (Rf)=15% Calculate the required rate of return for each stock using the CAPM(Capital Assets Pricing Model) Q7. Calculate the Optimal Weight for each stock Q8. Calculate the Portfolio Rate of Return and Portfolio Risk Part One: - First Question (8 Marks) Given the data of the below selected 10 stocks from EGX, please answer the following questions in Excel format. Stock 2019 2020 2021 2022 2023 Stock Price 13.67 12.67 13.79 18.38 29.98 Telecom Egypt Dividends 0.26 0.26 0.79 1.05 1.31 Stock Price 0.51 0.63 2.78 2.50 2.59 Raya Holding Dividends 0.41 0.20 0.05 0.06 0.04 Stock Price 10.98 6.66 7.46 8.741 13.98 TMG Holding Dividends 0.09 0.09 0.15 0.18 0.00 Stock Price 39.24 34.79 31.88 32.24 60.24 CIB Egypt Dividends 1.05 1.31 0.00 1.42 1.05 Stock Price 7.93 5.48 9.44 12.45 34.22 MOPCO Dividends 2.36 2.63 3.68 10.50 15.75 Stock Price 77.20 55.09 44.60 35.73 35.11 EPICIO ELSWEDY Dividends 4.20 4.27 3.10 3.15 2.10 Stock Price 14.96 8.67 9.23 8.58 21.68 ELECTRIC Dividends 0.84 0.84 0.42 0.00 0.42 Stock Price 7.09 5.95 6.52 6.10 10.28 Obour Land Dividends 0.26 0.53 0.63 0.68 1.00 Stock Price: 7.43 6.36 8.24 8.16 15.94 Oriental Weavers Dividends 1.58 0.68 1.05 1.05 0.63 Stock Price 8.89 8.79 13.69 11.52 20.76 B Investments Dividends 0.53 0.42 1.31 0.53 0.14. EGX Index 2019 2020 2021 2022 2023 2472 2521 3511 3243 5799 Q1. Calculate the Average Rate of Return for each stock and the market Q2. Calculate the Variance and Standard Deviation for each stock and the market Q3. Calculate the Covariance between each pair Q4. Calculate the Correlation between each pair Q5. Calculate the beta for each stock and the market Q6. Given Risk Free Rate (Rf)=15% Calculate the required rate of return for each stock using the CAPM(Capital Assets Pricing Model) Q7. Calculate the Optimal Weight for each stock Q8. Calculate the Portfolio Rate of Return and Portfolio Risk