[SOLVED] MATH GR5280 Capital Markets Investments

MATH GR5280, Capital Markets & Investments

Final Project

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):

wi ≥ 0, for ∀i ;

2.   Now, having the efficient risky portfolio {w(ˆ)i }i(1)1  for the solution for the above problem 1, you will

need to solve the problem 1 above with the following constraint on ESG:

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:

4.   Lastly, having the efficient risky portfolio {w(ˆ)i }i(1)1   for the solution for the above problem 3, you

will need to solve the problem 3 above with the following constraint on ESG:

You will need to numerically solve the above problems using the template “FinalProject AlexeiChekhlov Group0.xlsx”  and  submit  your  numerical  solutions  as  such  file,  with  filename  adjusted  with  your “FinalProject FirstnameLastname Group(your group#).xlsx”. Please, do not insert or delete any cells, keep the existing format – it is very nicely done and the graphs will allow you to “see” your solutions. The areas of cells that you will need to fill-in with your numerical solutions are as follows. The points for MM:

P2:AC3, P5:AC6, P8:AC9, P11:AC12. The curves (frontiers) for MM: C33:F113, I33:L113, O33:R153.

The  points   for  IM:  AI2:AV3,  AI5:AV6,  AI8:AV9,  AI11:AV12.  The  curves  (frontiers)  for  IM:

AM33:AP113, AS33:AV113, AY33:BB153. The grading will be done by comparing your tabulated results to  exact  solutions. The  calculations  should  be  done  on  a  Windows  computer  with  licensed Microsoft Office installed.

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:

	Group #1	Group #2	Group #3	Group #4
Stock #1	ADBE	AMZN	NVDA	QCOM
Stock #2	IBM	AAPL	CSCO	AKAM
Stock #3	SAP	CTXS	INTC	ORCL
Stock #4	BAC	JPM	GS	MSFT
Stock #5	C	BRK/A	USB	CVX
Stock #6	WFC	PGR	TD CN	XOM
Stock #7	TRV	UPS	ALL	IMO
Stock #8	LUK	FDX	PG	KO
Stock #9	ALK	JBHT	JNJ	PEP
Stock #10	HA	LSTR	CL	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.

# Group #1 Full Name Sector (Yahoo!finance)

1  ADBE         Adobe Inc.                                                                   Technology

2  IBM           International Business Machines Corporation     Technology

3  SAP            SAP SE                                                                          Technology

4  BAC           Bank of America Corporation                                   Financial Services

5  C                Citigroup Inc.                                                               Financial Services

6  WFC          Wells Fargo & Company                                            Financial Services

7  TRV           The Travelers Companies, Inc.                                Financial Services

8  LUV            Southwest Airlines Co.                                             Industrials

9  ALK            Alaska Air Group, Inc.                                                Industrials

10  HA              Hawaiian Holdings, Inc.                                             Industrials

# Group #2 Full Name Sector (Yahoo!finance)

1  AMZN       Amazon.com, Inc.                                                       Consumer Cyclical

2  AAPL         Apple Inc.                                                                   Technology

3  FFIV           F5 Networks, Inc.                                                        Technology

4  JPM           JPMorgan Chase & Co.                                               Financial Services

5  BRK/A       Berkshire Hathaway Inc.                                            Financial Services

6  PGR           The Progressive Corporation                                   Financial Services

7  UPS            United Parcel Service, Inc.                                        Industrials

8  FDX            FedEx Corporation                                                     Industrials

9  JBHT          J.B. Hunt Transport Services, Inc.                           Industrials

10  LSTR          Landstar System, Inc.                                                 Industrials

# Group #3 Full Name Sector (Yahoo!finance)

1  NVDA        NVIDIA Corporation                                                  Technology

2  CSCO         Cisco Systems, Inc.                                                    Technology

3  INTC          Intel Corporation                                                       Technology

4  GS              The Goldman Sachs Group, Inc.                               Financial Services

5  USB            U.S. Bancorp                                                                 Financial Services

6  TD CN       The Toronto-Dominion Bank                                    Financial Services

7  ALL            The Allstate Corporation                                          Financial Services

8  PG             The Procter & Gamble Company                             Consumer Defensive

9  JNJ             Johnson & Johnson                                                   Healthcare

10 CL              Colgate-Palmolive Company                                   Consumer Defensive

# Group #4 Full Name Sector (Yahoo!finance)

1  QCOM      QUALCOMM Incorporated                                       Technology

2  AKAM       Akamai Technologies, Inc.                                      Technology

3  ORCL         Oracle Corporation                                                    Technology

4  MSFT         Microsoft Corporation                                              Technology

5  CVX           Chevron Corporation                                                Energy

6  XOM          Exxon Mobil Corporation                                          Energy

7  IMO           Imperial Oil Limited                                                   Energy

8  KO             The Coca-Cola Company                                           Consumer Defensive

9  PEP            PepsiCo, Inc.                                                               Consumer Defensive

10 MCD          McDonald’s Corporation                                            Consumer Cyclical

Using this data and the template Excel spreadsheet you will need to make all the necessary calculations to produce the 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:
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 TAs have performed all these calculations for each of the group’s portfolios and will be able to compare your numbers,  specific points 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 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:

where {w(ˆ)i }i(1)1  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 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).