PAFBAC-301018, Intensive Course on Investments
Start date: 3/21/2025 Due date: 4/4/2025
Final Project
Note: All files and information related to the final project are located in the various folders starting with “Final Project” prefix on the system.
The aim of this Final Project is to practically implement the ideas from the course, specifically from Chapters 7 and 8. You will be given a recent 20 years of historical daily total return data for 21 stocks, which belong in groups to 4-5 different industry sectors, one (S&P 500) equity index (a total of 22 risky assets) and a proxy for risk-free rate (1-month Fed Funds rate). In order to reduce the non-Gaussian effects, you will need to aggregate the daily data to the monthly observations, and based on those monthly observations, you will need to calculate all proper optimization inputs for the full Markowitz Model (“MM”), alongside the Index Model (“IM”). Using these optimization inputs for MM and IM you will need to find the regions of permissible portfolios (efficient frontier, minimal risk portfolio, optimal portfolio, and minimal return portfolios frontier) for the following five cases of the additional constraints:
1. This additional optimization constraint is designed to simulate the Regulation T by FINRA (https://www.finra.org/rules-guidance/key-topics/margin-accounts), which allows broker-dealers to allow their customers to have positions, 50% or more of which are funded by the customer’s account equity:
2. This additional optimization constraint is designed to simulate some arbitrary “box” constraints on weights, which may be provided by the client:
3. A “free” problem, without any additional optimization constraints, to illustrate how the area of permissible portfolios in general and the efficient frontier in particular look like if you have no constraints;
4. This additional optimization constraint is designed to simulate the typical limitations existing in the U.S. mutual fund industry: a U.S. open-ended mutual fund is not allowed to have any short positions, for details see the Investment Company Act of 1940, Section 12(a)(3) (https://www.law.cornell.edu/uscode/text/15/80a-12):
5. Lastly, we would like to see if the inclusion of the broad index into our portfolio has positive or negative effect, for that we would like to consider an additional optimization constraint:
You will need to present the results in both the tabular and graphical form. with the objective to make inferences and comparisons between the sets of constraints for each optimization problem and between the MM and IM models in general. You will need to explain your observations making the connections to theory studied in the lectures.
Again, you will be given 20 years of daily data of total returns for the S&P 500 index (ticker symbol “SPX”), and for ten stocks (ticker symbols see the table below) such that there are 4-5 groups of stocks with stocks in each group belonging to one industry sector and an instrument representing risk-free rate, 1-month annual Fed Funds rate (ticker symbol “FEDL01”). Note that stocks in each of the groups are different. Therefore, each groups will have its own results and conclusions.
Below, please, find the table of instruments’ ticker symbols (a.k.a. tickers) for each group to work with:
Below, please, find the table which shows the details for each of the stocks and which stocks belong to the same industry sector in each group.
Using this data you will need to prepare an Excel spreadsheet that makes all the necessary calculations to plot a Permissible Portfolios Region, which combines the Efficient Frontier, the Minimal Risk or Variance Frontier, and the Minimal Return Frontier for a given set of constraints (1-5 above). The Minimal Return Frontier and the Efficient Frontier together are forming the Minimal Risk or Variance Frontier – it is just a matter of re-formulating the optimization problem, as follows:
Minimal Risk or Variance Frontier:
Minimal Return Frontier:
Efficient Frontier:
Two unique points that you need to find on the Efficient Frontier are of special interest:
Minimal Risk Portfolio:
and
Efficient Risky Portfolio:
This Final Project in an open-book which means that you can and should use the Instructor’s handouts and the corresponding Chapter copy reading material provided by the Instructor, as well as any additional materials provided to you. Instructor and Mentor have performed all these calculations for each of the group’s portfolios and will be able to compare your numbers, specific points and graphs to theirs. If your spreadsheet calculations are done correctly, you and we should be able to match the results with sufficient accuracy.
The main tool that we would like you to use to solve the optimization problems for each point on the Minimal Risk or Variance Frontier is the Excel Solver. Please, try to learn how to use it on your own, if you have not done so already. The Mentor will be helping you to address any issues related to Solver during the Mentor sessions. To calculate large numbers of multiple points on any of the required frontiers, you will need to use the Excel Solver Table, which the Mentor will teach you how to install and use. Both Excel Solver and Excel Solver Table will also be covered in lectures with illustrations which are very similar to your Final Project.
For your calculations, you need to use the full available historical data range:
start date 9/17/2004;
end date 9/20/2024.
As it was mentioned above, you will need to calculate the solutions to two optimizations covered in lectures:
The full Markowitz Model (MM);
The Index Model (IM).
As we have described this in detail above, each of these optimization problems MM and IM you will need to implements and solve with the following additional optimization constraints:
As we have already mentioned, your task is to produce the Permissible Portfolios Region:
Minimal Risk or Variance Frontier (a curve);
Minimal Risk Portfolio (a point);
Maximal Sharpe Ratio or Efficient Risky Portfolio (a point);
Efficient Frontier (a curve);
Capital Allocation Line or CAL (a straight line);
Minimal Return Frontier (a curve).
You will need to analyze all your results with the purpose of comparison of different constraints for each optimization problem (MM and IM), and the two optimization problem solutions between each other with same constraints.
You are expected to write the details of your comparisons analysis and your explanations as to why certain results are similar and certain other results are not in the PowerPoint presentation and present your work using one or several presenters during an up-to 30-minute presentation time allocated for each group.
Do not hesitate to ask Mentor, Lecturer, or TAs any questions related to this.
Good luck!
You are given two weeks to complete the Final Project and to prepare the presentations. We encourage you not to delay starting the work as workload is meant for several days of team work and not as a onenight, single person effort.
Final Project presentations will take place on April 4th, 2025 at 8:30 PM EST.
To re-iterate, in this Final Project you will need to achieve the following goals:
1. Get familiar with the markets allocated to your group, download the data, and review all the necessary information from Bloomberg slides.
2. Prepare the data for optimization problem solution (aggregate it from daily to monthly frequency), calculate all the required inputs for each of the optimization problems MM and IM, and for each of the five sets of additional optimization constraints.
3. Calculate both of the two key frontier points (maximal Sharpe Ratio and Minimal Risk), and two frontiers: the Efficient Frontier and the Minimal Return Frontier.
4. Prepare the PowerPoint presentation where you show your results, formulas and conclusions with all the details of your work. Please, aim for an up-to 30-minute presentation.