The first Portfolio Management workshop in India for MBA Students

We will conduct a financial workshop focused on applying the Markowitz Portfolio Theory and the Capital Asset Pricing Model (CAPM). The workshop will consist of several steps to comprehensively analyze a portfolio of 100 stocks traded on the National Stock Exchange (NSE) over the past decade.

The time frame of study- is 2013–2023.

Total stocks included: 100 from NSE

Excel skills: Vlookup, Index, Match, and MMULT

Laptop compulsory

Stepwise Learning Module

Step 1: Import historical stock price data from NSE for the selected 100 stocks on a weekly basis.

Step 2: Calculate the weekly returns for each of these stocks.

Step 3: Extending our analysis over the past 10 years to gather sufficient data for our portfolio assessment.

Step 4: Calculate the unsystematic risk for all 100 stocks.

Step 5: Calculate the risk-return trade-off.

Step 6: Visualizing the results through graphical representations

Step 7: Create an optimal portfolio consisting of 16 carefully selected stocks.

Step 8: Calculate the variance and covariances of these 16 stocks.

Step 9: Comparing the risk-reward ratio

Step 10: Compare Optimal vs. all other portfolio

Step 11: Draw a capital market line (CML).

Step 12: Draw an Efficient Frontier line (EFL).

Here's a streamlined way to calculate stock price volatility directly in one cell:

1. Input your stock prices in column A (let’s say from A2 to A11).

2. Enter this formula in any cell where you want the volatility:

excel

=STDEV.P((A3:A11/A2:A10)-1)*SQRT(252)

This formula computes the daily returns and calculates the annualized volatility in one step.

same way i want to calculate annulaized daily returns in one step.

Sure thing! To calculate annualized daily returns in one step in Excel, follow these steps:

1. Input your stock prices in column A (let’s say from A2 to A11).

2. Enter this formula in any cell where you want the annualized return:

excel

=((PRODUCT(1 + (A3:A11/A2:A10 - 1))^(252/COUNTA(A2:A11)) - 1))

This formula calculates the daily returns, computes the product of the returns, annualizes them, and gives you the annualized daily return in one cell.

https://stackoverflow.com/questions/37639561/how-do-i-dynamically-change-input-cell-in-a-formula-based-on-the-value-in-anothe

For this kind of dynamic reference, you need the INDIRECT function. It takes two arguments INDIRECT(reference,style)

reference, a text string containing a cell, a range of cells text or a named range

and style a boolean that if omitted or TRUE, indicates that reference is A1 style, and when FALSE, the reference is using the R1C1 style.

so in your case you can use

INDIRECT("A"&C1)