This article is about an Excel model for calculating portfolio variance. When it comes to calculating portfolio variance with just two assets, life is simple. But consider a situation when there are 10, 15, maybe hundreds of assets. This brief article is a practical demonstration of how portfolio variance can be modeled in Excel - the underlying math, and an actual spreadsheet for your playing pleasure!
This formula is very useful in forming an intuitive understanding of how correlation affects risk, and examining various concepts relating to portfolio construction. This formula is not really scalable to real life situations where a portfolio may consist of tens or hundreds of securities.
What we really need for that is matrices, and Excel. This tutorial looks at how portfolio risk calculations can be modeled within Excel. Yet this is useful should you wish to see how this really would work in practice, or wish to test some additional ideas, for example, the impact of changing correlations on portfolio volatility.
The variance of a portfolio of correlated assets can be written as W T vW, where W is a column vector ie a matrix with a single column containing the weights of different assets in the portfolio. So for two assets, the combined variance of the portfolio can be written as follows in matrix notation:. In practice we rarely have the covariance matrix.
Standard Deviation and Variance of a Portfolio
What we generally get is the correlation matrix, which gives us the correlation between any two of the assets in the portfolio in the form of a matrix.
Therefore if we know the correlation matrix between assets, we can calculate the covariance matrix as follows:. We can substitute this expression for the covariance matrix in 1 above to get the portfolio variance.
See the calculations in action — here is a sample spreadsheet with 6 assets for which the standard deviations and correlations are known. We can calculate the portfolio volatility for any set of weights, and also calculate the Sharpe Ratio. We can look at the effect of changing different variables on risk and return, and even calculate the efficient portfolio I leave it to you as an exercise.
If possible, you should try constructing this spreadsheet yourself. It will clarify many concepts in a way that cannot be done from a mere reading of text. Not a member?Portfolio Standard Deviation refers to the volatility of the portfolio which is calculated based on three important factors that include the standard deviation of each of the assets present in the total Portfolio, the respective weight of that individual asset in total portfolio and correlation between each pair of assets of the portfolio.
This helps in determining the risk of an investment vis a vis the expected return. Raman plans to invest a certain amount of money every month in one of the two Funds which he has shortlisted for investment purpose. Thus based on his risk appetite if Raman wishes to avoid excess volatility he will prefer investment in Fund A compared to Fund B as it offers the same average return with the less amount of volatility and more stability of returns.
Standard Deviation of Portfolio is important as it helps in analyzing the contribution of an individual asset to the Portfolio Standard Deviation and is impacted by the correlation with other assets in the portfolio and its proportion of weight in the portfolio. Portfolio Standard Deviation calculation is a multi-step process and involves the below-mentioned process.
Portfolio Standard Deviation is the standard deviation of the rate of return on an investment portfolio and is used to measure the inherent volatility of an investment. A larger standard deviation implies more volatility and more dispersion in the returns and thus more risky in nature. It helps in measuring the consistency in which returns are generated and is a good measure to analyze the performance of Mutual funds and Hedge Funds returns consistency. However, it is pertinent to note here that Standard Deviation is based out of historic data and Past results may be a predictor of the future results but they may also change over time and therefore can alter the Standard Deviation so one should be more careful before making an investment decision based on the same.
This has been a guide to what is Portfolio Standard Deviation, its interpretation along with examples. Also, we learn how to calculate the standard deviation of the portfolio three assets. You may learn more about Asset Management from the following articles —.
Your email address will not be published. Save my name, email, and website in this browser for the next time I comment. Free Investment Banking Course. Login details for this Free course will be emailed to you. What is Portfolio Standard Deviation? Interpretation of Standard Deviation of Portfolio This helps in determining the risk of an investment vis a vis the expected return. Portfolio Standard Deviation is calculated based on the standard deviation of returns of each asset in the Portfolio, the proportion of each asset in the overall portfolio i.
A high portfolio standard deviation highlights that the portfolio risk is high and return is more volatile in nature and as such unstable as well. A Portfolio with low Standard Deviation implies less volatility and more stability in the returns of a portfolio and is a very useful financial metric when comparing different portfolios.
Example Raman plans to invest a certain amount of money every month in one of the two Funds which he has shortlisted for investment purpose. Details of which are reproduced below:. Popular Course in this category. View Course.
Leave a Reply Cancel reply Your email address will not be published.The optimization is based on the monthly return statistics of the selected portfolio assets for the given time period. The optimization result does not predict what allocation would perform best outside the given time period, and the actual performance of portfolios constructed using the optimized asset weights may vary from the given performance goal.
The required inputs for the optimization include the time range and the portfolio assets. Portfolio asset weights and constraints are optional. You can also use the Black-Litterman model based portfolio optimization, which allows the benchmark portfolio asset weights to be optimized based on investor's views.
You can upload a portfolio asset allocation by selecting a file below. The import uses a standard Excel or CSV file format with a ticker symbol followed by asset balance or weight on each row, and you can download sample CSV files example 1example 2 showing the import data format. You can upload a list of tickers by selecting either a text file of an Excel file below.
The tickers in the file can be listed either on separate lines or on the same line. Portfolio Type Asset Classes Tickers. Start Year End Year Maximize Return subject to Maximize Omega Ratio subject to Maximize Sortino Ratio subject to Robust Optimization No Yes.
Use Historical Returns Yes No. Use Historical Volatility Yes No.
Use Historical Correlations Yes No. Correlation Matrix Browse…. Asset Constraints Yes No. Group Constraints No Yes. Benchmark None Specify Ticker Import Benchmark Benchmark Ticker. Select asset 1. Select asset 2. Select asset 3. Select asset 4. Select asset 5. Select asset 6. Select asset 7.In the financial world, risk is the nemesis of return; that is, investors are usually forced to find the balance between the two, but most would prefer a no-risk, high-return investment.
As a result, there are numerous measurements for risk in the investment community. One of the most popular is variance, which is the spread of values around the average return. The square root of variance is standard deviation, which is viewed as a measure of volatility. Obtain the average return for each asset in your portfolio.
Let's say you have three stocks with an average return of 8. Subtract the mean from each return number. The mean is the average or the sum divided by the number of assets, which in this case equals The three subtraction results are 8. Square each return result. The three results are 4. Divide the sum by the number assets in the portfolio. The answer is This is the variance for the portfolio, which represents the average fluctuation in the portfolio.how to calculate average return and variance of a portfolio in excel
The square root of Variance is difficult to interpret directly, so standard deviation is used instead. In the example presented, the standard deviation is 4. One standard deviation translates into a probability of 68 percent. Therefore, about 68 percent of the time, you would expect the portfolio return to be between 4. Variance and standard deviation should be applied to "normal" data, that is, data that clusters equally around an average value. Skewed and outlying data can reduce the significance you attach to variance and standard deviation.
Expected Return and Variance for a Two Asset Portfolio
Sum the squares. This equals About the Author.In this paper, he described how investors can maximize their expected returns while minimizing risks. The main rationale behind the theory is if investors were given a choice of investing in two different assets which provides the same returns but have different risks, the investors would likely choose the asset with a lesser risk. Portfolio Risk In finance, risk is defined as the uncertainty that an investor has to face.
For a security like a stock it is measured as the volatility of the returns of the stock over a period of time. Similarly, for a portfolio, it is the volatility of the returns of the portfolio over a period of time.
The Standard Deviation is one way for tracking this volatility of the returns. Another way is through the Beta of the Stock or the Portfolio. Beta is a relative measure that measures the volatility of the stock or portfolio with respect to that of the market.
Links to all tutorial articles (same as those on the Exam pages)
A portfolio is simply a combination of assets or securities. The Modern Portfolio Theory describes how investors can combine different assets to create a portfolio that reduces risk but not the expected returns. This is considered the main aim of holding a portfolio over holding specific asset. The combination of different assets, especially uncorrelated assets, is known commonly as diversification.
This is where you frequently hear people talk about how diversification reduces risks. Portfolio Risk Calculator Spreadsheet This spreadsheet starts by calculating the Returns of the individual stocks, the Returns of the Portfolio and the Returns of the Market based on historical prices.
The Portfolio Mean, Variance and Standard Deviation are also calculated to allow you to see the effects of diversification. The spreadsheet also calculates the Beta of the Portfolio which is another measure of risk and the Correlation of the Portfolio with the Market.
Inputs Proportion The Portfolio Risk spreadsheet allows diversification of up to 6 different stocks by default. The first part of the spreadsheet allows you to key in the proportion of each stock in the portfolio.
Price The second part allows you to key in the prices of the stock and the market in the past 1 year. You can also refer to our spreadsheet on automatic downloading of stock data. The prices will be used for the calculation of returns and other results in the Outputs section.
Outputs The outputs of the spreadsheet are listed below. Download Free Modern Portfolio Risk spreadsheet - v1.Portfolio variance measures the dispersion of average returns of a portfolio from its mean.
It tells us about the total risk of the portfolio. It is calculated based on the individual variances of the portfolio investments and their mutual correlation. In case of a portfolio, we need to work out the variance using the individual variance of each investment. However, because different investments have less than perfect correlation, we must account for the covariances between different investments. The variance of a portfolio is less than the weighted average of the variance of individual investments due to their less than perfect correlation.
Since covariance equals the product of correlation coefficient and standard deviation of each asset, we can rewrite the above equation as follows:.
Illustrate diversification benefits in a portfolio of three investments, a stock A, a bond B, and a real estate asset C. The volatility is best measured using standard deviation which can be calculated as follows:. If the assets had perfect correlation and they move together, the portfolio variance and standard deviation would have been 0. But since different assets rarely have perfect correlation diversification is useful. A good test to see if addition of an asset will result in diversification benefit or not is to compare the Sharpe ratio before addition and after addition.
You are welcome to learn a range of topics from accounting, economics, finance and more. We hope you like the work that has been done, and if you have any suggestions, your feedback is highly valuable.
Let's connect! Go to top Formula Example Join Discussions. Join Discussions All Chapters in Finance. Current Chapter. About Authors Contact Privacy Disclaimer. Follow Facebook LinkedIn Twitter.Portfolio standard deviation is the standard deviation of a portfolio of investments.
It is a measure of total risk of the portfolio and an important input in calculation of Sharpe ratio. One of the most basic principles of finance is that diversification leads to a reduction in risk unless there is a perfect correlation between the returns on the portfolio investments. Owing to the diversification benefits, standard deviation of a portfolio of investments stocks, projects, etc. Okoso Arden is your friend. A year back he started following the stocks. In a finance article published in a magazine in those days, he read that the not-all-eggs-in-one-basket approach to investing is useful because it helps reduce risk.
Okoso requested you to calculate for him the extent to which the risk was reduced by the strategy. We can illustrate the fact that diversification indeed reduces the risk level by finding the weighted average standard deviation of the investments and then finding the portfolio standard deviation after taking into account the correlation between the two investments.
The portfolio standard deviation is You are welcome to learn a range of topics from accounting, economics, finance and more. We hope you like the work that has been done, and if you have any suggestions, your feedback is highly valuable. Let's connect! Go to top Formula Example Join Discussions. Join Discussions All Chapters in Finance. Current Chapter. About Authors Contact Privacy Disclaimer. Follow Facebook LinkedIn Twitter.