- How To Plot Efficient Frontier In Excel
- Graphing Efficient Frontier In Excel
- How To Graph Efficient Frontier In Excel For Mac Download
- How To Graph Efficient Frontier In Excel For Mac 2016
An efficient frontier is a graph that plots the expected return on a portfolio of investments on the y-axis and its risk as measured by its standard deviation on the x-axis. It demonstrates the risk-and-return trade-off of a portfolio and helps us visualize the efficient portfolios, the global minimum variance portfolio and inefficient portfolios. EfficientFrontierPortfolios (Excel 2010) (Version 3 corrects the volatility estimate for FrontierPort4.) The workbook is “flexible” in that it accommodates any number of assets up to 50. It is simple to use. Disclaimer This workbook and the results it produces do not constitute investment advice.
An efficient frontier is a graph that plots the expected return on a portfolio of investments on the y-axis and its risk as measured by its standard deviation on the x-axis. It demonstrates the risk-and-return trade-off of a portfolio and helps us visualize the efficient portfolios, the global minimum variance portfolio and inefficient portfolios.
In a portfolio, we can have a range of different combinations of the individual assets and the total portfolio return and standard deviation changes in response to any change in the asset allocation. The portfolio’s expected return is the weighted-average of the expected return of the individual investments and its variance and standard deviation depends on the weight of each asset, their individual standard deviation and their mutual correlation.
A risk-averse investor’s aim is to identify the mix of investments which yields the highest return for the lowest level of investment risk or exposes his to lowest level of risk for a given investment return. The efficient frontier plots portfolios that yields the same risk-return tradeoff. Any portfolio that falls below the efficient frontier is an inefficient portfolio because it is generating lower return per unit of risk. Such a graph is called the efficient frontier. The point on the efficient with the lowest standard deviation is called the minimum variance portfolio. On the same graph we can plot an investor’s utility function given his level of risk aversion. The point of intersection of the efficient frontier and the utility curve represent the portfolio allocation that is best suited given the risk-return trade-off and risk aversion of the investor.
The efficient frontier is based on standard deviation, a measure of total risk. However, because unique risk can be eliminated through diversification, we need to only worry about systematic risk. Beta coefficient is a measure of systematic risk and the capital asset pricing model, which estimates required return with reference to the risk-free rate and the market portfolio and the investment beta is a better model of risk-return.
Example
You are considering two assets, Asset A and Asset B, for investment. Black squad offline. The following table shows your expectation of their expected return and standard deviation:
How To Plot Efficient Frontier In Excel
Asset | E(R) | Standard Deviation |
---|---|---|
A | 12% | 6% |
B | 8% | 4% |
Correlation coefficient between returns on both assets is expected to be 0.6
We can invest all our money in A or B or in some combination of A and B. The following table shows our portfolio’s expected return and standard deviation at different mix of A and B.
Asset A Weight | Asset B Weight | Portfolio Standard Deviation | Portfolio Expected Return |
---|---|---|---|
100% | 0% | 6.00% | 12.00% |
80% | 20% | 4.87% | 11.20% |
60% | 40% | 3.94% | 10.40% |
40% | 60% | 3.39% | 9.60% |
20% | 80% | 3.42% | 8.80% |
0% | 100% | 4.00% | 8.00% |
The results above can be plotted to get the following graph. It is called efficient frontier.
Portfolio mix at Point C and D are better than B and A respectively because they offer higher expected return at for the same level of risk.
The left-most point on the curve is the global minimum variance portfolio, the portfolio that has the lowest possible risk as measured by the standard deviation. However, it is not necessarily the best risk-return trade-off. As we move to portfolio C, D, E and F, there will be a point at which the investor’s utility curve will intersect the efficient portfolio. It will be the best possible portfolio for the given investor.
by Obaidullah Jan, ACA, CFA and last modified on
Studying for CFA® Program? Access notes and question bank for CFA® Level 1 authored by me at AlphaBetaPrep.com
Studying for CFA® Program? Access notes and question bank for CFA® Level 1 authored by me at AlphaBetaPrep.com
Related Topics
Graphing Efficient Frontier In Excel
As we know, an efficient frontier represents the set of efficient portfolios that will give the highest return at each level of risk or the lowest risk for each level of return. A portfolio is efficient if there is no alternative with:
- Higher expected return with same level of risk
- Same expected return with lower level of risk
- Higher expected return for lower level of risk
How To Graph Efficient Frontier In Excel For Mac Download
Let’s take a portfolio of two assets and see how we can build the efficient frontier in excel. Let’s say we have two securities, A and B, with the following risk-return data.
We can combine these two assets to form a portfolio. In the portfolio, we can combine the two assets with different weights for each asset to create an infinite number of portfolios having different risk-return profiles. For example, if we take 50% of each asset, the expected return and risk of the portfolio will be as follows:
E(R) = 0.50*12% + 0.50*20% = 16%
How To Graph Efficient Frontier In Excel For Mac 2016
σ = Sqrt(0.20^2*0.5^2+0.40^2*0.5^2+2*(-0.2)*0.5*0.5*0.2*0.4) = 20.49%
The following table shows the risk-return profile for different portfolios created by combining the two assets in different weights.
The risk and return of these portfolios can be plotted on the XY scatter graph with risk on x-axis and return on Y axis. The graph looks as follows and is called the efficient frontier.
Note that this graph was created with just two assets in the portfolio. The efficient frontier can be created using multiple assets. This frontier represents all the feasible portfolio combinations that one can create. There is also a minimum variance portfolio (MVP) for which there is minimum risk. An investor will not want to purchase a portfolio below the MVP. The curve bends backwards which indicates the benefits of diversification due to negative correlation.