|
 Your holdings, analysis options, risk and return Portfolio Optimizer Pro™ is an easy-to-use Windows® desktop implementation of the Markowitz Efficient Frontier, a sophisticated mathematical modeling tool that will enable you to evaluate your portfolio's diversification, risk, and return quickly and accurately. Using Portfolio Optimizer Pro™ you can in many cases realize a substantially more favorable outcome in your portfolio merely by reallocating your investment among the securities you already hold, either to minimize the portfolio risk for a given return, or to maximize the portfolio return for a given level of risk. Background: The Markowitz Efficient Frontier Model In 1952, Vol. 7, Issue 1 of the Journal of Finance contained an article titled “Portfolio Selection” by economist Harry Markowitz. In the article, Dr. Markowitz discussed the ideas of portfolio risk, diversification, and correlation of returns on assets. Using standard deviation of returns as the measure of portfolio risk, Markowitz showed that, by choosing assets whose returns are not perfectly positively correlated, the risk of a portfolio can be lowered while maintaining or increasing the expected return. The article defined a Markowitz Efficient Portfolio as one with minimum risk for a given level of return, and maximum return for a given level of risk: no additional diversification can improve a Markowitz Efficient Portfolio for a given return or a given level of risk. The set of all Markowitz Efficient Portfolios constitutes the Markowitz Efficient Frontier: risk/return combinations above the Frontier are not possible, and risk/return combinations below the Frontier are not efficient. Dr. Markowitz was awarded the Nobel Prize in Economics in 1990 for his pioneering work, which forms a cornerstone of Modern Portfolio Theory. Portfolio Optimizer Pro™ enables you to perform this type of analysis quickly and easily on your Windows® desktop, and thereby enhance your bottom line with the same type of analysis that elite financial professionals have done for years behind closed doors. Step 0: Before You Begin Make sure that your computer has Excel 2000 or Excel 2003 installed with the "Solver" add-in. Make sure that your computer is connected to the Internet. Step 1: Create a Portfolio In the Security Ticker box on the Create Portfolio tab, enter the ticker symbol for a security you would like to add to your portfolio. The Model will recognize most ticker symbols for stocks, ETFs, indices, REITs and mutual funds traded on major world exchanges. Furthermore, you do not need to use upper-case letters in the symbol; the Model will change lower-case letters to upper-case when the security is added to your portfolio. When you’ve typed in the ticker symbol, click the Add to Portfolio button. The Model will check to make sure that the symbol you’ve entered is valid and that it can get historical price data on the Internet for that symbol. Once that is verified, it will add that security to your portfolio. Depending on which edition you have, the Model allows up to three, five, ten, or twenty-five securities in a portfolio, but you need a minimum of two securities to run the model. If you want to delete a security from your portfolio, type the ticker symbol into the Security Ticker box, then click the Delete from Portfolio button. Once you have added all the securities to the portfolio, enter the value of the portfolio into the Portfolio Value box. This is the dollar figure that the Model will use to determine the amount to allocate to each security in an efficient portfolio; it must be a positive number. You can enter the number without a dollar-sign; e.g., enter $1,000,000.00 as “1000000”. You may also enter values for an existing portfolio of these securities; later, this portfolio can be analyzed for risk / return and plotted against the Efficient Frontier. The values need not add up to the portfolio value; the model will use them as relative weights. So, for example, if you wanted to analyze a portfolio with all the securities equally weighted, you could enter “1” for the value of each; if you have three securities and want twice as much of the second as the first, and twice as much of the third as the second, you could enter “1” for the first, “2” for the second, and “4” for the third. Step 2: Generate the Markowitz Workbook After you have created the portfolio and given it a value, click the Generate Markowitz Workbook button. This will create an Excel workbook with historical pricing data for all of the securities, and all of the information required to implement the Markowitz Efficient Portfolio model on the Summary tab. When the Model has finished generating the workbook, you will notice that the Portfolio Securities box now has dollar figures next to each security; when you create an efficient portfolio these figures will represent the allocation amongst the securities in the efficient portfolio. You will also notice numbers in the boxes labeled Max Ann. Return w/o Shorting, Min Ann. Return w/o Shorting, Annual Return, and Annual Risk. If you have chosen to maximize return for a given annual risk level, the labels on the first two boxes will be Max Ann. Risk w/o Shorting and Min Ann. Risk w/o Shorting, respectively. - Max Ann. Return w/o Shorting: The maximum annual expected return that you can get using the securities in your portfolio without any short-selling. It is computed as twelve months’ compounding of the maximum average monthly return from the historical data used for the securities in your portfolio.
If you have chosen to maximize return for a given level of risk, this box will be labeled Max Ann. Risk w/o Shorting: the maximum annualized risk measure possible without any short-selling. - Min Ann. Return w/o Shorting: The minimum annual expected return that you can get using the securities in your portfolio without any short-selling. It is computed as twelve months’ compounding of the minimum average monthly return from the historical data used for the securities in your portfolio.
If you have chosen to maximize return for a given level of risk, this box will be labeled Min Ann. Risk w/o Shorting: the minimum annualized risk measure possible without any short-selling. - Annual Return: The annual expected return from the current portfolio, computed as twelve months’ compounding of the weighted-average monthly return from the historical data used for the securities in your portfolio.
- Annual Risk: The annualized risk measure for the current portfolio. (See Step 4, below, for a description of the available risk measures.)
 Once the Markowitz Workbook is created, you can view the monthly historical returns for the individual securities you have chosen, as well as for the current portfolio, on the Monthly Return Graph tab: this shows the returns for the entire period of historical price data available for all of the chosen securities.
Step 3: Create an Efficient Portfolio After the Markowitz Workbook is created, you need to enter a goal expected return (or goal risk level) for your efficient portfolio. This is an annual return/risk, a twelve-month compounding of the average monthly expected return/risk of the portfolio. Enter this as a number without the percent sign; e.g., enter a goal return of 25.5% as “25.5”. Once you have entered the goal return/risk, click the Create Efficient Portfolio button. An efficient portfolio is one that has minimum risk for a given return, or maximum return for a given risk. The model will compute the set of allocations for the individual securities that minimizes the risk of the portfolio while giving a return that is equal to or greater than the goal return, or maximizes the return while giving an annual risk level equal to or less than the goal risk level.. If no portfolio exists that gives at least the goal expected return, the Model will compute the portfolio with the greatest return. If there are portfolios with expected returns equal to or above the goal, the Model will compute the minimum-risk portfolio that meets or exceeds the goal return. When the Model arrives at an efficient portfolio, the label in the center of the window will change from Portfolio may be inefficient to Efficient Portfolio. The Annual Return box will give the expected annual return for the portfolio created, and the Annual Risk box will give the annualized risk measure for the portfolio created. The Portfolio Securities box will contain the dollar figures for the allocations in the efficient portfolio. Step 4: Adjust Model Options There are five sets of Model options available on the Model Options tab: Method of Optimization, Range of Historical Data, Security Holding Limits, Risk Measurement, and Benchmark Indices. - Method of Optimization: You can choose to give the Model a goal annual return and have it determine the lowest-risk portfolio for that return, or to give the Model a goal annual risk and have it determine the highest-return portfolio for that risk level.
- Range of Historical Data: This allows you to choose the amount of historical monthly price data to use to create an efficient portfolio. “Max” uses the maximum number of months for which all of the securities have (positive) adjusted closing prices.
You can choose to use less than the maximum number of months: 12, 24, 60, 120, 240 and 360 months are available if they are less than the maximum. Changing the number of months of data to use can potentially change the correlations of returns between pairs of securities, which will, in turn, change the allocation for the efficient portfolio. - Security Holding Limits: The Model will allow you to set restrictions on the minimum and maximum percentage of the portfolio that can be held in a single security.
- Minimum: To ensure that you have holdings in every one of the securities in your analysis, you can choose a minimum limit of 5%, 4%, 3%, 2% or 1% of the portfolio value; when the Model creates an efficient portfolio it will only consider portfolios that meet this minimum limit. If your account will allow you to sell securities short, you can choose Short 5%, Short 10%, or Short 15%; the Model will limit the amount of short-selling on each individual security to that percentage of the total portfolio value. As an example, if the portfolio value is $100,000 and you choose 10% shorting, the allocation for any individual security must lie between ($10,000) and $100,000. When you choose 5%, 10% or 15% shorting, another box will appear allowing you to enter the margin requirement for short-selling; this requirement will be used by the Model to determine the amount of available cash for long positions. For example, if the portfolio value is $100,000, the margin requirement is 60% and the total short position is ($10,000), there will be $104,000 in long positions. Enter the margin requirement as a number without the percent sign; e.g., enter a margin requirement of 60% as “60”.
- Maximum: To avoid having too much money concentrated in a single security, the Model will allow you to choose a maximum limit between 10% and 100%; when the Model creates an efficient portfolio it will only consider portfolios that meet this maximum limit.
- Risk Measurement: The model will allow you to choose among three measures of portfolio risk: Variance (or Standard Deviation), Lower Semivariance, and Semivariance below Target Rate.
- Variance: The traditional measure of risk in the Markowitz Model is variance or standard deviation (= square root of the variance) of historical returns over the chosen historical period. When Variance is chosen, the risk value given in the Annual Risk box is the standard deviation of the current portfolio. This risk measure typically assumes symmetric returns; with securities having asymmetric payoffs – options, for example – another risk measure may be more appropriate.
- Lower Semivariance: This risk measure looks only at returns below the average; returns above the average are given a weight of zero. This may be a more appropriate measure of risk when the returns of the portfolio are asymmetric or when the portfolio owner is more concerned with downside risk than upside risk. When Lower Semivariance is chosen, the risk value given in the Annual Risk box is the square root of the lower semivariance of the current portfolio.
- Semivariance below Target Rate: This risk measure looks only at returns below a target rate – the nominal risk-free rate, for example; returns above the target rate are given a weight of zero. This may be a more appropriate measure of risk when the portfolio owner is concerned more with principle preservation than with growth. When Semivariance below Target Rate is chosen, another box will appear allowing you to enter the target (annual) rate to be used in the calculation; once again, this number is entered without a percent sign. In this case, the risk value given in the Annual Risk box is the square root of the semivariance below the given target rate for the current portfolio.
- Benchmark Indices: To be able to compare the risk / return characteristics of your portfolio to market indicators, you can choose to include in the Efficient Frontier graph the risk / return of any or all of the following indices: the Dow Jones Industrial Average, the NASDAQ Composite Index, the NYSE Composite Index, the S&P 500 Index, and the U.S. 10-Year Treasury Note.
After adjusting the model parameters, you should have the Model recreate the efficient portfolio, as changes in the model parameters may result in a change in the allocations for the efficient portfolio. Changing some of the Model options will cause changes on the Summary tab; when you choose those options you will notice on the Model Options tab that the Apply Model Option Changes button will be enabled; i.e., it will change from gray to black. When you are finished making changes to the options, click that button to make certain that all of your changes are applied properly. Step 5: Other Options  The Model will allow you to generate a graph of the efficient frontier – all possible efficient portfolios – and to see where the goal efficient portfolio lies on the efficient frontier. Click on the Generate Efficient Frontier Graph on the Summary tab to create the graph. Depending on the number of securities in the portfolio and the speed of your computer this may take up to several minutes to complete.
Once the efficient frontier graph is created you may view the graph on the Efficient Frontier Graph tab. The graph will show the efficient frontier under the assumptions of 0% shorting and 10% shorting (with 0% margin); it will also show portfolios consisting of each of the individual securities, as well as the goal efficient portfolio. To change the securities in the portfolio, the workbook corresponding to the current portfolio must be deleted – the workbook reset – before securities are added to or deleted from the portfolio. Click on the Reset Markowitz Workbook button on the Summary tab to delete the current workbook and allow changes in the makeup of the portfolio.
|