I have been searching online for a few days regarding how to calculate portfolio VaR for a portfolio consisting of leveraged products - but so far, I have not been able to come up with anything remotely useful and practical (i.e. so that I can implement it in a spreadsheet for example).

I am trading custom leveraged products, and my PnL movements are based on the following two criteria:

  1. The gearing with respective to a point movement in the market (At the point at which the transaction is created, I get to choose the gearing - for example, I can choose to risk 100 cents for every point move in the underlying market).

  2. The margin gearing which relates to how much margin the broker requires in order to establish a position (actually this may be irrelevant in risk calculation, as margining appears to be ignored in futures VaR calculation).

My questions are:

  1. How can I build a VaR model that takes into account the fact that each trade (i.e. transaction) may have a different gearing?

  2. What would be the steps required to build a simple Excel model to help me calculate a VaR for my portfolio?

  • $\begingroup$ Have you considered Monte Carlo? Using MC you could also model non-linearities. $\endgroup$
    – Bob Jansen
    Nov 20, 2012 at 19:46
  • $\begingroup$ If you're trying to do it in Excel, you're already asking for trouble. As Bob implies you can't typically do this analytically, and Excel is hard for non-gurus to do Monte Carlo in. $\endgroup$
    – Brian B
    Nov 20, 2012 at 20:09
  • $\begingroup$ @BobJansen: My MC fu is a bit rusty. Could you outline the main steps involved if I decide to go the MC route?. I may implement the functionality in a C++ shared library, which I would then use via Excel. $\endgroup$ Nov 20, 2012 at 23:53
  • $\begingroup$ I will, later today $\endgroup$
    – Bob Jansen
    Nov 21, 2012 at 8:27

1 Answer 1


First of all you need a model to generate future returns, I assume you already have this.

Since its just a model, there will be an unexplained component in the predictions made for every period $t$ and for every asset $i$. Let $\varepsilon_{t, i}$ denote this random innovation and $\mathrm{E}[r_{t, i}] = f(\varepsilon_{t, i})$ the expected asset return as a function of the innovation. In a Monte Carlo you pseudo-randomly generate the innovations, apply $f$ to obtain a random sample from your return distribution, in pseudo code, for one period:

r = zeros(1, N)
for i=1:N
    eps = draw_from_distribution()
    r(i) = f(eps)

with N the number of simulations. This is all there is to it, to find the 5% VaR just take the 5% quantile from r.

An advantage of Monte Carlo simulation is that it is easy to take an asset return model of a high frequency and apply it to a VaR of a lower frequency. In that case you repeat the code above for every period and calculate the cumulative return from the one period returns.

There are a number of ways to do a draw_from_distribution. You can simply use a distribution like the normal or the student-t or perform Filtered Historical Simulation.

  • $\begingroup$ Thanks for the answer. I could do with a little more detail however. In particular, I am not sure which model to use to generate future returns - a suggestion would be helpful. Additionally, I am not sure how to implement f(x). Please clarify. $\endgroup$ Nov 22, 2012 at 9:17
  • $\begingroup$ The model depends on the assets you want to calculate the VaR for. For instance if you have stocks and want to use a simple model you can look at this question: quant.stackexchange.com/questions/4589/… The answer as it is now just describes the general solution, you would have to provide more details about your specific situation for hints on implementing $f(x)$. $\endgroup$
    – Bob Jansen
    Nov 22, 2012 at 9:37
  • $\begingroup$ Thanks for the link. The assets I am trading are equity, equity indices, commodities and forex. I suspect that for equity (and equity indices), I can model returns using a GBM model?. Regarding implementing f(x), I'm not sure I understand what you mean - could you please clarify what further information you require? $\endgroup$ Nov 22, 2012 at 10:29
  • 1
    $\begingroup$ Implementation of $f(x)$ requires a model for the returns. You can look at the question mentioned for an idea but a choice for a model requires careful consideration, I (and others) can't judge which model to use. $\endgroup$
    – Bob Jansen
    Nov 27, 2012 at 20:04

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.