Links to all tutorial articles (same as those on the Exam pages)
※ Download: Minimum variance portfolio excel
To do this we will make a correlation matrix. The optimal risky asset portfolio is at the point where the CAL is tangent to the efficient frontier. UPDATE : I changed the layout of the workbook including the graph displayed.
Finally, if desired you can select a minimum and maximum percentage in a given stock under the output statistics. Gain the confidence you need to move up the ladder in a high powered corporate finance career path. In this example, the square root of 1.
Portfolio Optimization - Optional: Next, in order to generate the Efficient Frontier, we can design two other optimal portfolios with different target volatilities in order to plot on the frontier. I have figured out the minimum variance portfolio % in stocks, % in bonds, exp.
Capital Allocation Line CAL and Optimal Portfolio The Capital Allocation Line CAL is a line that graphically depicts the risk-and-reward profile of risky assets, and can be used to find the optimal portfolio. The process to construct the CAL for a collection of portfolios is described below. Portfolio expected return and variance For the sake of simplicity, we will construct a portfolio with only two risky assets. Levels of variance translate directly with levels of risk; higher variance means higher levels of risk, and vice versa. The variance of a portfolio is not just the weighted average of the variance of individual assets, but also depends on the covariance and correlation of the two assets. The variance of portfolio return is greater when the covariance of the two assets are positive, and less when negative. Since variance represents risk, the portfolio risk is lower when its asset components possess negative covariance. Diversification is a technique that minimizes portfolio risk by investing in assets with negative covariance. The efficient frontier A portfolio frontier is a graph that maps out all possible portfolios with different asset weight combinations, with levels of portfolio standard deviation graphed on the x-axis and portfolio expected return on the y-axis. To construct a portfolio frontier, we first assign values for E R 1 , E R 2 , stdev R 1 , stdev R 2 , and ρ R 1 , R 2. The result is shown on the graph below, where each dot on the plot represents a portfolio constructed under an asset weight combination. So how do we know which portfolios are attractive to investors? If such is the case, then all investors would prefer A to B. From the graph, we can infer that portfolios on the downward sloping portion of the portfolio frontier are dominated by the upward sloping portion. As such, the points on the upward sloping portion of the portfolio frontier represent portfolios that investors find attractive, while points on the downward sloping portion represent portfolios that are inefficient. According to the mean-variance criterion, any investor would optimally select a portfolio on the upward sloping portion of the portfolio frontier, which is called the efficient frontier , or minimum variance frontier. A portfolio above the efficient frontier is impossible, while a portfolio below the efficient frontier is inefficient. Complete portfolio and capital allocation line In constructing portfolios, investors often combine risky assets with risk-free assets such as government bonds to reduce risks. A complete portfolio is defined as a combination of a risky asset portfolio, with return R p , and the risk-free asset, with return R f. The slope of the line, S p , is called the Sharpe Ratio The Sharpe Ratio is a measure of risk adjusted return comparing an investment's excess return over the risk free rate to its standard deviation of returns. The Sharpe Ratio or Sharpe Index is commonly used to gauge the performance of an investment by adjusting for its risk. The Sharpe ratio measures the increase in expected return per unit of additional standard deviation. Optimal portfolio The optimal portfolio consists of a risk-free asset and an optimal risky asset portfolio. The optimal risky asset portfolio is at the point where the CAL is tangent to the efficient frontier. This portfolio is optimal because the slope of CAL is the highest, which means we achieve the highest returns per additional unit of risk. The graph below illustrates this: The tangent portfolio weights are calculated as follows: Summary of capital allocation line Investors use both the efficient frontier and the CAL to achieve different combinations of risk and return based on what they desire. The optimal risky portfolio is found at the point where the CAL is tangent to the efficient frontier. This asset weight combination gives the best risk-to-reward ratio, as it has the highest slope for CAL. Download the Free Template Enter your name and email in the form below and download the free template now! You may withdraw your consent at any time. This request for consent is made by Corporate Finance Institute, 16th Floor, 595 Burrard Street, Vancouver, British Columbia. This iframe contains the logic required to handle Ajax powered Gravity Forms. The Sharpe Ratio or Sharpe Index is commonly used to gauge the performance of an investment by adjusting for its risk. Download CFI's Excel template and Sharpe Ratio calculator. Gain the confidence you need to move up the ladder in a high powered corporate finance career path.
We can accomplish both of these tasks by using the paste special function in VBA. We need to calculate a few statistics from the percent change sheet, including annualized standard deviation and annualized return. We can also autofill a range of pseudo weights. This leads to a more robust portfolio that is less subject to estimation risk. You can retrieve the data for each of these companies from Yahoo Finance, the stock symbols are below. This workbook does not support the generation of the Tangency Portfolio given a portfolio of assets.
