- How To Graph Efficient Frontier In Excel For Mac 2017
- How To Plot Efficient Frontier In Excel
- How To Graph Efficient Frontier In Excel For Mac Pdf
- How To Graph Efficient Frontier In Excel For Mac 2016
Jan 28, 2020 Select and highlight the range A1:F2 and then click Insert Line or Area Chart Line. The line graph is inserted with straight lines corresponding to each data point. To edit this to a curved line, right-click the data series and then select the “Format Data Series” button from the pop-up menu. Efficient Frontier Excel Workbook sheet: Efficient Frontier In order to create an efficient frontier, the expected return E(R p) was maximized while constraining the standard deviation σ p to specific values. The weights of each asset i, is w i. The correlation coefficient ρ ij is the correlation between assets i and j. Graphing the efficient frontier for a two-stock portfolio in Excel - Duration: 12:23. CFA/FRM: How to Build Efficient Frontier in Excel - Part 1 (of 2) - Duration: 20:07.
I mentioned in a previous post on how to get (nearly) live stock data from Google Finance. However, if you start pulling data from different markets, daily historical rates won’t make sense as different markets are closed on different days. This causes problems when trying to figure out the correlation between stocks. A solution to this is to pull monthly rates as the adjusted stock price for each month will be a better indicator of correlation.
Python has a great library called pandas_datareader that allows you to pull in historical information right into a pandas dataframe. The only downside is if an API is deprecated, your code breaks. Hence, I’ve elected to create this tutorial using Yahoo Finance’s Historical data download function. The objective of this article is to pull a large amount of worldwide historical data (over 20 years worth) and then use Modern Portfolio Theory (Mean Variance Optimization) to create an efficient frontier. The efficient frontier can help decide asset allocations in your portfolio based on a given risk tolerance and expected return.
Overview
The goal of the portfolio optimization is to retrieve an annualized expected return for a given risk tolerance. The return is associated with a portfolio of weightages (asset-allocation) to help decide investment strategies. The optimization strategy that will be used in this analysis is Modern Portfolio Theory (Markowitz, 1952), commonly known as Mean Variance Optimization (MVO) introduced by Harry Markowitz in 1952. The MVO model only takes into consideration historical results and thus is limited to that. It will not be able to account for other factors that could affect a model such as ‘views’ or insight into future market forecasts. A more robust model that could be incorporated in future optimizations is the Black-Litterman Model (Black & Litterman, 1992) that enhances the asset-allocation process by introducing user input for specific opinions on market returns.
Data Collection
Throughout this post, I will guide you through an excel workbook so you can understand exactly what is happening. Update win 7 sp2 iso. If you’d like, you can download the excel workbook here.
In order to avoid high active management costs, I’ve elected to use Index Funds as the selected assets for the optimization. Market indices have the longest history of stock information and won’t have the risks of individual company stocks. Historically markets have grown and by using market indices, the expected return will be at the market average, which is far better than trying to outperform the market year after year. In addition, indices helped provide greater exposure, both in terms of geographic location and in terms of type of asset. The following indices were chosen for the optimization (Table 1).
[Excel Workbook sheet: Names]
Index | Name | Country | Type |
^DJI | Dow Jones Industrial Exchange | US | Equity |
^IXIC | Nasdaq | US | Equity |
GSPTSE | Toronto Stock Exchange | Canada | Equity |
N225 | Nikkei 225 | International | Equity |
GSPC | S&P500 | US | Equity |
XOI | NYSE Arca Oil Index | Worldwide | Commodity |
HUI | HUI Gold Index | Worldwide | Commodity |
HIS | Hong Kong Stock Exchange | International | Equity |
FBIDX | Fidelity® U.S. Bond Index Fund Investor Class | US | Fixed Income |
VEIEX | Vanguard Emerging Mkts Stock Idx Inv | International | Equity |
FBNDX | Fidelity Investment Grade Bond | US | Fixed Income |
TYX | Treasury Yield 30 Years | US | Fixed Income |
VBMFX | Vanguard Total Bond Market Index Inv | US | Fixed Income |
VEURX | Vanguard European Stock Index Investor | Europe | Equity |
Table 1. Breakdown of assets chosen for optimization
Since the investors will be Canadians, I look at the geographic outlook in three different areas: Canada, the US, and International. Also, by type of assets, a mixture of equity, fixed income, and commodities were chosen.
Since the optimization would look at upwards of 30 years in the future, the more data collected, the better. Data was collected from July 1st, 1996 to March 1st, 2018 to account for approximately 22 years of data. It was a trade-off to either get more years of data or exclude the fixed income funds due to the limited data available for those assets. To avoid inconsistent dates for data collection due to different market closures worldwide, monthly data was collected. This time frame covers a few market crashes including the Asian Financial Crisis (Michael & John, n.d.) in 1997, the Dotcom Crash (Beattie, Market Crashes: The Dotcom Crash (2000-2002), n.d.) from 2000-2002, and the Housing Bubble/Credit Crisis (Beattie, Market Crashes: Housing Bubble and Credit Crisis (2007-2009), n.d.) of 2007-2009.
Adjusted monthly close price was pulled from Yahoo Finance (Historical Stock Data) by applying filters to pull from the following parameters:
- Time Period: Jul 01, 1996 – Mar 1, 2018
- Show: Historical Prices
- Frequency: Monthly
Data for each asset was collected in that indices’ respective currency and outputted to a csv file. Currency conversion was not required as the percentage return was calculated for returns.
Optimization Process
Monthly Returns
[Excel Workbook sheet: Adjusted Closing]
The adjusted closing prices were taken from each index and then collated into a table.
[Excel Workbook sheet: Returns]
After that the monthly returns were calculated as a percentage by subtracting the closing price of a previous month form the closing price of the current month and then dividing that by the closing price of the previous month.
Ri = (ClosingPricei – Closing Pricei-1) / Closing Pricei-1
An average of the all the returns was taken to get the average monthly return for each asset. Then those averages were multiplied by 12 to get an annualized average return.
Covariance and Standard Deviation
[Excel Workbook sheet: Cov-STD-Cor]
A covariance matrix was created by comparing each asset with all the other respective assets. This matrix is essential in understanding the risk of each asset and how it relates to the others.
The standard deviation for each asset was taken by looking at the monthly returns. This was then used to calculate the correlation matrix.
The top part of this formula is the Covariance matrix in the excel workbook and the bottom part is the D Matrix. By dividing these two, you get the Correlation Matrix.
Correlation
Correlation is important in understand how similar two assets are. The higher the correlation, the more likely they are to move in the same direction. Negative correlation implies that if one asset goes up, the other will go down and vice versa.
As expected, assets have 100% correlation with themselves. In addition, the S&P500 (GSPC) is 95% correlated with the Dow Jones Industrial Exchange (DJI). This is intuitive as there is a lot of overlap between the two indices. Another interesting finding is that Treasury Bills are heavily negatively correlated with corporate bond indices.
![How To Graph Efficient Frontier In Excel For Mac How To Graph Efficient Frontier In Excel For Mac](https://www.bogleheads.org/w/images/1/11/US-International.png)
Efficient Frontier
[Excel Workbook sheet: Efficient Frontier]
In order to create an efficient frontier, the expected return E(Rp) was maximized while constraining the standard deviation σp to specific values. The weights of each asset i, is wi. The correlation coefficient ρij is the correlation between assets i and j.
Before we do anything, it is important to remember to annualize the Expected Returns and Standard Deviations. Since we took monthly closing prices, multiple them by 12 as seen by cell A2 in this worksheet.
Excel’s Solver plugin was used to calculate these maximum points. If you don’t already have solver added in, go to File -> Options -> Add-Ins -> Manage: Excel Add-Ins Go -> Click Solver Add-in and select OK. If done correctly, you should now see the Solver option in the Data pane on excel.
The weightage of each asset was constrained to positive values to avoid short selling and with an upper bound of 20% to ensure diversification. The solver was run for multiple different iterations of Standard Deviation for the portfolio (STD_P) to get different points for the efficient frontier. After about 15 scenarios, the Expected Return of the Portfolio (E(R_P)) was graphed against the STD_P to get your efficient frontier. To ensure you get a nice frontier, be sure you maximize the Expected Return, but also minimize it. Of course you’ll only care about the maximum expected return because why would you want less money?
As expected, portfolios with a higher return were exclusively in equities which have higher risk and portfolios with the least amount of risk were heavily weighted into the fixed-income assets.
The optimal point with minimal risk is an annual Expected Return of 4.55% and a portfolio consisting of the following assets:
Ticker | Weightage |
DJI | 0.11 |
GSPTSE | 0.08 |
HUI | 0.01 |
N225 | 0.03 |
FBIDX | 0.20 |
FBNDX | 0.20 |
TYX | 0.16 |
VBMFX | 0.20 |
Works Cited
Beattie, A. (n.d.). Market Crashes: Housing Bubble and Credit Crisis (2007-2009). Retrieved from Investopedia: https://www.investopedia.com/features/crashes/crashes9.asp
Beattie, A. (n.d.). Market Crashes: The Dotcom Crash (2000-2002). Retrieved from Investopedia: https://www.investopedia.com/features/crashes/crashes8.asp
Black, F., & Litterman, R. (1992). Global Portfolio Optimization. Financial Analysis Journal, 28-43.
Historical Stock Data. (n.d.). Retrieved 03 16, 2017, from Yahoo Finance: https://finance.yahoo.com/quote/%5EN225/history?period1=975646800&period2=1521086400&interval=1mo&filter=history&frequency=1mo
Markowitz, H. (1952). Portfolio Selection. The Journal of Finance, 77-91.
Michael, C., & John, C. (n.d.). Asian Financial Crisis. Retrieved from Federal Reserve History: https://www.federalreservehistory.org/essays/asian_financial_crisis
Disclaimer — The material in this article is provided for informational purposes only. It is not a recommendation to buy or sell any security or implement any investment strategy.
Modern portfolio theory attempts to maximize the expected return of a portfolio for a certain level of risk. The theory is that by diversifying through a portfolio of assets we can get a higher return per unit of risk than we can by holding an individual asset, and that by adjusting the weights of each asset in a portfolio we can create an optimal portfolio for each investor’s level of risk aversion. Assuming that markets are efficient and that the assets in a portfolio aren’t perfectly correlated, we can reduce the total variance of a portfolio at any given expected return by combining assets in various weights.
Imagine a graph with risk on the X axis (measured as standard deviation of the asset’s historical returns) and dividend-adjusted return on the Y axis (measured as an average of historical return). We can plot every possible combination of risky assets in a portfolio to find the best possible return at each level of risk (and lowest possible risk for each level of expected return), and the resulting curve would have a higher return for any given level of risk than any individual asset. We can then combine our efficient portfolio with a risk free asset to create a portfolio of portfolios which we can graph as the capital market line. The capital market line will lie tangent to the efficient frontier at the portfolio with the highest sharpe ratio and represent the highest expected return per unit of risk; this represents the market portfolio. By levering up the capital market line or combining the optimized portfolio with a risk-free asset, we can achieve expected returns higher than the efficient frontier at respective standard deviations.
I will demonstrate how the efficient frontier and capital market line can be modeled in Excel in order to produce efficient portfolios. Let’s say that we choose to construct a risky portfolio with all 30 stocks in the Dow Jones Industrial Average. Below is a screenshot of the closing price of the stocks each month over the past five years, along with an index of the S&P 500 to include in our model.
We can find continuously compounded monthly returns by taking the natural log of the closing price of each month / the closing price of the month prior. In Excel, this is entered as
How To Graph Efficient Frontier In Excel For Mac 2017
=LN(Prices!B4/Prices!B5)
We create a table of continuously compounded monthly returns for the past five years, then compute the mean and standard deviation for each stock over this time horizon.
Covariance between returns on stocks is an important factor in this portfolio optimization because the value of diversification comes from assets that are not perfectly correlated; the greater the covariance, the more effectively we can diversify in order to reduce portfolio variability.
Covariance between returns on assets A and B is defined as
We must find the sum of every stock’s monthly change in price less its mean for the five-year monthly returns multiplied by every other stock’s monthly change in price less its mean for the five-year monthly returns. This number will then be divided by the number of months in our table. We can compute the covariance between every single stock in this model using matrix algebra - to do this we subtract the matrix of returns by the vector of means and multiply it by the transposed version of itself subtracted by the vector of means. In Excel, this is entered as
=MMULT(TRANSPOSE(B3:AF62-B64:AF64),B3:AF62-B64:AF64)/COUNT(A3:A62)
where the count command represents the number of monthly returns in my table.
The covariance table will appear as such:
At this point we can construct a portfolio of arbitrary weights assigned to each stock. We then create cells to store the portfolio mean (vector of mean returns multiplied by vector of weights assigned to each stock to get a weighted average mean), portfolio variance (the vector of weights multiplied by the covariance matrix multiplied by the vector of weights), and portfolio standard deviation (square root of variance). Depending on the way I structure my spreadsheet, I may need to transpose vectors in order to multiply my matrices.
Upon constructing this part of the spreadsheet I can begin to find points on the efficient frontier. If we find two efficient portfolios, we can plot the entire efficient frontier by varying the weight of capital applied to each of these portfolios. I can use Excel’s Solver to find two efficient portfolios; the way I choose to do this is to maximize the value of the portfolio mean by changing the portfolio weights, subject to the constraint of the weights adding to 100% and a portfolio standard deviation of 7%. I then repeat the process for a constraint of portfolio standard deviation equal to 8%.
![Excel Excel](https://img.wonderhowto.com/img/97/45/63621543287863/0/create-tangent-line-with-excel.w1456.jpg)
Allowing portfolio weights to be negative represents taking a short position in a stock.
I can copy and save the values of the portfolio weights, mean, variance, and standard deviation after each iteration of running Excel’s Solver to the side of my worksheet. Vector magic full version free download.
I can use a linear combination of my two efficient portfolios to effectively calculate the portfolio mean and standard deviation of every efficient combination of assets. I will arbitrarily pick a weight for one of my efficient portfolios within this new portfolio of portfolios and enter it into a cell in Excel, then find a weighted average return for my new portfolio. The covariance between these two efficient portfolios is defined as
effectively multiplying the vector of weights for the first efficient portfolio by the covariance matrix of all stocks by the vector of weights for the second efficient portfolio. The standard deviation of the new portfolio of efficient portfolios will simply be the square root of this covariance.
At this point we can create a data table using Excel’s What-If Analysis to easily calculate the standard deviation and mean at many various weights of portfolio A with respect to portfolio B. The left column here represents the weight applied to my portfolio with standard deviation 7%, where the weight of my portfolio with standard deviation 8% equals 1 - portfolio of StDev 7%.
[Data table continues downward until Weight7 = 10]
Plotting this data table as a scatterplot or scatter with smooth lines allows us to visualize the efficient frontier. Every point on the positively sloped region of this curve represents a portfolio that will offer the highest expected return per unit of risk.
This efficient frontier is computed based on two efficient portfolios, and if we were constructing a portfolio based on these findings we would be curious to see what weight to apply to each stock in this portfolio at any given point. This is easy to compute by creating a matrix with a column for each stock and a row for each weight of portfolio A used in the data table, then multiplying the weight of portfolio A by the respective weight of stock A in portfolio A and adding this to the weight of portfolio B (1 - weightA) multiplied by the respective weight of Stock A in portfolio B, and completing the entire matrix with such a formula. Alternatively, we can use Excel’s Solver to solve for our initial weight-seeking task with the constraint of a mean or standard deviation equal to a certain value to find the optimal portfolio weights at that efficient portfolio.
We can maximize the sharpe ratio [(portfolio mean - risk free rate) / portfolio standard deviation) to create a portfolio with the best historical risk-adjusted performance. The CAPM model allows us to combine the efficient portfolio with a risk-free asset to do attain a higher expected return than the efficient frontier. We do this by combining the risk free asset with the tangent portfolio containing all of our assets (this portfolio would represent the cap-weighted market portfolio). If we construct a portfolio at this point and keep the individual asset weights constant, we can lever up to reach a higher expected return or combine this portfolio with a risk free asset to a lower standard deviation; all the possible combinations of the risky portfolio and risk free asset make up the capital market line.
The CML’s slope is that of the sharpe ratio, thus indicating the best expected return per unit of risk, which will be superior to any portfolio on the efficient frontier. The CML also lies northwest of the efficient frontier, representing lower risk and/or higher expected return. The point of tangency represents a portfolio of 100% of the risky asset, whereas portfolios on the CML with a higher expected mean represent leverage and portfolios on the CML with a lower standard deviation combine the risky portfolio with a risk-free portfolio.
To model this in Excel, we first find the tangent portfolio by maximizing the sharpe ratio subject to the constraint of all portfolio weights adding up to 100% using Excel’s solver. We then construct a risky-riskless portfolio by combining the tangent portfolio with the risk-free asset in at an arbitrary weight. We find the mean and standard deviation of the portfolio at this arbitrary weight. Since the standard deviation of the risk-free asset is zero, we can simply multiply the tangent portfolio standard deviation by the tangent portfolio weight to find the standard deviation of the risky-riskless portfolio.
We can then create a data table as before.
How To Plot Efficient Frontier In Excel
When graphed, this purple line represents the capital market line. Any rational investor would hold a portfolio of weights in proportions to the tangent portfolio constructed, then combine this portfolio with a risk-free asset according to their risk tolerance in order to generate expected returns above the efficient frontier.
How To Graph Efficient Frontier In Excel For Mac Pdf
The S&P 500 landed at the following spot for this data set.
How To Graph Efficient Frontier In Excel For Mac 2016
Using such a model would help an investor who believes in efficient markets construct an optimal portfolio exposed to the highest expected return at the lowest variance. Limitations can be placed on this portfolio when using Excel’s solver if needed, for example to limit the maximum investment in any individual stock or disallow short selling.