Question 1: [Data] In the attached Excel file, you will find 10 years of historical market data from US 1 month yield, S&P 500 Index levels, and a stock price of a company. Ignoring dividends, please compute the following in an Excel file: MarketDataSeries.xlsx
a. Please define the Sharpe Ratio
b. Annualized Sharpe ratios of both S&P 500 Index and the company stock using the full history.
c. What is the beta of the company stock to the S&P 500 Index?
d.What is the average annualized excess return of the company stock? How does geometric and arithmetic averaging affect the resulting excess return?
Question 2: Risk Management
Consider a small hypothetical international corporate bond portfolio worth $10 million USD in par notional. All the bonds are USD denominated issued by foreign corporates. The bond portfolio is selected from a bond universe. A spreadsheet contains the bond universe (along with the bond portfolio positions) in a single table. The table can be found on the computer in the file named SampleBondIndexPortfolio.xls. Each table row represents one bond in the bond universe. The first three sample rows are shown below with column headers.
Pos Notional represents the position in USD notional if the portfolio contains that bond. Mkt Cap is the total market value of the outstanding issuance of the bond in USD. Duration is expressed in year. YTM is the yield to maturity expressed in percentage. Stripped Spread is the credit spread derived from the YTM over the comparable yield of a US Treasury strip curve expressed in basis points. Spread Duration is the duration of the bond with respect to change in stripped spread. Price is quoted in percentage of par. In the example below, the portfolio contains a position of $1,000,000 notional in the 2nd bond.
|
Instrument
|
Pos Notional
|
Mkt Cap
|
Duration
|
YTM
|
Stripped Spread
|
Spread Duration
|
Price
|
|
AE DP World 6.85% due 37
|
|
1627657990
|
11.173489
|
7.632172
|
523.2097
|
10.530189
|
90.25
|
|
AE Nat Bank of Abu Dhabi 4 1/4% due 15
|
1000000
|
776964037.5
|
3.072632
|
3.015728
|
256.852
|
3.067358
|
102.85
|
|
AE Taqa Abu Dhabi 5 7/8% due 16
|
|
1105385420
|
4.306768
|
3.420617
|
260.1812
|
4.271708
|
110
|
|
BR BFF Intl Ltd 7 1/4% due 20
|
|
258733807.9
|
6.206501
|
5.553705
|
409.0943
|
6.067273
|
110
|
Please create a spreadsheet on the PC using the file SampleBondPortfolio.xls to answer the following questions.
a. What is the market value of the portfolio?
b. What is the portfolio's average duration and average yield to maturity on a position marketweighted basis?
c. If all credit spreads widen by 50 basis points, what is the dollar P&L impact on the portfolio?
d. If you could sell any two bond positions and buy substitute another two from the same bond universe, could you suggest a "better" portfolio? Justify your portfolio decisions carefully.
Question 3: A database contains the following 3 tables. SecMaster, Holdings, Prices.
Copy the three data tables below into an Excel spreadsheet and calculate the answers showing work.
1. What is the market value of each portfolio on each date Jan 1 and march 1? The results should have 3 columns: Date, Portfolio, MarketValue.
2. Report all Bonds in SecMaster which are not held in any portfolio on for each date in Holdings. i.e. list all bonds not in any portfolio as of Jan 1. List all bonds not in any portfolio as of March 1. Express the results in the form Date, ID, Name sorted by Date.
Table 1 SecMaster
|
ID
|
Name
|
Broker
|
|
123
|
Bond1
|
G
|
|
A21
|
Bond2
|
M
|
|
XYZ
|
Bond3
|
G
|
|
134
|
Bond4
|
M
|
|
345
|
Bond5
|
F
|
|
124
|
Bond6
|
G
|
Table 2 Holdings
|
Date
|
ID
|
Quantity
|
Portfolio
|
|
1/1/2000
|
123
|
1000
|
A
|
|
1/1/2000
|
A21
|
-500
|
A
|
|
3/1/2000
|
123
|
600
|
A
|
|
3/1/2000
|
XYZ
|
700
|
A
|
|
1/1/2000
|
A21
|
500
|
B
|
|
1/1/2000
|
124
|
3140
|
B
|
|
3/1/2000
|
345
|
1000
|
B
|
Table 3 prices
|
Date
|
Name
|
Price
|
|
1/1/2000
|
Bond1
|
100
|
|
1/1/2000
|
Bond2
|
103
|
|
1/1/2000
|
Bond3
|
98
|
|
1/1/2000
|
Bond6
|
97
|
|
3/1/2000
|
Bond1
|
101
|
|
3/1/2000
|
Bond2
|
102
|
|
3/1/2000
|
Bond3
|
101
|
|
3/1/2000
|
Bond4
|
99
|
|
3/1/2000
|
Bond5
|
88
|
Question 4: (Essay on Risk Management)
A. Please provide definitions of the following 4 types of risk management:
Market Risk, Operational Risk, Credit Risk, Liquidity Risk
B. Under what circumstances do these type of risks overlap? In particular, give an example of when operational risk becomes credit risk. Give an example where liquidity risk becomes market risk.
C. Is historical VAR useful to measure the risk of a run on a bank? Give clear arguments to support your view?
Information related to above question is enclosed below:
Attachment:- Files.rar