You are a risk analyst in a hedge fund. You have been asked to calculate the VaR of the following two fund portfolios.

Portfolio 1

• Common stock of Company Q valued at USD 1,000,000
• Common stock of company R valued at GBP 600,000
• 3-year bonds of Company S valued at USD 3,000,000
• 10-year bonds of Company T valued at SFR 2,000,000
• Currency forward Sell (GBP 500,000)/Buy USD on 31-Dec-2020
• Currency forward Sell (SFR 2,000,000)/Buy USD on 31-Dec-2020

Net position amount: $7,005,263*

*(1,000,000 + 600,000*1.5 + 3,000,000 + 2,000,000/0.95)

Average combined daily return %: 0.01%

(SD) of combined daily return %: 0.46%

Confidence interval: 99%

Time horizon: 1 day

Portfolio 2

Investment: 20,000 shares of Novartis common stock listed on NYSE, closing price on 31-Dec 2018 = $86.04

Currency forward: Sell (EUR)/Buy USD 2,500,000 on 30-Jun-2020

Investment: $5,000,000 Medtronic bond, maturity 31-Dec-2023, 2.5% semi-annual coupon, BPV $2,431.23

Net position amount: Calculate using the formula shown above

Average combined daily return %: Calculate in Attachment 3

(SD) of combined daily return % Calculate in Attachment 3

Confidence interval: 95%

Time horizon: 1 month

For question 1, insert the relevant VaR formula into the highlighted cell in the Portfolio VaR calculation spreadsheet. Using the NORM.INV function in Excel, calculate the VaR of Portfolio 1.

For question 2, use the formula applied to Portfolio 1.

For question 3, use the AVERAGE and STDEV.S functions to calculate the answers.

For question 4, insert the relevant VaR formula into the highlighted cell.

For question 5, update the VaR calculation by assuming a 50% correlation between the currency forwards. Additional details are in the worksheet.

Give brief details of how you calculated each answer.

1. What is the VaR of Portfolio 1?
2. What is the net position amount of Portfolio 2?
3. What is the average combined daily return % and of the combined daily return % of Portfolio 2?
4. What is the VaR of Portfolio 2?
5. What is the VaR after updating the correlations?