How Safe is My Portfolio?
Creating a "Value At Risk" Calculator using Crystal Xcelsius
By David Harper
*Note: This article makes several references to the Excel spreadsheet embedded in the Crystal Xcelsius model for this example. You can download the complete spreadsheet, HERE .
A successful investment strategy should include the evaluation of potential risks to your portfolio. Knowing what you stand to lose--in real time, not just when you create your portfolio--can help guide your future investment strategies and ensure you don’t overextend or misallocate your hard-earned dollars. Financial analysts call this concept of knowing what you stand to lose, your “value at risk” (VAR). Using Crystal Xcelsius, we can develop an interactive visual model that simplifies this complex “what-if” analysis and allows us to test multiple risk scenarios at the push of a button.
Analyzing VAR starts with a normal, or bell-shaped, distribution curve. The normal distribution curve portrays all the possible future outcomes of your investment, where the most likely outcomes are near the middle. Since we are dealing only with “loss” scenarios, our example Crystal Xcelsius model focuses on the left-hand side of the curve – that is, the cases where the outcome is less than the original investment.
This VAR scenario assumes our portfolio has begun with a value of $100. An important question about this portfolio is, “If things go badly, how much can I lose?” Strictly speaking, I could lose the whole thing. But, let’s assume we are asking about the next 30 days; i.e., our time horizon is one month. In this case, even if a total loss is theoretically possible, practically speaking it is highly unlikely. We want a realistic picture of the risk. That’s where the VAR’s “confidence level” comes into play. We want to determine the worst loss with either a 95% or 99% confidence. Put another way, what is our worst loss in either 95 cases out of 100, or alternatively, 99 cases out of 100. So, instead of asking “what’s my worst possible theoretical loss?” the question is: “realistically, how much can I lose if conditions remain more or less normal?” That’s the job of VAR.
Graphically, the answer to the VAR question is found in the left-tail of the distribution. You can see that greater portfolio losses (i.e. lower portfolio values) have less likelihood of occurring. In this model, you begin by providing four inputs: Time Horizon, Expected Return, Confidence, and Standard Deviation. The graph reflects the VAR calculation, based on the four parameters you enter. The “value at risk” is where the green bars switch to red bars (because the red area is only 5% or 1% of the area under the curve, depending on your selection of a 95% or 99% confidence level).
How is this done? The first step is to assemble the data inside Excel. I’ve used a single Combination Chart component in my spreadsheet to display the calculation. As shown here, Column H is labeled “delta.” This is a histogram of the normal distribution and it feeds all three plotted series. The line series plots directly from column H. To the right of this column, an IF() function parses the series into two columns. With this technique, you can break a single series into two or more sub-series (all the same data). The only purpose is to assign different colors to different segments of the series inside our Crystal Xcelsius model. For example: we simply want to paint the “left-tail” red and the rest of the curve green.
The VAR model is dependant upon the normal distribution curve. If you are interested in how the normal curve is calculated, you can look at the calculation in Column G. Basically, Excel does the job for us with its built-in function called NORMDIST(). For a complete explanation of how the value at risk calculation works, please read the online “Introduction to Value at Risk” tutorial, located Here.
I’ve also added a mini-quiz to the model which asks the user to input a guess. Using the default setup, the correct VAR answer happens to be $17.03 (in other words, under the input assumptions, we are 95% confident that our worst case scenario is a $17 loss from $100 to $83). If you enter $17, the chart displays “correct!”
Crystal Xcelsius makes the deployment of these learning interactions very easy. You simply need three cells: an input cell, a calculation cell and an evaluation cell. In this chart, an INPUT text component “pushes” the user input to a designated empty cell in the embedded spreadsheet. Next, the model performs a subtraction: the user’s answer is correct if the difference between the input and the correct answer is 0 (note: it performs two tests, in case the user inputs $83 to indicate a $17 loss). Finally, an IF() function evaluates the difference (i.e., correct answer minus user’s guess), and decides the appropriate word to display in a TEXT LABEL component.
The final element in this Crystal Xcelsius model is a feature that allows the user to toggle the actual calculation. I prefer charts that hide complexity until the user asks for it. The answers are displayed with three GRID components; each GRID component is activated with the dynamic visibility feature. The contents of the grid are developed with string concatenation, which is fully supported by Crystal Xcelsius. So, if we look at the beginning of the first line (from cell L8), it looks like this:
=" = "&TEXT(F9,"#%")
The TEXT() function formats a numeric like 0.3 into its string equivalent of “30%.” Once you’ve done this translation, you can concatenate string elements with an ampersand (&).
Conclusion
By visualizing this complex financial concept with Crystal Xcelsius, we are able to apply the valuable teachings of “value at risk” analysis much more effectively. Using this interactive model, you’ll be able to quickly evaluate the future success of your portfolio under any number of scenarios, helping you make more intelligence investment decisions in a shorter period of time.
David Harper is the Principal of Investor Alternatives, LLC, a firm that specializes in investment research, software sector coverage, and derivatives valuation. He is the Editor-in-Chief of Investopedia Advisor, a newsletter devoted to the early recognition of public companies that are likely to be lead future market. He publishes the Bionic Turtle Study Notes, courseware for learning advanced risk management concepts. He is a Charted Financial Analyst (CFA) and Financial Risk Manager (FRM).