1) Select two stocks/ companies of your and also one index (to which they belong like Nifty 50 or BSE Sensex)

2) Please obtain the daily closing stock price of these two companies (and also obtain the closing index) from Bloomberg or Prowess database for the past one year, i.e. from 1 April 2022 to 31 March 2023. You can get the data only from these two sources and no other data source. You need to mention the data source and the date of obtaining the data in your excel sheet.

3) From the price series you should compute the daily returns either simple returns or log returns. Please choose one method and be consistent, i.e. apply same method for both the stocks (and the index) for the one year entire period. Please include a brief explanation as to your choice of method (in your report), i.e. why you chose to use simple returns or log returns as the case may be.

5) Next sheet should have the closing stock price and index data that you have downloaded

6) First set of calculations (do this in separate tab in Excel):

You will need to calculate the following:

- Average daily return, Variance, and Standard deviation of Stock #1 over the period of a year

- Average daily return, Variance, and Standard deviation of Stock #2 over the period of a year

- Covariance and Correlation across the two stocks (over the same time period)

7) Assume that you are being given Rs. 100,000 in your portfolio (as of the starting date) and you need to split that 60:40 between the two stocks. You may choose which stock should get 60% and which stock should get 40%.

8) In another sheet in excel do the following set of calculations:

- Expected return, Variance, and Standard deviation of Overall portfolio (with 60:40 split)

9)Compute the portfolio risk and portfolio return for various combinations of the two stocks that you have selected. Go from 0% in stock 1 and 100% in stock 2 all the way to 100% in stock 1 and 0% in stock 2 in increments of either 1% or 5% or 10%. You should now have two columns of data, one column which has the portfolio risk and the second column which has the portfolio return. Draw a scatter plot using this data. Excel requires that the first column in your dataset should represent the X axis, which in our case is portfolio risk and the second column should represent the Y axis which in our case is the portfolio return. The chart that you obtain would be the efficient frontier.

10) In this sheet you would also need to plot the CML. For this identify the risk free rate from the internet and mark it as a point on the Y axis on the same scatter plot. You can do that by adding a new data series consisting of only one point and label it as risk free rate. Then insert a line into this chart that is anchored at the risk free point and is tangential to the efficient frontier. This line represents the CML.

11) You may identify the point of tangency by using excel solver. The steps to do this are as outlined below. Remember the CML represent combinations of risk free asset and the market portfolio. Initially and quite arbitrarily assume that your market portfolio comprises of 50% in stock 1 and 50% in stock 2. Then calculate the risk and return of the market portfolio. Then compute the Sharpe ratio for this market portfolio in a cell in this sheet. Then use Excel solver to maximize the Sharpe ratio by changing the weight of the stocks in the portfolio. This will give you the point of tangency, which is the market portfolio.

12) calculate the Beta of each of the stocks with respect to the market, where the market is proxied by the index. Graph the regression on a chart. Using the calculated Beta and the risk free rate calculate the required return for each of the stocks based on CAPM model.

13), please write a short report in WORD (max two A4 size pages) outlining your key analysis, findings, learnings, as well any decisions you made such as methodology, etc. It should be a short report and to the point.