promotion image of download ymail app
Promoted

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?

Update:

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?

3 Answers

Relevance
  • 1 decade ago
    Favorite 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,

    The steps:

    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

    • Commenter avatarLogin to reply the answers
  • 3 years ago

    Value At Risk Excel

    • Commenter avatarLogin to reply the answers
  • Anonymous
    5 years ago

    Hope this helps!

    • Commenter avatarLogin to reply the answers
Still have questions? Get your answers by asking now.