Start date: 12/9/2023
Due date: 12/23/2023
MATH GR5280, Capital Markets & Investments
Final Project
Note: All files and information related to the final project are located in the various folders starting with “Final Project” prefix on CourseWorks.
The aim of this Final Project is to practically implement the ideas from the course, specifically from Chapters 7 and 8 of [BKM13]. Using Bloomberg, you will be given a recent 20 years of recent historical daily total return data for ten stocks, which belong in groups to three-four different sectors (according to Yahoo!finance), one (S&P 500) equity index and a proxy for risk-free rate (1-month Fed Funds rate). Additionally, you will be given contemporaneous ESG [ESG3] scores data also from Bloomberg for all of your companies with detailed explanations to them. 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 four cases of problems:
1. This optimization 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):
3. This 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:
wi 0,fori;
2. Now, having the efficient risky portfolio wˆ 10 for the solution for the above problem 1, you will
need to solve the problem 1 above with the following constraint on ESG:
ESGw0.9 ESGwˆ;
4. Lastly, having the efficient risky portfolio wˆ 10 for the solution for the above problem 3, you i i1
will need to solve the problem 3 above with the following constraint on ESG:
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. The grading will be done by comparing your tabulated results to exact solutions.
ESGw0.9 ESGwˆ.
Code Help
Start date: 12/9/2023
Due date: 12/23/2023
MATH GR5280, Capital Markets & Investments
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 three-four sectors of stocks with stocks in each group belonging to one (Yahoo!finance) sector and an instrument representing risk-free rate, 1-month annual Fed Funds rate (ticker symbol “FEDL01”). Note that stocks in each group are completely different. Therefore, each group will have its own results and conclusions.
Below, please, find the table of stock ticker symbols (aka, tickers) for each group to work with:
Stock #1 Stock #2 Stock #3 Stock #4 Stock #5 Stock #6 Stock #7 Stock #8 Stock #9 Stock #10
ADBE IBM SAP BAC C WFC TRV LUK ALK HA
AMZN AAPL CTXS JPM BRK/A PGR UPS FDX JBHT LSTR
NVDA CSCO INTC GS USB TD CN ALL PG JNJ CL
QCOM AKAM ORCL MSFT CVX XOM IMO KO PEP MCD
Below, please, find the table which shows the details for each of the stocks and which stocks belong to the same sector in each group.
Start date: 12/9/2023
Due date: 12/23/2023
MATH GR5280, Capital Markets & Investments
# Group #1
Adobe Inc.
International Business Machines Corporation SAP SE
Bank of America Corporation
Citigroup Inc.
Wells Fargo & Company
The Travelers Companies, Inc.
Southwest Airlines Co.
Alaska Air Group, Inc.
Hawaiian Holdings, Inc.
Amazon.com, Inc.
Apple Inc.
F5 Networks, Inc.
JPMorgan Chase & Co. Berkshire Hathaway Inc.
The Progressive Corporation United Parcel Service, Inc. FedEx Corporation
J.B. Hunt Transport Services, Inc. Landstar System, Inc.
NVIDIA Corporation
Cisco Systems, Inc.
Intel Corporation
The Goldman Sachs Group, Inc. U.S. Bancorp TheToronto-DominionBank TheAllstateCorporation TheProcter&GambleCompany Johnson & Johnson Colgate-Palmolive Company
Sector (Yahoo!finance)
Technology Technology Technology Financial Services Financial Services Financial Services Financial Services Industrials Industrials Industrials
Sector (Yahoo!finance)
Consumer Cyclical Technology Technology Financial Services Financial Services Financial Services Industrials Industrials Industrials Industrials
Sector (Yahoo!finance)
Technology Technology Technology Financial Services Financial Services FinancialServices FinancialServices ConsumerDefensive Healthcare Consumer Defensive
Sector (Yahoo!finance)
Technology Technology Technology Technology
Consumer Defensive Consumer Defensive Consumer Cyclical
QUALCOMM Incorporated Akamai Technologies, Inc. Oracle Corporation Microsoft Corporation Chevron Corporation Exxon Mobil Corporation Imperial Oil Limited
The Coca-Cola Company PepsiCo, Inc. McDonald’s Corporation
程序代写 CS代考 加QQ: 749389476
Start date: 12/9/2023
Due date: 12/23/2023
Minimal Risk Portfolio: and
Efficient Risky Portfolio:
w min ; r
MATH GR5280, Capital Markets & Investments
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-4 above). The Minimal Return Frontier and the Efficient Frontier together are forming the Minimal Risk or Variance Frontier – it is just a matter of reformulating the optimization problem, as follows:
Minimal Risk or Variance Frontier:
wmin r
Two unique points that you need to find on the Efficient Frontier are of special interest:
Minimal Return Frontier:
Efficient Frontier:
subject to:rw const
rwmin r
subject to : w const
subject to : w const
rw max . rr
For each of the markets in the above table we have uploaded adjusted most recent data containing the
Adjusted Closing daily price.
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 TAs 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
Start date: 12/9/2023
Due date: 12/23/2023
1. wi 0,fori;
2. wi 0, for i , and
MATH GR5280, Capital Markets & Investments
you have not done so already. The TAs will be helping you to address any issues related to Solver during the TAs 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 TAs 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 2/28/2003;
end date 3/6/2023.
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 four optimization constraints:
E S G w 0.9 iiii
E S G wˆ iiii
4. w2and ESGw0.9
ESGwˆ, iiii
where wˆ 10 in each case corresponds to the efficient risky portfolio solution of the corresponding non
ESG-constrained problem.
As we have already mentioned, your task is to produce the following objects on the Permissible Portfolios Region in the numerical (and the template spreadsheet does it in the graphical for you) form:
Minimal Risk or Variance Frontier (a curve), range for portfolio returns: from -10% to 50% with step of 0.5%;
Global Minimal Risk or Variance Portfolio (a point);
Maximal Sharpe Ratio or Efficient Risky Portfolio (a point);
Maximal Return or Efficient Frontier (a curve), range for portfolio standard deviation: from 10%
to 50% with step of 0.5%;
Capital Allocation Line or CAL (a straight line);
Minimal Return or Inefficient Frontier (a curve), range for portfolio standard deviation: from 10%
to 50% with step of 0.5%.
The curves above must be produced in tabular form (Excel), using the template provided, preserving the formats in the template, with which comparison to exact solution will be made for grading, using specifically the above ranges. If a numerical solution cannot be found, just leave the corresponding cell
Programming Help, Add QQ: 749389476
Start date: 12/9/2023
Due date: 12/23/2023
MATH GR5280, Capital Markets & Investments
empty. The points above should also be tabulated. All the tabulation should be done similar to example provided by the Instructor (see the file “Final Project Group0.xlsx” provided).
Do not hesitate to ask TAs, Lecturer 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 work and not as a one-night effort.
Final Project presentations will take place on December 23rd, 2023 at 7:00 PM EST.
Your spreadsheet should be named following the following convention which is similar to the homework naming convention: “FinalProject-1 Alexei Chekhlov.xlsx”. Here “1” is the number of your Final project group, and instead of “Alexei Chekhlov” should be your name in the following format: “FirstName Last Name”.
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 four sets of additional optimization constraints.
3. Calculate both of the two key frontier points (maximal Sharpe Ratio and Minimal Risk), and three frontiers: the Efficient Frontier, the Minimal Return Frontier, and the Minimal Variance Frontier.
4. Prepare the Excel file with your results using the template provided, preserving the formatting, the ranges, etc. Name it using the convention “FinalProject-1 Alexei Chekhlov.xlsx” and submit
through the portal on CourseWorks.
Remember, that you can always ask both the Lecturer and the TAs any questions.
References:
[BKM13] Z. Bodie, A. Kane, A. J. Marcus, “Investments”, Thirteenth Edition, McGraw Hill, 2024.
[CFA3] “Certificate in ESG Investing Curriculum”. CFA Institute. Edition 3. CFA Society of the UK, 2021.