Setting Up the Inputs
Help for setting up the inputs in the portfolio optimization template for Excel
The first step is to input the required data into the Input sheet or download financial security data using the separate template provided. Additional input options are:
- Modify Correlation Matrix. Use the option to modify the correlation matrix and portfolio dynamics before optimization. Any input data will be calculated to preload this information. Modification to the correlation matrix can be useful for modeling predicted or forecast returns or performance for the portfolio of investments.
- Data Type. Specify whether the input data is price data or return data. All price data should be greater than zero. Percentage return data should be entered as whole numbers of percentage points (e.g. '5.6' should be entered to represent 5.6% or 0.056).
- Risk. Risk can be evaluated via several alternative methods:
- Overall Deviation: all volatility of the portfolio is used for optimization under the Sharpe ratio.
- Downside Deviation: only volatility below the average is used for optimization under the Sortino ratio. This method focusses on minimizing downside risk.
- Semi-Deviation: only volatility below the target return level is used for optimization under the Sortino ratio. This method focuses on minimizing risk below a certain level.
- Capital. A nominal capital investment amount can be used to set all starting investment weights to equal values. This can be useful for testing potential portfolios that have not been invested.
- Risk free Rate. This is the risk-free interest rate that corresponds to the frequency for which historical observation data is entered further down the worksheet. The equivalent government bond rate can be used here.
- Target Return. The target return level is used to display a probability of reaching this level in both the current and optimized portfolios via Monte Carlo simulation. The target return is also used for semi-deviation under the Sortino ratio and Gain/Loss under the Omega ratio. The target return should correspond to the periodicity of the input data. For example, if monthly data is entered below, this should be the target annual return divided by 12.
- Position. The position strategy for the portfolio optimization can be All Long, All Short or Long/Short mixed. Long assumes that all investments are bought and later sold, while Short assumes that all investments are sold short and later bought back. Long/Short mixed optimization includes both long and short strategies for each investment in the portfolio. Returns and technical analysis results are calculated according to the position strategy selected.
- Result Sets. The optimization process saves possible portfolios along the extremities of the efficient frontier. Pivotal profiles for minimum and maximum return, risk, and ratios can be subsequently loaded for analysis. Furthermore, portfolios that possess specific risk and return profiles suitable to desired investment strategies along the efficient frontier can be identified and selected as custom result sets. The initial result set is loaded by default after the optimization has been completed and alternative result sets can be subsequently loaded without requiring to run the optimization again.
- Min and Max Constraints. Minimum and Maximum constraint weightings can be used to bias the optimization process in line with benchmark guidelines or limitations. This can be used where there are liquidity limitations or obligations to hold products such as short sale positions. A percentage between 0 and 100% should be entered here above each product or business unit data.
- Current Units. The current number of units held in each product or business is entered in this row. This is used to calculate the current portfolio weightings by applying the last observations' price. Long positions are represented by positive units and short positions by negative units.
- Historic Price Data. Enter Product or Business names in this row. Up to 100 products or businesses can be entered for optimization. Underneath this, price or percentage return data (in whole numbers of percent) should be entered under each product or business. The number of observations is unlimited but must be the same for each product or business. An optional benchmark can also be included in the benchmark column for comparison purposes in the results. Two common input types deserve special attention:
- Business Streams. When applying the model to a portfolio of businesses, the historical economic profit or return of each business unit should be entered here. Because the model assumes that each business is independent, effort should be made to allocate overhead costs to each business as much as possible. This can be accomplished through methods such as Value Stream Mapping.
- Financial Market Data. To assist with the automatic download of publicly listed market security data, Business Spreadsheets has developed a free and open source template (included in the portfolio optimization download) which can download this data automatically at daily, weekly or monthly frequencies. The format of this template is identical to the portfolio optimization 'Input' sheet so that downloaded data can be automatically copied and pasted in.
- Historic Observation Periods. The dates corresponding to the price data entered for each product or business can be entered here. Note that the time interval for observation price data must correspond to the risk-free rate and target return percentages entered above.
- Rolling Optimization. The rolling optimization feature allows to specify a rolling time period and periodicity for which optimizations are run and results consolidated into a separate workbook. Rolling optimization analyzes the performance of each optimization after it has been made and until the next one. This serves to evaluate the effect on the portfolio if optimizations had been made at periodic intervals. Back periods for each optimization represents the number periods to be used in each optimization process. The period gap between each optimization represents the number of periods between each optimization result and the total number of optimizations is calculated and displayed.
Screenshot: Setting Up the Inputs
){kind=link}