Value at Risk with Excel?
Hi, with a set of data, on foreign currency, how to I go about caluculating the Value At Risk (VAR) of the data. I understand only the theory part of VAR, but am not sure how to apply the techniques. Anyone is kind enough to provide me with a step by step analysis?
I have the historical data for a currency, say EUR/USD for the past 2 months, with that, how do I go about doing it?
- 1 decade agoFavorite Answer
1. Find a set of return data from a set of price/rate data: = P2/P1 - 1 or Ln(P2) - Ln(P1)
2. Calculate mean = AVERAGE(return data)
3. Calculate stdev = STDEV(return data)
4. Calculate VaR (99% confidence level) = mean - 2.33*stdev, should be negative.
5. today value = latest price/rate data
6. tomorrow worst case = today value* (1 + VaR)
note: daily data of one asset
If a portfolio of assets,
1. Collect historical daily data for asset A prices and calculate the daily return.
2. Collect historical daily date for asset B prices and calculate the daily return.
3. Generate covariance matrix for asset A and B.
4. Calculate portfolio variance and standard deviation. In Excel, it can be done using MMULT(MMULT(TRANSPOSE(value),covariancematrix),value)
5. Calculate VaR at 99% confidence level as 2.326 multiply by standard deviation.
6. Tomorrow's worst value = Today's value - VaR
- Anonymous5 years ago
Hope this helps!Source(s): risk excel: https://bitly.im/c19/value-at-risk-with-excel