Jun 14 2009
Project Portfolio Risk Analysis Using Excel, Minitab and Crystal Ball
Successful investors focus primarily on risk reduction. Many tools are available on the market today to assist investors with fundamental and technical analysis of their equity investment. However, to successfully understand risk, portfolio analysis is essential. This article explores a method of better understanding the risk of your investment portfolio using various tools—including Excel, Minitab and Crystal Ball.
It is a well documented fact that by diversifying an investment portfolio the total portfolio risk is reduced, particularly the unsystematic risk or unique risk. However, the investor is cautioned that beyond an optimal point, the advantage of adding additional shares dissipates the risk reduction advantage.
Figure 1 below depicts a complete portfolio risk. Analysis which is discussed below.
Once shares (the investment focus of this article but can be substituted for any investment type) have been selected, and basic information as to price and volume has been noted, the next step is to calculate the Portfolio Beta.
Portfolio Beta
The portfolio Beta is obtained from the individual share Beta. Beta measure the sensitivity of a share price to the movement of the market as a whole. For example, a share with a Beta of 1.5 will move, on average , 15% for each 10% move of the market.
The individual share Beta’s are determined by performing regression analysis between the monthly returns of the ALSI index as the independent variable and the monthly returns of the individual shares. Minitab has powerful regression functions to assist with this process.
These Betas are then weighted by the proportion of the portfolio and summed to produce the portfolio beta.
Coupled with good knowledge of the market and skills at forecasting, the Beta can be used to reduce risk by shifting your portfolios Beta. Increasing it when you predict a market rise and vice versa. Thus, you will be altering the market risk of your portfolio.
R² and Standard Deviation
Again using the monthly returns of the individual shares, the R² and Standard Deviation (Std Dev) for each share can be obtained using Minitab. In fact performing the regression in the previous step will provide you with this information.
The R² value indicates the percentage market or systematic risk inherent in the share and ultimately the portfolio. Systematic risk is risk that can not be diversified away. It works hand in hand with Beta risk. Technically the R² explains the “best fitting” line that fits the return data. It tells us what proportion of the movement of a share is due to market movement.
Standard Deviation
STD DEV measures the standard amount by which the value in a data set differs from the mean (average).
In finance, the STD DEV of expected returns is a common measure of investment risk. The higher the variance, measure by STD DEV, the higher the risk of a share or portfolio.
The Market Index Risk
The market index risk is the STD DEV of the market index risk. In our examples it is equal to 4.5%. The magnitude of the portfolio risk is =
Portfolio Beta X Market Index Risk.
The Portfolios Unique Risk
When a diversified portfolio across shares are not perfectly correlated, the portfolio risk is less than the weighted sum of the risk of the individual shares. The risk that disappears in the portfolio creation process is called the shares unsystematic risk or unique risk.
The unique risk of a portfolio is calculates by first computing the unique risk of each share—(1-R²) x STD DEV. The result is then multiplied by the proportion of each share to the portfolio. The square root of the sum of these results constitute the portfolio’s unique risk.
By diversifying the portfolio, the unique risk was reduced and is significantly lower than any of the individual shares.
The Portfolios Total Risk
The portfolio’s total risk is calculated by the following formula:
(Total Risk)² = (Market Risk)² + (unique Risk)²
Portfolio Return
The benchmark return of a a portfolio can be caluclated using the following formula:
Benchmark returns = Beta x Market Index Return + (1-Beta) x Risk Free Rate.
Using Crystal Ball (CB)
When using CB the first step is to define distribution return assumptions in Crystal Ball (green cells). The expected return for each share is multiplied by its proportion in the portfolio. These are summed to produce the portfolio return (blue cell)
The simulation is run and the distribution graph below is produced.
This portfolio has a 77.15% chance for producing a positive return.
The sensitivity analysis, shown to the right, ranks the magnitude of the various shares returns and impact on this portfolio.
Successful I&I (Improve & Innovate),
Piero
