Students need to conduct analysis of Power Assets Holdings Limited by using the CAPM model and financial ratios.

Part 1: CAPM (12 marks)

Calculate the beta of Power Assets Holdings Limited based on the daily return of the share prices of this company from 1/1/2021 to 31/12/2021 (excluding unavailable data for example, public holiday within the period). (6 marks)

Calculate the expected return of the share by using CAPM (3 marks).

Comment on the difference between the actual return and expected return of the share prices of the company within that period (3 marks)

Here are tips from professors:

To calculate beta in Excel:

1. Download historical security prices for the asset whose beta you want to measure.
2. Download historical security prices for the comparison benchmark.
3. Calculate the percent change period to period for both the asset and the benchmark. If using daily data, its each day
4. Find the variance of the asset using =VAR.S(all the percent changes of the asset).
5. Find the covariance of asset to the benchmark using =COVARIANCE.S(all the percent changes of the asset and all the percent changes of the benchmark).

More tips (Risk-Free Rate):

To approximate your risk-free rate, you can use the historical average US treasury bond rate or other government bond rates.

Steps:

1. Download historical bond prices
2. Calculate the percentage changes in the prices (i.e., returns)
3. Get the average.

How to calculate these by excel? Thank you.