20191204003602interconnected_risk_calculation.xlsx20191115010218week_8_assignment2 x
Interconnected Risk Exercise — Assess contagion risk in an investment portfolio
Please read the following and see attachment for the excel
Description: You are the same risk analyst in a wealth management company who helped one of the wealth managers win the account of the Vanderbilt Family Office. Your liquidity analysis of the FO’s existing and proposed investment portfolios was favorably received and the proposed portfolio changes were made.
Now the FO’s Administrative Manager has asked you to stress test the new portfolio in a Global Markets Correction scenario, highlighting the positions which are vulnerable to contagion between more than one risk category.
Instruction: Study the price changes of the investments listed in Attachment A between 31 March 2019 and the 30 June 2019 stress test. Based on your knowledge of each type of investment’s market, credit and liquidity risk characteristics, enter your rationale for the existence and magnitude, or non-existence, of contagion risk against each position in the shaded Contagion Risk Assessment column. Then, answer the following questions, giving brief reasons for each of your answers.
1.Which fixed income position(s) show the most contagion risk in the stress test?
2.Which equity position(s) show the most contagion risk in the stress test?
3.Which alternative investment* position(s) show the most contagion risk in the stress test?
(* = Commercial Real Estate and Hedge Funds)
>VBFO new port
NEW PORTFOLIO
-Mar-19
Expected 1 2 Yield , 360/360
3 9
1,228
1 , senior unsecured, 31-Dec-2023, %, 180/360
3 9
5%
1.0 1.1 2 5% , Coco, 31-Dec-2037, 6%, 180/360
1 1.00 2.0 , 360/360
5 9
3.50% 5 , 0%, 31-Dec-2038
5 9
1.00 ,200
2.65% 2.65% 1 4 1.00 2.75% 1.0 4 1.00 1.0 2 days: up to USD 1mn 5 days: up to USD 5mn 10 days: up to USD 10mn 30 days: up to USD 20mn 4 1.00 4.50% 1.0 3.3 2 days: up to USD 1mn 5 days: up to USD 5mn 10 days: up to USD 10mn 30 days: up to USD 20mn This muni bond’s yield and liquidity in moderate amounts are typical of the sector 1.00 0%
5.0 1 1.0 1.0 1.50% 2 175,000 2.50% 5.0 2.50% 4 -15% ,000
0.25% 5.0 1.3 0.25% 15% 2 3.50% -7.5% 3,000,000 1.08 1.00 – 2.75% 1 1.25% 5,000,000 1.00 – 1.75% 1 -5% 1 day: up to USD 20mn Shorts 60 1.00 – – 1 30% 1.09 – – 1 25% 1 day: up to USD 2mn 90 120.36 – – 1 25% 1 day: up to USD 2mn 125 1.39 – 1 day: up to USD 2mn 1.00 5%
360 days up to USD 20mn
1.00 5,000,000 5.0 1.00 5,000,000 5.0 180 days up to USD 10mn Can be bought in lots of USD 5mn 5,000,000 1.00 3,000,000 1.00 180 days: up to USD 10mn 2,000,000 1.00 1.00 2,000,000 2.0 2.9 4.8 0.0 Total ERROR:#REF! 100.5% Expected Days to Liquidate or Close Out Expected Projected Fixed income Equities Hedge Funds Total ERROR:#REF! 15% diversification applied %
100.0% = Expected Days to Liquidate or Close Out Expected Projected Description Quantity Price FX US$ Value Yield 1 2 5 10 30 60 180 360 Yield Volatility Liquidity VaR Stress Liquidity Metric Notes CONTAGION RISK ASSESSMENT Fixed income 3 1.02 1.25% 3.1 1.25% 0.75% 1 3% 1 day: up to USD 10mn 2 days: up to USD 20mn Credit rating Aa2/AA 3 1.00 1.75% 1.0 4.0 1.1 1.75% 0.35% 2 0.25% 5% 1 day: up to USD 1mn 2 days: up to USD 5mn 5 days: up to USD 10mn 10 days: up to USD 20mn Credit rating A2/A 1 1.00 3.50% 4.7 3.50% 2.65% 5 0.65% -10% 10 days: up to USD 2mn 30 days: up to USD 5mn 5 2.65% 2.65% 0.55% 1 2.5% 5 days: up to USD 5mn 10 days: up to USD 15mn Credit rating Aaa/AA+ 5 1.00 2.75% 2.0 1 day: up to USD 5Mn 2 days: up to USD 20mn Credit rating A3/A- 1.00 6.25% 2.0 2.0 2 days: up to USD 1mn 5 days: up to USD 5mn 10 days: up to USD 10mn 30 days: up to USD 20mn Credit rating Baa3/BBB- 1.00 4.50% 2.0 2 days: up to USD 1mn 5 days: up to USD 5mn 10 days: up to USD 10mn 30 days: up to USD 20mn Credit rating Baa1/A- 1.00 4.50% 1.0 2 days: up to USD 1mn 5 days: up to USD 5mn 10 days: up to USD 10mn 30 days: up to USD 20mn Credit rating A- Equities 1.00 0.00% 2.0 3.0 0.5 0.00% 22% 1 4.25% -15% 1 day: up to USD 5Mn 2 days: up to USD 10mn 5 days up to USD 20 mn 1.33 1.50% 1.0 2.0 2.0 3.4 1.50% 12% 2 2.50% -7.5% 1 day: up to USD 1Mn 2 days: up to USD 2mn 5 days up to USD 10 mn 10 days: up to USD 15mn 2.50% 2.0 3.0 3.0 2.50% 34% 4 6.75% -15% 5 days: up to USD 5Mn 10 days: up to USD 10mn 1.02 0.25% 3.0 1.7 0.25% 15% 2 3.50% -7.5% 2 days: up to USD 5mn 5 days: up to USD 10mn 10 days up to USD 20mn Currencies Shorts – 0.50% 2.75% 1 1.25% -5% 1 day: up to USD 20mn – 0.20% 1.75% 1 1.30% -5% 1 day: up to USD 20mn Derivatives Shorts 1.00 – – 66% 1 8.30% 30% 1 day: up to USD 2mn 1.02 – – 28% 1 6.95% 25% 1 day: up to USD 2mn 122 – – 32% 1 5.75% 25% 1 day: up to USD 2mn 1.33 – 1 day: up to USD 2mn Commercial Real Estate 1.00 9.0 Can be sold in lots of USD 10mn 1.00 5,000,000 5.25% 5.0 180 days up to USD 10mn Can be bought in lots of USD 5mn 1.00 5,000,000 5.85% 5.0 180 days up to USD 10mn Can be bought in lots of USD 5mn Hedge Funds 3.75% 5.1 60 days: up to USD 5mn 1.00 5.80% 3.0 180 days: up to USD 10mn 1.00 4.95% 2.0 180 days: up to USD 10 mn 17.0 0.0 Total ERROR:#REF! ERROR:#REF! WAV Yield 15% diversification applied 100.0% 100.0% INVESTMENTS TO CONSIDER YIELD & LIQUIDITY ANALYSIS Fixed income Equities Hedge Funds 361.0 Total ERROR:#VALUE! ERROR:#REF! New WAVY 15% diversification applied Existing projected cumulative liquidity 1.89% 36.9% 59.7% 78.2% 86.3% 91.0% 100.0% 100.0% Applying Analytic Techniques to Business Overview The first step in creating meaningful information from raw data is to represent the data effectively in a graphical format and calculate any required statistics. The second step is interpreting and explaining those graphs and statistics in order to apply them in the practical business context. In the last assignment, you were asked to create the first part of your analytics report by developing graphs and calculating some descriptive statistics. In this assignment, you will complete your analytics report by interpreting those graphs and statistics, and explicitly connecting those interpretations to implications in the practical business context. Scenario Your supervisor has asked you to prepare a report for the quarterly company meeting. The first part of the task was to download the data and create scatterplots and histograms, and to calculate mean, median, and mode of the stock prices for your report. Now you must analyze and interpret those graphical representations of the company stocks and write the report of your findings for your supervisor. Your Role You are an analyst in the same business that you used for the last assignment. Having accessed the company data and placed it in an appropriate graphical format, you must now use descriptive statistics and analysis to develop a report to inform business decisions. Instructions After reviewing and integrating your instructor’s feedback on your previous assignment, complete the report as follows: · For each graph you created, write at least one paragraph interpreting the graph. . What does that graph represent? . What does the shape of the graph tell you about how the data have changed over time? · For each statistic you calculated, include at least one sentence explaining what the statistic represents. . What does the mean tell you? . What does it imply if the median is different from the mean? . What does the standard deviation tell you about the volatility of the data? · Write a new conclusions section in which you explain how these interpretations can be used in the company: . What are some trends about which company leaders should be aware? . How might the information you have provided be used in decision making in the company? . Explicitly connect those interpretations to implications in the practical business context. · Create a 6–8 page report containing: . One-page introduction of your chosen company that you created in your previous assignment. . Section labeled Graphical Representations of Data that includes the four graphs you created as well as your interpretations of the graphs. . Section labeled Descriptive Statistics, with the statistics you calculated as well as your interpretations of the statistics. . One-page conclusion in which you describe the potential business applications of the data and interpretations. . APA-formatted references page (remember to cite the source of your financial data). Additional Requirements · Include APA-formatted in-text citations where appropriate. · Follow the typical double-spaced analytics report format. · Make sure your written communication is free of errors that detract from the overall message. Turnitin Report Formatting Title Page Citation Outline
2
Attachment A
VANDERBILT FAMILY OFFICE INVESTMENT PORTFOLIO
–
3
1
Expected
Days to Liquidate or Close Out
Projected
Description
Quantity
Price
FX
US$ Value
Yield
5
10
30
60
180
360
Volatility
Liquidity
VaR
Stress
Liquidity Metric
Notes
CONTAGION RISK ASSESSMENT
Fixed income
Sovereign Q, EUR, 1,000,000, 31-Dec-2028, 1.
5%
102.12
1.0
3,3
4
1.
25%
3.3
1.25%
0.75%
0.
30%
3%
1 day: up to USD 10mn 2 days: up to USD 20mn
Credit rating Aa2/AA
Contagion between market, credit and/or liquidity risk: Yes/No? Briefly state the rationale for your assessment
Bank R, USD,
2,000,000
2.0
10
1.1
1.00
6,071,520
1.7
4.0
1.75%
0.35%
0.25%
1 day: up to USD 1mn 2 days: up to USD 5mn 5 days: up to USD 10mn 10 days: up to USD 20mn
Credit rating A2/A
This bond has moderate yield and liquidity in modest amounts
Bank S, USD,
5,000,000
0.97
4,850,000
6.25%
2.9
10 days: up to USD 2mn 30 days: up to USD 5mn
Credit rating Baa3/BBB-
This Coco bond has limited liquidity in modest amoounts but a very high yield
Company T, CAD, 1,500,000, 31-Dec-2021,
2.75%
0.96
1.3
5,166,367
3.50%
5.2
2.65%
0.65%
-10%
5 days: up to USD 5mn 10 days: up to USD 15mn
Credit rating Ba1/BB
+
This high yield bond has reasonable liquidity in moderate amounts
US Treasury, USD,
3,000,000
0.5
8,8
90
5.0
3.9
0.55%
0.
15%
2.5%
1 day: up to USD 5Mn 2 days: up to USD 20mn
Credit rating Aaa/AA+
This bond has good liquidity in large amounts and a moderate yield
NJ State Transit Authority, USD, 1,000,000, 15-Jun-2024, 3%, 180/360
100.47
4,018,840
3.0
2 days: up to USD 1mn 5 days: up to USD 5mn 10 days: up to USD 10mn 30 days: up to USD 20mn
Credit rating A3/A-
This muni bond has a low yield compared to others with similar liquidity
IL State GO, USD, 1,000,000, 3-Jan-2024, 5%, 180/360
1.10
4,398,400
4.50%
3.4
Credit rating Baa1/A-
This muni bond’s yield and liquidity in moderate amounts are typical of the sector
MI Financial Authority, USD, 1,000,000, 1-Jun-2022, 5%, 180/360
1.08
4,304,400
Credit rating A-
Equities
Company V, common stock, Nasdaq
175,000
39.42
6,898,500
0.0
1.9
0.00%
22%
4.25%
-15%
1 day: up to USD 5Mn 2 days: up to USD 10mn 5 days up to USD 20 mn
This stock has high growth potential and good liquidity but zero yield
Company Y, common stock, TSX
470,000
24.94
1.39
8,432,950
1.50%
6.4
12%
2.50%
-7.5%
1 day: up to USD 1Mn 2 days: up to USD 2mn 5 days up to USD 10 mn 10 days: up to USD 15mn
This stock has moderate yield and liquidity in moderate amounts
Company X, common stock, Nikkei
6,233
120.36
9,062,604
4.1
34%
6.75%
5 days: up to USD 5Mn 10 days: up to USD 10mn
This stock has a good yield and moderate liquidity in modest amounts
Company W, common stock, Euronext
125
46.30
1.09
6,311,848
2 days: up to USD 5mn 5 days: up to USD 10mn 10 days up to USD 20mn
This stock has a low yield but moderate liquidity in large amounts
Currencies
Shorts
Sell EUR, Buy USD, 1.05, 31-Dec-2019
(87,000)
0.50%
-5%
1 day: up to USD 20mn
Sell CAD, Buy USD, 1.35, 30-Jun-2019
1.38
(130,000)
0.20%
1.30%
Derivatives
Put, SPX, strike 1,800, 5-Jul-19
1,021.97
(61,318)
66%
8.30%
1 day: up to USD 2mn
Put, AEX, strike 395, 5-Jul-19
150
441.82
(72,277)
28%
6.95%
Put, Nikkei 225, strike 15,000, 5-Jul-19
72,500.00
(54,212)
32%
5.75%
Put, GSPTSE, strike 13,000, 5-Jul-19
764.54
(68,754)
Commercial Real Estate
Ownership of office tower located at 240 E Monroe in Chicago
0.30
30,000,000
9,000,000
4.7
9.0
180 days: up to USD 10mn
Can be sold in lots of USD 10mn
This CRE investment has a moderate yield but limited liquidity and represents only one portion of the commercial real estate sector
Ownership of West Hills shopping mall in Pittsburgh
0.11
45,000,000
5.25%
180 days up to USD 10mn
Can be bought in lots of USD 5mn
This CRE investment has average yield and liquidity and represents a different portion of the commercial real estate sector
Ownership of luxury apartment building in Denver
0.07
75,000,000
5.85%
This CRE investment has better than average yield and liquidity and represents a different portion of the commercial real estate sector
Hedge Funds
Z Investment Advisors Macro Fund
0.95
4,750,000
3.75%
4.8
60 days: up to USD 5mn
This hedge fund has typical liquidity but not a very high yield
Q Investment Advisors Value Fund
1.02
3,060,000
5.80%
3.1
This hedge fund has average yield and liquidity for the sector
R Investment Advisors CRE Fund
4.95%
180 days: up to USD 10 mn
This hedge fund adds CRE expsoure with average yield and liquidity for the sector
Total
100,083,294
3.17%
15.3
18.8
28.7
6.1
24.1
ERROR:#REF!
100.6
WAV Yield
15% diversification applied
Projected cumulative liquidity
15.3%
34.1%
62.8%
68.8%
71.7%
76.4%
100.5%
INVESTMENTS TO CONSIDER
YIELD & LIQUIDITY ANALYSIS
31-Mar-16
Description Quantity Price FX US$ Value Yield 1 2 5 10 30 60 180 360 Yield Volatility Liquidity VaR Stress Liquidity Metric Notes
Commercial Real Estate Total
290,837,134
1.09%
42.8
54.7
90.7
28.9
39.3
75.0
253.2
361.0
ERROR:#VALUE!
291,310,696
New WAVY
New projected cumulative liquidity
Old WAVY
14.7%
33.5%
64.6%
74.5%
88.0%
113.8%
200.7%
324.6%
Existing projected cumulative liquidity
1.89%
17.0
36.9%
59.7%
78.2%
86.3%
91.0%
100.0%
– – + – – –
=
Rationale for acceptability of liquidity risk in new portfolio
The liquidity profile of the new portfolio exceeds the family’s requirement for at least 30% of the portfolio to be liquid in 2 days.
VBFO new port stress SV
Attachment A
VANDERBILT FAMILY OFFICE INVESTMENT PORTFOLIO – NEW PORTFOLIO – STRESS TEST
Scenario:
GLOBAL MARKET CORRECTION
30-Jun-19
Sovereign Q, EUR, 1,000,000, 31-Dec-2028, 1.5%, 360/360
102.85
3,147,210
0.30%
Bank R, USD, 2,000,000, senior unsecured, 31-Dec-2023, 2.0%, 180/360
100.85
6,051,000
Bank S, USD, 5,000,000, Coco, 31-Dec-2037, 6%, 180/360
93.25
4,662,500
Credit rating Ba1/BB+
Company T, CAD, 1,500,000, 31-Dec-2021, 2.75%, 360/360
91.25
1.33
5,145,677
5.1
0.15%
US Treasury, USD, 3,000,000, 0%, 31-Dec-2038
64.00
9,600,000
7.6
NJ State Transit Authority, USD, 1,000,000, 15-Jun-2024, 3%, 180/360 4
94.00
3,760,000
(0.2)
IL State GO, USD, 1,000,000, 3-Jan-2024, 5%, 180/360 4
107.00
4,280,000
2.3
MI Financial Authority, USD, 1,000,000, 1-Jun-2022, 5%, 180/360 4
106.25
4,250,000
4.3
Company V, common stock, Nasdaq 175,000
31.54
5,518,800
Company Y, common stock, TSX 470,000
23.69
8,372,714
Company X, common stock, Nikkei 175,000
5,610
122
8,046,701
Company W, common stock, Euronext
125,000
37.04
4,722,600
Sell EUR, Buy USD, 1.05, 31-Dec-2019 3,000,000 1.09 1.00
210,000
Sell CAD, Buy USD, 1.35, 30-Jun-2019 5,000,000 1.33 1.00
(100,000)
Put, SPX, strike 1,800, 5-Jul-19 60
3,576.90
214,614
Put, AEX, strike 395, 5-Jul-19 150
1,988.19
304,193
Put, Nikkei 225, strike 15,000, 5-Jul-19 90
253,750.00
187,193
Put, GSPTSE, strike 13,000, 5-Jul-19 125
1,987.80
186,824
Ownership of office tower located at 240 E Monroe in Chicago 0.30
29,910,000
8,973,000
4.75%
180 days: up to USD 10mn 360 days up to USD 20mn
Ownership of West Hills shopping mall in Pittsburgh 0.11
44,775,000
Ownership of luxury apartment building in Denver
0.66
7,575,000
Z Investment Advisors Macro Fund 5,000,000 1.02 1.00
5,100,000
Q Investment Advisors Value Fund 3,000,000
1.01
3,030,000
R Investment Advisors CRE Fund 2,000,000
0.98
1,960,000
Total
97,623,025
3.19%
11.3
11.0
19.1
8.4
7.1
23.7
97.6
Projected cumulative liquidity
11.6%
22.8%
40.2%
59.8%
68.4%
75.7%
31-Mar-16 Expected Days to Liquidate or Close Out Expected Projected
Description Quantity Price FX US$ Value Yield 1 2 5 10 30 60 180 360 Yield Volatility Liquidity VaR Stress Liquidity Metric Notes
Commercial Real Estate Total
283,670,865
1.10%
30.8
31.2
55.2
68.0
55.9
82.1
252.2
282,668,042
New projected cumulative liquidity Old WAVY
10.9%
22.0%
41.5%
65.5%
85.3%
114.4%
203.6%
331.3%
17.0%
– – + – – – = =
Rationale for acceptability of liquidity risk in new portfolio
The liquidity profile of the new portfolio exceeds the family’s requirement for at least 30% of the portfolio to be liquid in 2 days.Order an Essay Now & Get These Features For Free:
