I am building a VaR model (in Excel using the variance-covariance method) for a portfolio containing stocks, bonds, and an ETF. Additionally, there is a put option (out of the money) that is there to hedge the downside risk of my position in the ETF. The ETF tracks the MSCI index.

My question is, how do I implement the put option in my VaR model?

  • 2
    $\begingroup$ The value of the put option at maturity $T$ is $\mathrm max(K-S_T,0)$. So you simulate the ETF ($S$) and see under your the worst scenarios what your put option payoff is. $\endgroup$
    – Frido
    Commented May 20, 2023 at 12:44

1 Answer 1


You have a matrix that for all your market factors contains their volatilities and pairwise correlations, probably calculated from historcal market data. You assume that they are normally distributed. You calculate VaR to some confidence interval, often 99%.

As long as all the instruments in your portfolio are linear within your chosen confidence interval, you can use matrix multiplication, which is easy in Excel: the vector of your factor sensitivities times the matrix times the transpose(vector of factor sensitivities) times normsinv(confidence interval) times sqrt(time horizon) and few other things.

In your example, if the option is so far our of the money that it is worthless on your confidence interval, you just assume that it's worthless and effectively exclude it from the VaR calculation. Likewise, if the option were very far in the money that you could assume that you hold the underlying, then you could indeed assume that you hold the underlying. But, conversely, if the option is out of the money now, but would be in the money if the underlying moved, e.g., 2 historical standard deviations, in other words, the value of the option is materially non-linear within your confidence interval, then you can't use matrix multiplication anymore. You have to use a Monte-Carlo simulation (MC). MC is described well, for example, in Value at Risk: The New Benchmark for Managing Financial Risk by Philippe Jorion, section 12 or Market Risk Analysis Volume IV: Value at Risk Models by Carol Alexander, section IV.1.9.3. For MC, you will need to estimate the change in the value of option under thousands of risk scenarions. This is likely to be computationally intensive, so you may want to look into shortcuts where you reprice the option under a few scenarios, and linearly iterpolate between them. Or, instead of a MC and covariance matrix, you can use "Historical VaR'.

While it's possible to implement MC or Historical VaR in Excel, it's really painful, and I urge you to use another tool more suitable for such calculations.

I also urge you to look at expected shortfall (ES), rather than VaR.

Another approach you might try instead of MC is not to assume that a small number of parameters summarizes the dynamics of your historical market data, not to use any covrance matrix, but rather to calculate how the value of your portfolio would change if the market data changed like it did in your historial data; and use the worst cases for VaR or ES.

You should also consider the impact of additional historical or hypothetical market stress scenarios.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.