FINC 3330 Quant Project

Due: Sunday, July 05, 2020, 11:59 P.M.

Consolidate all excel spreadsheets from part1 in a single Excel le. Please make a cover page (name and

ID) in Sheet1 in your EXCEL le. To submit this project electronically, use Blackboard. DO NOT send me

it via email. Please submit your project in a single EXCEL le. ONLY an EXCEL FILE will be accepted.

You have write up with your own words. Do not copy from others’ work. In the last page, you have to show

all your references for your project in the last sheet in your Excel.

Part 1 Analysis of Economic Indicators

Instruction: You have been retained by the SHB investment group to provide a fundamental analysis

report to the president of the company, Seungho Baek. You are asked to examine the value of economic

indicators as below.

1. TED spread: The TED spread is an indicator of perceived credit risk in the general economy. An

increase in the TED spread is a sign that lenders believe the risk of default on interbank loans (also

known as counterparty risk) is increasing.

2. Credit spread: A credit spread is the dierence in yield between a U.S. T- bond and a debt security

with the same maturity but of lesser quality. Widening credit spreads indicate growing concern about

the ability of corporate (and other private) borrowers to service their debt while narrowing credit

spreads indicate improving private creditworthiness.

3. Term spread: Term spreads, also known as interest rate spreads, represent the dierence between the

long-term interest rates and short-term interest rates on debt instruments such as bonds Widening

term spreads indicate boom economy in the future whereas narrowing credit spreads indicate economic

recession in the future.

4. Term Structure of Yields: The term structure of interest rates is the relationship between interest rates

or bond yields and dierent terms or maturities. When graphed, the term structure of interest rates

is known as a yield curve, and it plays a central role in an economy. 1

5. Cumulative returns of S&P market index: A cumulative return is the aggregate amount an investment

has gained or lost over time, independent of the period of time involved. Upward trend of cumulative

returns generally indicates bull stock market while downward trend indicates bear market.

In order to generate these economic indicators, you need to use two data resources to collect economic

variables such as Federal Reserve Economic Data (FRED) 2 and Yahoo! Finance. The interest rates necessary

for computing spreads is located in FRED. You are able to obtain the historical stock index prices from Yahoo!

Finance. To locate the historical s&p 500 index price, please follow the below steps.

Enter ^GSPC in the search bar for Quote \Lookup”.

1Note that the term structure re ects expectations of market participants about future changes in interest rates and their

assessment of monetary policy conditions. The yield curve represents the changes in interests rates associated with a particular

security based on length of time until maturity. Unlike other metrics, the yield curve is not produced by a single entity or

government. Instead, it is set by measuring the feel of the market at the time, often referring to investor knowledge to help

create the baseline. The direction of the yield curve is considered a solid indicator regarding the current direction of an economy.

2https://fred.stlouisfed.org/

1

Click Historical Data tab.

Set \Time Period” Jan. 2001 – Dec. 2019.

Set \Frequency Monthly”.

Click Apply and click Download.

You must write a report including your ndings, all relevant information and computations, and provide

evidence. For this project, you must download monthly historical data from Jan. 2001 to Dec. 2019.

Problem 1. Investigate important economic risk events since the year of 2000 and summarize them.

Problem 2. TED spread is dened as the dierence between 3 month LIBOR (loan) rate – 3 month T-bill

rate.3 Compute monthly TED spreads from Jan. 2000 to Dec. 2019 and plot the TED spreads

over time.

Problem 3. Let us dene a credit spread as the dierence between US coporate bond yields and 10 year

Treasury rate. For this exercise, use Bank of America Merrill Lynch US Corporate AAA

Eective Yield as a corporate bond rate and 10-Year Treasury Constant Maturity Rate as

Treasury rate.4 Compute monthly credit spreads from Jan. 2000 to Dec. 2019 and plot the

credit spreads.

Problem 4. In practice, a term spread is dened as 10 year Treasury rate minus 1 year Treasury rate.5

Calculate monthly term spreads from Jan. 2000 to Dec. 2019 and plot the term spreads.

Problem 5. Examine time series plots from problem 2, 3, and 4 and suggest your ndings. Suggest your

ndings on how these indicators responded against systemic shocks that you answered in

problem 1.

Problem 6. Examine yield curve shapes using daily yield curve rates provided by U.S. department of

treasury6.

a) Report treasure yield rates (1 month, 3 month, 6 month, 1 year, 2 year, 3 year, 5 year, 7

year, 10 year, 20 year, 30 year) on Jan. 02, 2014, Jan. 02, 2015, Jan. 04, 2016, Jan. 03,

2017, Jan. 02, 2018, Jan. 02, 2019, and Jan. 02, 2020.

b) Compute each term spreads for seven term structures.

c) Plot seven yield curves that show the relation between yields and maturities.

d) Provide your ndings thoroughly from b) and c).

Problem 7. Compute monthly S&P 500 index returns. To calculate monthly returns using S&500 index

price, you need to use formula as Rt = PtPt1

Pt1

where t represents time (In our case, t refers to

month; Pt represents the S&P index price at time t. For example, if you would like to compute

a return on Feb., 2000, you may be able to specify an equation as RFeb;2000 = PFeb;2000PJan;2000

PJan;2000

Problem 8. Using monthly returns that you obtained from the previous problem, compute monthly cu-

mulative returns. To compute P monthly cumulative returns, use the formula as CRt = Rt + T

j=1 Rtj where CRt represents a cumulative return at time t.

Problem 9. Plot monthly cumulative returns and examine how cumulative return series are aected by

economic crisis. Also indicate bear or bull stages in the US stock market since the year of

2000.

3TED spread =3 month loan rate – 3 month T-bill rate

4BofA Merrill Lynch US Corporate AAA Eective Yield – 10-Year Treasury Constant Maturity Rate

510-Year Treasury Constant Maturity Rate – 1-Year Treasury Constant Maturity Rate

6https://www.treasury.gov/resource-center/data-chart-center/interest-rates/pages/textview.aspx?data=yield

2

Part 2 Portfolio Return and Risk

Instruction: You are a portfolio investment analyst at Goldman Sachs. Your investment unit manages two

equity portfolios – one portfolio, named P1, consists of two stock assets (Apple (AAPL) and Microsoft(Ticker:

MSFT)) and the other portfolio, named P2, consists of ve stocks (Disney(Ticker:DIS), Boeing(Ticker:BA),

Amazon(Ticker:AMZN), Tesla(Ticker:TSLA), Net ix (Ticker:NFLX)). Now you are asked to compute two

portfolio returns and risk measures. To do this, rst download monthly stock prices from Dec.2009 to Dec.

2019 from Yahoo! Finance and compute monthly stock returns from Jan.2010 to Dec. 2019.7

Problem 1. Compute the respective average, standard deviation, and covariance of monthly stock returns.8

Problem 2. Make two covariance matrices using two portfolio components. Note that you have to make a

completed form of a matrix as below.9

P1 =

2

AAPL = AAPL;AAPL AAPL;MSFT = MSFT;AAPL

MSFT;AAPL = AAPL;MSFT 2M

SFT = MSFT;MSFT

(1)

P2 =

0

[email protected]

2D

IS DIS;BA DIS;AMZN DIS;TLSA DIS;NFLX

BA;DIS 2B

A BA;AMZN BA;TLSA BA;NFLX

AMZN;DIS AMZN;BA 2

AMZN AMZN;TLSA AMZN;NFLX

2T

LSA;DIS TLSA;BA TLSA;AMZN 2T

LSA TLSA;;NFLX

2N

FLX;DIS NFLX;BA NFLX;AMZN NFLX;TLSA 2N

FLX

1

CCCCA

(2)

Problem 3. Using the obtained statistics from problem 1, calculate an equal weighted portfolio return and

portfolio variance for the rst portfolio using the below equations.

E(RP1 ) = wAAPLrAAPL + wMSFT rMSFT (3)

2P

1 = w2

AAPL2

AAPL + w2M

SFT 2M

SFT + 2wAAPLwMSFT AAPL;MSFT (4)

Problem 4. Using the obtained statistics from problem 1, calculate an equal weighted portfolio return and

portfolio variance for the second portfolio using the below equations.

E(RP2 ) = wDIS rDIS + wBArBA + wAMZN rAMZN (5)

+ wTLSArTLSA + wNFLXrNFLX

2P

2 = w2D

IS2D

IS + w2B

A2B

A + w2

AMZN2

AMZN + w2T

LSA2T

LSA + w2N

FLX2N

FLX (6)

+ 2wDISwBADIS;BA + 2wDISwAMZNDIS;AMZN + 2wDISwTLSADIS;TLSA

+ 2wDISwNFLXDIS;NFLX + 2wBAwAMZNBA;AMZN + 2wBAwTLSABA;TLSA

+ 2wBAwNFLXBA;NFLX + 2wAMZNwTLSAAMZN;TLSA + 2wAMZNwNFLXAMZN;NFLX

+ 2wTLSAwNFLXTLSA;NFLX

7Rt =

PtPt1

Pt1

where Rt represents a stock return at time t, Pt is a stock price at time t.

8Use STDEV.P in Excel, not STDEV.S. Note that the formula for standard deviation based a sample is given by 2S

ample =

PN

i=1

(X X

)2

N1 , while the formula for standard deviation based on a population is written as 2P

opulation =

PN

i=1

(X X

)2

N .

9Use Data Analysis Toolpak to compute a covariance matrix.

3

Problem 5. Using a matrix multiplication (i.e. MMULT in Excel.), compute two portfolio returns and

portfolio variances.10

E(RP ) = w rT (7)

2P

= w wT (8)

Problem 6. With the rst portfolio, create a table that shows the benet of diversication using Data

Table in Excel. (Note that the table shows portfolio returns and portfolio standard deviation

with respect to scenarios of weights on AAPL.)

Problem 7. Using the table obtained from problem 6, Plot expected returns against portfolio risk (standard

deviations) displaying ecient portfolios.

Problem 8. Compute 99%-VaR and ES for the rst and second portfolio components and explain these

values.

V aR(99%) = E(RP ) Z99%P = E(RP ) 1(99%)P (9)

ES(99%) = E(RP ) P

(1(1 0:99)

1 0:99

(10)

where () is the normal density function, 1() is the inverse cumulative normal density

function.

Note for Excel:

To compute Z99% (i.e.1(0:99) ), use NORM.S.INV (0.99)

To compute (1(10:99)

10:99 , use NORM.S.DIST(NORM.S.INV (0.01),FALSE)/0.01

10Let w be a weight matrix that has N elements, w = (w1;w2; ::::;wn), and let r be a return matrix with N elements,

r = (r1; r2; ::::; rn). Let be a N by N covariance matrix, =

0

[email protected]

1;1 1;2 : : : 1;N

2;1 2;2 : : : 2;N

…

…

: : :

…

N;1 N;2 : : : N;N

1

CCCA

4

Part 3 Minimum Variance and Tangent Portfolios

Instruction: You have two customers that have dierent tastes of risk. The rst customer is a risk

averter who nd the way of minimizing investment risk. The other customer is a kind of person who taking

aordable risk that makes a risk adjusted return. Your boss asks you to nd optimal weights of investments

satisfying their risk-preferences.

Problem 1. Using the rst portfolio, nd out optimal weights that minimizes the portfolio standard devi-

ation (Minimum Variance Portfolio).

min

w

2P

= w wT

subject to w rT = E(RP )

w 10 = 1:

(11)

Problem 2. Using the rst and second portfolio, nd out optimal weights that maximizing the portfolio

standard deviation (Tangent Portfolio).

max

w

E(RP )

P

=

w rT

(w wT)1=2

subject to w 10 = 1:

(12)

Problem 3. Compute 99% – portfolio VaRs (i.e. percentage VaR) for the respective minimum variance

portfolios and tangent portfolios and explain these values.

5

Part 4 Portfolio Evaluations and Systemic Risk

Instruction: From the previous parts, you have created three portfolios with two stocks (equal weighted

portfolio, minimum-variance portfolio, tangent portfolio). Now you are about to evaluating the performance

of three portfolios.

Problem 1. Download monthly stock prices for the period from Dec. 2019 to May 2020 using Yahoo!

Finance and calculate monthly stock returns from Jan.2020 to May 2020.

Problem 2. Using the obtained weights (equal weights and two sets of optimal weights) from part 1 and

part 2, compute three monthly portfolio returns for each portfolio (P1 and P2) over the period

from Jan.2020 to May 2020. Let denote EWP as an equal weighted portfolio, MVP as an

minimum variance portfolio, and TP as a tangent portfolio.

Problem 3. Compute the respective averages, and standard deviations for EWP, MVP, TP based P1.

Problem 4. Compute the respective averages, and standard deviations for EWP, MVP, TP based P2.

Problem 5. Compute the respective Sharpe Ratio (SR) for the six portfolios using the below equation.

SR =

E(RP )

P

(13)

Problem 6. Generate three cumulative returns and plot three cumulative returns based on P1.

Problem 7. Generate three cumulative returns and plot three cumulative returns based on P2.

Problem 8. Let us examine the validity of 99%-VaR and ES.

(a) How many monthly equal weighted portfolio returns are beyond 99% VaR and ES from

Problem 8 in Part 1.

(b) How many monthly minimum variance portfolio returns are beyond 99% VaR and ES

from Problem 3 in Part 2.

(c) How many monthly tangent portfolio returns are beyond 99% VaR and ES from Problem

3 in Part 2.

Problem 9. Interpret all the results from problem 3 to problem 7. Also suggest your ndings and your

opinion about each portfolio performance during the period between Jan. 2020 to May 2020.

6

**What Students Are Saying About Us**

..........
Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐ "Honestly, I was afraid to send my paper to you, but you proved you are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"

.......... Customer ID: 11***| Rating: ⭐⭐⭐⭐⭐

"This company is the best there is. They saved me so many times, I cannot even keep count. Now I recommend it to all my friends, and none of them have complained about it. The writers here are excellent."