In our previous post on portfolio risk attribution, we presented the following formula for decomposing the risk contribution of each asset in a portfolio:

Eq6This formula shows that the individual risk contributions of each asset are the product of the asset’s weight, volatility, and correlation to the rest of the portfolio.  It gives us an intuitive way of understanding how different assets affect the risk profile of a portfolio.1

To better illustrate some of these concepts, I put together a risk budgeting spreadsheet that calculates the portfolio allocations required to target a user-specified risk profile.  The spreadsheet can be accessed here.

In the spreadsheet, the green cells are intended inputs, and the blue cells are the important calculated values. Any other cells are auxiliary to the calculations.  There are three columns for inputting one year of asset prices.  From these prices, the returns, volatilities, and correlations are calculated and used to calculated the risk attribution using the above formula.  The asset weights that achieved a specified risk profile can be calculated using Solver, which is preset and accessed by clicking on the “Solve” button.

The sheet is preloaded with data from SPY, TLT and EEM and a risk profile of 30%, 40% and 30%, respectively.  Solving this yields allocations of 37% SPY, 44.6% TLT and 18.4% EEM.  Feel free to explore the calculations; however, any changes to formulas may make the automatic solving feature inoperable – you have been warned!  If the “Solve” button is not working, you may have to install Solver as an Excel Add-in or add it as a reference in Visual Basic.

An interesting exercise is to alter the risk budget and see how greatly the allocations change.  This is analogous to exploring the curvature of the contours shown in the “triangle graphs” toward the end of the previous risk attribution post.  This sheet is also useful for calculating a three asset risk parity allocation.  As always, we welcome any feedback or questions.

1. This can be used for historical analysis and is only applicable going forward if the volatilities and correlations do not change.

Nathan is a Portfolio Manager at Newfound Research, a quantitative asset manager offering a suite of separately managed accounts and mutual funds. At Newfound, Nathan is responsible for investment research, strategy development, and supporting the portfolio management team. Prior to joining Newfound, he was a chemical engineer at URS, a global engineering firm in the oil, natural gas, and biofuels industry where he was responsible for process simulation development, project economic analysis, and the creation of in-house software. Nathan holds a Master of Science in Computational Finance from Carnegie Mellon University and graduated summa cum laude from Case Western Reserve University with a Bachelor of Science in Chemical Engineering and a minor in Mathematics.