You are a risk analyst in an insurance company. You have been asked to calculate the VaR of each of three separate stock investments.

Investment A

Common stock: General Motors

Position value: $500,000

Confidence Interval: 95%

Time period: 1 month

Investment B

Common stock: Proctor & Gamble

Position value: $2,500,000

Confidence Interval: 99%

Time period: 3 months

Investment C

Common stock: Exxon Mobil

Position value: $10,000,000

Average absolute daily return %: Calculate from Attachment 1

(SD) of absolute daily return % Calculate from Attachment 1

Confidence Interval: 99.9%

Time period: 5 days

For question 1, insert the relevant VaR criteria and formula into the highlighted cells in the VaR calculation of stocks spreadsheet. Using the NORM.INV function in Excel, calculate the VaR of investment A.

For question 2, insert the relevant VaR formulae into the highlighted cells in the VaR calculation of stocks spreadsheet. Using the NORM.INV function in Excel, calculate the VaR of investment B.

For question 3, use the AVERAGE and STDEV.S functions in Excel to calculate the average daily return and of the daily return of Investment C in the VaR calculation of stocks spreadsheet. Then insert the relevant VaR formulae into the highlighted cells and, using the NORM.INV function in Excel, calculate the VaR of investment C.

For question 4, calculate the VaR % of position value of investments A, B and C. Then answer the question.

For question 5, backtest the VaR calculation using a 21 day time horizon. Instructions can be found at the tab called Stock 1 Backtest.

Give brief details of how you calculated each answer.

1. What is the VaR of Investment A?
2. What is the VaR of Investment B?
3. What are the average daily return %, of the daily return % and the VaR of Investment C?
4. Which of the three VaR numbers has the highest VaR % of its position value? Explain why its VaR % of position value is higher than the other two.
5. Did the VaR model backtest successfully?

The stocks spreadsheets (even transfer into images) are too big to update, can someone tell me what i should do to update the spreadsheets?