It's quite a complex portfolio optimisation model which aims
to find the optimised fund allocation in a portfolio of Australian and NZ managed funds with future return simulation using MonteCarlo method.
The historical prices are provided by Morningstar NZ and the copyright of this model is belong to Lyfords Ltd which I work with.
The optimisation is done by maximising the tangency portfolio (theta) so that it touches the edge of the efficient portfolio from the constant. There are also additional constraints such as particular conditions for any particular fund allocation. (eg. Thoroughbred Cash Fund = 5% and PPS Int Fixed Interest less than 10%)
If it applies into managed funds instead of shares, this may be an optimisation of a portfolio that may have been optimised (by fund managers) as a managed fund is in fact an optimised portfolio.
The process as follows:
1. Gathering data input: historical monthly fund prices. Calculate the historical monthly mean and stdevation of each asset.
2. Conduct a MonteCarlo simulation for monthly returns through 3 years horizon (36 months) using normal distributed random numbers using the historical montly mean and stdev at random probability.
3. Simulate the whole 3 years random movement. If simulated 10 times, so there are 36 monthly simulations times 10 three-yearly simulations (360). Record the projected mean and stdev.
4. Find the simulated portfolio return and risk using the projected mean and stdev.
5. Find the optimised portoflio expected return and risk.
6. Solve the optimised asset allocation using solver VBA. Apply additional constraints to reoptimise.
7. Calculate the value-at-risk.
Option to use mean or CAPM return is to choose whether CAPM is believed to be effective or not in this optimisation. If mean return is used then beta and risk premium don't matter.
Covariance matrix is also to be projected based on the simulation result.
Option to use historical or projected standard deviation is to choose whether portfolio risk is believed to be accumulated in the simulation result.
To run the Solver VBA.
1.
Tools > Add-ins to have the Solver add-in first (in a file called Solver.xla)
2. Then create a reference to the '
Solver.xla' in Visual Basic Editor.
Go to
Tools > Macro > Visual Basic Editor, choose
Tools > References and check the box for
Solver.
If it is missing, just click browse and manually find the "
Solver.xla" file wherever it is located in your computer.
[Sorry, it's a premium model]
