This page shows how POW! Frontier Wizard performs portfolio optimisation in Excel. To see the screen shots clearly, click on them to download the full size image.
- Use POW! Frontier Wizard to define the model
- Enter Universe Parameters
- Set Portfolio Particulars
- Optimise
- Display Results
- Graph Results
- Customising and Extending POW!
1. Use POW! Frontier Wizard to define the model
POW! Frontier Wizard displays a series of dialog boxes in Excel in which you set the structure of your model - number of assets, types of factors, currencies, transactions costs etc.
When you have finished, POW! Frontier Wizard will create a new optimisation workbook matching your requirements. You can then go ahead and enter the particulars of your problem.
2. Enter Universe Parameters
Universe data is all the data that defines the properties of the assets - return alphas, risk matrices, factor betas and whatever else your model includes. This data is laid out in a series of worksheets like the one shown below.
Some information, like risk matrices, can be pasted directly into the sheet from wherever it happens to be; if you only have raw timeseries, then POW! Toolbox can convert them automatically. Elsewhere the built-in Helpers will lend a hand. In the illustration below, a Helper dialog is being used to assign a currency factor to an asset in a cross-country model
3. Set portfolio particulars
Portfolio settings, as opposed to universe data, describe the specific problem that you wish to solve, and consist of such things as the benchmark weights, holdings limits, current portfolio weights (relevant for transactions costs) and general constraints such as minimum industry exposure or maximum turnover.
You can attach several portfolios to the same set of universe data, e.g. to investigate the effect of different constraints, or to process a set of client portfolios in a single session.
4. Optimise
When you have finished, select Optimise from the Excel menu and the problem will be passed to the Optimiser, which will calculate the entire efficient frontier.
5. Display Results
The results from the Optimiser can be displayed in worksheets like the one shown below.
You can see portfolio weights at your chosen points along the efficient frontier together with their associated risk and return. The figures for the portfolio benchmark and initial holdings are also displayed for comparison. As well as the figures displayed here, POW! can calculate statistics like the factor contributions to risk and return.
6. Graph Results
POW! Frontier Wizard can create a number of charts from the optimisation results, such as the two examples below. The upper one shows how the portfolio weights change along the efficient frontier (the first two columns show the benchmark and initial holdings weights).The lower one shows the characteristic curves of a pair of efficient frontiers plotted in relative risk-return space.
7. Customising and Extending POW! Frontier
POW! reports are easily customised to provide management with concise summaries highlighting important features; and high quality graphs and reports can be tailored for individual client presentations. The results from the POW! Optimiser are accessed by means of a set of Excel worksheet functions, so getting the figure you want in a report is as simple as entering the formula in the cell where it is required.
If you already have a risk model or similar software, POW! can draw on its data, helping you to explore its features and extend its potential.
POW! Frontier can itself be extended. If you wish to perform repetitive optimisations, or have specialised applications that cannot readily be handled within a spreadsheet, you can use POW! Frontier Automation to access the Optimiser directly using Visual Basic. POW! has been used in this way for tasks such as processing multiple mandates and analysing credit portfolios. Now Try Yourself ! Click here to find out how to get a free demonstration copy of the software.