6/8/2020 Efficient Frontier Software Excel
I'm trying to calculate the efficient frontier (and the optimal portfolio at the Sharpe ratio) given two vectors for a portfolio: (1) expected returns and (2) historical standard deviations. I would like to be able to calculate this in R. Using the portfolioFrontier function of the fPortfolio package in R, I have successfully calculated the efficient frontier and optimal portfolio allocation at the Sharpe ratio using a time series of historical returns. However, the fPortfolio package only appears to allow back-testing on a time series. I would like to calculate the efficient frontier and optimal portfolio at the Sharpe ratio for future (i.e., expected) returns. How can I do this?Ideally this would be implemented with a function in R. The closest resource I could find was from using Octave code.
![]()
In this context, I develop concepts around efficient frontier. I would like to show my students how an efficient frontier could be built out of excel using solver and VBA. I am currently trying to automate a VBA-prompted Solver to generate an efficient frontier; The solver is pre-calibrated, so no need to set goal, constraints etc.
I successfully translated the code to R, but the efficient frontier doesn't appear to match (or be as accurate) as the one from the R package.Here's my attempt in R (translating Octave code from the above website): expectedReturns.
Free Portfolio OptimizationBackgroundIn 1952, Harry Markowitz published a paper on portfolio selection and the effects of diversification on security returns. His works have a great impact on modern finance and have led to the development of the Capital Asset Pricing Model by William Sharpe, Linter and Mossin.In the Portfolio Risk spreadsheet, we have developed a model to calculate the Returns, Mean, Variance and Standard Deviation of a Portfolio based on historical prices. The calculation allows us to see the effects of diversification in the Portfolio. We are taking a step further in this Portfolio Optimization spreadsheet by optimizing the allocation of the assets in the portfolio using Markowitz theory. We will start with a worksheet that models the Risk Reward Trade Off Line followed by by a worksheet that models Portfolio Optimization of 2 Assets. With these two worksheets as a basis, we will use the Microsoft Excel Solver to model the complex Portfolio Optimization of more than 2 assets. Finally we will integrate our portfolio optimization model with stock prices downloaded from https://finance.yahoo.com. A regression of the historical prices will be performed automatically and the output average returns, correlations, variances and covariances will be used for the portfolio optimization model. Risk Reward Trade Off LineThe basic principle of the Risk Reward Trade Off Line is the more risk you take, the higher your reward. Of course, the flip side is the possibility of you losing more money. Markowitz theory allows us to vary the amount of risk we undertake in the hope of achieving the returns we expected. The basic concept is to build a portfolio which consists of a normal asset like a stock or a bond with another Riskless Asset like the U.S Treasury Bills. By varying the proportion of each asset, it allows us to vary the amount of risk we wish to undertake vs the returns we hope to achieve.Risk Reward Trade Off Line Spreadsheet![]() Inputs
OutputsThe worksheet uses the Portfolio theory to calculate the expected return of the portfolio using the following formula:Expected Return of Portfolio = Weight of normal asset * Expected Return of normal asset + Weight of Riskless asset + Expected Return of Riskless asset. The standard deviation of the portfolio is the proportion of total assets invested in the risky asset multiply by the standard deviation of the risky asset. This is because the standard deviation of the riskless asset is considered to be zero. Risk Reward Trade Off LineThe graph shows the different proportion of the normal and Riskless asset. It is simple to see that by investing proportionately more on the normal asset, it may allow us to achieve more returns but at the same time will subject us to more risks. Thus the risk appetite of the investor will determine the various proportions of the portfolio to use.Correlations of AssetsOne of the basic aspects of building a portfolio is to include assets which are negatively or have a small positive correlation with each other. When the assets in a portfolio do not move in the same direction, it is thought to be safer as they do not fluctuate as much. In the next few sections, we will see correlation between the different assets to be an assumed number or calculated from the regression of historical asset prices.Portfolio Optimization (2 Assets)In the 'PortfolioOptimization2Assets' worksheet, we will use Markowitz theory to optimize the proportions of the 2 normal risky assets and the riskless asset in the portfolio. By optimizing the portfolio, we will have a portfolio that is considered as an efficient portfolio.Efficient PortfolioA efficient portfolio is one that combines the different assets to provide the highest level of expected return while undertaking the lowest level of risk.Efficient FrontierThe graph below shows the attainable set of portfolios by combining the different risky assets as dark dots. From the point X to the point Y in the blue curve, it allows us to achieve highest level of return with the minimal risk we have to undertake. This set of portfolios is known as the efficient frontier. The efficient frontier has been proven to be a hyperbola curve when expected return is plotted against standard deviation.Tangency PortfolioThe Tangency Portfolio is a portfolio that is on the efficient frontier with the highest return minus risk free rate over risk. In other words, it is the portfolio with the highest Sharpe ratio.Inputs
OutputsThe following four fields are the most important output of this worksheet model. They are the weights of the two different assets that give us the optimal portfolio based on Markowitz theory. The Standard Deviation and the expected Rate of Return are also calculated.
Usage of Optimal PortfolioThis section of the worksheet allows you to enter the amount to invest and it will use the Optimal Portfolio weights to calculate the amount to invest in the Riskless Asset, Asset 1 and Asset 2. By entering the Expected Rate of Return, it uses the Risk Reward Trade Off Line to vary the proportion of the Portfolio of normal assets and Riskless Asset.Optimal Combination of Risky Assets CurveThe optimal combination of risky assets curve is plotted using the following fields by varying the weights in Asset 1 and 2 and calculating the Standard Deviation and Expected Return.
Efficient Trade Off LineThe Efficient Trade Off Line shows the different proportion of the normal and Riskless assets. The following field is added to the curve in the previous section.![]()
Download Free Portfolio Optimization spreadsheet - v1.2System RequirementsMicrosoft® Windows 7, Windows 8 or Windows 10 Windows Server 2003, 2008, 2012 or 2916 512 MB RAM 5 MB of Hard Disk space Excel 2007, 2010, 2013 or 2016 License By downloading this software from our web site, you agree to the terms of our license agreement. Download FreePortfolioOptimization.zip (Zip Format - 112 KB) Get the Professional versionJoeri D. Benefits
Limited Time Bonus
USD69.00 - Purchase (For a limited time, get it Free. Click on the Details page above to find out how.) ![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |