Experiential Learning Assignment: Building a
Portfolio and Calculating the Efficient Frontier
Dr Ashok Patil
Objective
The objective of this assignment is to provide practical experience in constructing a
portfolio of stocks, calculating risk and return, and determining the Efficient Frontier
using real-world data. Students will gain hands-on experience in portfolio management
by analyzing stocks and index data, calculating returns, risk metrics, and visualizing the
Efficient Frontier.
Assignment Overview
You are required to download historical stock data for 5 companies of your choice and
the relevant market index data (e.g., SENSEX, NIFTY, etc.) to build a portfolio. After
constructing the portfolio, you will calculate the risk-return characteristics and plot the
Efficient Frontier.
Steps to Complete the Assignment
1. Company and Index Data Selection
Select 5 publicly traded companies from different industries to ensure diversi-
fication in the portfolio.
Choose companies that are listed on major stock exchanges.
Provide a brief explanation of why you selected these companies (e.g., industry
diversity, growth potential, etc.).
Choose an index to represent the broader market. You can use:
– SENSEX for market.
– Large Cap Index for large-cap stocks.
– Alternatively, you can select any other market index relevant to your geo-
graphical region or industry focus.
1
Experiential Learning Assignment Financial Modeling
2. Data Download
Download historical data for each of the 5 companies and the index (daily, weekly,
or monthly data). The data should include:
– Stock prices (Open, Close, High, Low) or adjusted closing prices.
– Date range: At least 3-5 years of historical data.
– You can use sources like:
* Yahoo Finance ([Link]
* Google Finance ([Link]
* Bloomberg ([Link]
Data fields to collect:
– Date: The date of the data entry.
– Adjusted Closing Price: Adjusted for dividends and stock splits.
3. Calculate Historical Returns
For each stock and the market index, calculate the historical returns.
Current Close
Daily Return = ln (1)
Previous Close
You may choose to work with monthly returns.
Calculate the average return and standard deviation (volatility) for each stock and
the market index over the selected period.
4. Build the Portfolio
Portfolio Weights: Assign different weights to each of the 5 companies in your
portfolio. You may experiment with different weight combinations (e.g., equal
weights or custom weights based on your analysis).
Portfolio Return: Calculate the expected return of the portfolio by taking the
weighted average of the individual stock returns.
Portfolio Return = w1 R1 + w2 R2 + · · · + wn Rn (2)
where w1 , w2 , . . . , wn are the portfolio weights and R1 , R2 , . . . , Rn are the respective
stock returns.
Portfolio Volatility (Risk): Calculate the standard deviation (volatility) of the
portfolio return.
q
σp = w12 σ12 + w22 σ22 + 2w1 w2 Cov(R1 , R2 ) + . . . (3)
where σ1 , σ2 are the standard deviations of the stock returns, and Cov(R1 , R2 ) is
the covariance between the returns of the two stocks.
2
Experiential Learning Assignment Financial Modeling
5. Calculate the Efficient Frontier
Create Multiple Portfolio Combinations: For each set of portfolio weights,
calculate the expected return and standard deviation (risk). You can experiment
with different combinations of weights (e.g., 0% to 100% for each stock, varying the
weights systematically).
Plot the Efficient Frontier: Plot the risk (standard deviation) against the ex-
pected return for each combination of portfolio weights. The Efficient Frontier is the
curve that represents the optimal risk-return trade-off for a given set of portfolios.
6. Optimal Portfolio
Find the optimal portfolio based on your analysis. The optimal portfolio lies on
the Efficient Frontier and maximizes returns for a given level of risk.
If applicable, you can calculate the Sharpe Ratio for each portfolio combination
and identify the portfolio with the highest Sharpe Ratio, which represents the most
efficient portfolio.
7. Risk-Return Analysis and Interpretation
Analyze and interpret the results of your portfolio:
– Which combination of weights provides the best risk-return trade-off?
– How does the portfolio perform compared to the individual stocks and the
index?
– What is the role of diversification in reducing portfolio risk?
– Discuss the impact of market volatility on the portfolio.
8. Submit the Report
Report Contents:
– Introduction: Brief overview of the companies selected, the index chosen, and
the rationale for portfolio construction.
– Data and Assumptions: Description of the data used (source, time period,
frequency).
– Portfolio Construction: Detailed explanation of how the portfolio was built,
including the weights assigned to each stock.
– Risk and Return Calculations: Show all calculations for returns, risk, and
portfolio metrics.
– Efficient Frontier: Present the plot of the Efficient Frontier, explaining the
significance of the curve.
– Analysis and Interpretation: Discuss your findings, the optimal portfolio, and
any insights gained from the analysis.
Submit: Upload the report (Word/PDF) and the Excel/CSV file containing the
raw data and portfolio calculations.
3
Experiential Learning Assignment Financial Modeling
Grading Criteria
Data Selection & Relevance: Appropriateness of company selection and index
choice.
Calculations: Accuracy and correctness of return and risk calculations.
Efficient Frontier: Clear presentation of the Efficient Frontier with proper analysis.
Interpretation & Insight: Depth of analysis and the ability to draw meaningful
conclusions from the results.
Clarity & Organization: The clarity of your written report and the structure of
your analysis.
Deadline
28 February 2025 by 3 PM. Submission on Moodle.