Optimising Portolio of ING PPS Funds

What is ING PPS (Private Portfolio Service) Funds?

Private Portfolio Service is a master trust administered by ING. It has been developed as both a series of unit trusts and a superannuation fund, representing a broad range of asset classes and fund managers.

Private Portfolio Service provides investors with the opportunity to diversify their portfolio, without the need to rebalance different investments across a number of fund managers and products.

Someone definitely has to do a quantitative research on these funds, right? What's the purpose? To find the best and optimised alocation of a portfolio of one's retirement plan or any investment plan.

Based on their daily/monthly historical prices, I calculated the daily/monthly log return of each PPS fund, Ln(P2) - Ln(P1) to show the continously compounded return.

I then calculated the mean and standard deviation of each fund and built the covariance matrix. Next, I calculated the portfolio mean and standard deviation.

Finally, using solver, the optimised allocation is found by assuming risk free rate 7%. Contact me to get the optimised allocation and the spreadsheet, if you're curious or just checking with your own calculation.

Please confirm if these annualised monthly means and standard deviations are close with your calculation for data ending 21 Jan 08.

PPS	mean	stdev
AggBal	7%	0.087
AsianEq	10%	0.159
AusEq	7%	0.126
Bal	5%	0.063
DivTrad	3%	0.041
EuroEq	6%	0.138
GlobOpp	-1%	0.176
InterEq	5%	0.131
InterFix	3%	0.029
Mortg	1%	0.018
NZEq	5%	0.098
NZFixed	0%	0.021
PlatInter	1%	0.101
Prop	6%	0.080

Some notes

Sorry, I didn't use CAPM return in this calculation. No market index benchmark therefore no beta and risk premium to consult. :-)

This is an optimisation of a portfolio of optimised funds as a managed fund is a portfolio which is optimised regularly by its fund managers.

Putting into practice, some clients don't believe in this simulation. This is because the simulation is based on the daily prices and they don't concern this kind of time horizon.

For getting the best point of view, in my opinion, it is best simulated from monthly prices, not daily prices.

A Noisy Market Siren

The idea is to automatically activate a noisy siren from a sound file when opening the excel file as the market index value drops to, or is below, the minimum expected value. The market index value is refreshed every 30 minutes.

Eg. the index value alert is 4,300. If the market index value is below 4,300, the noisy siren will be automatically activated.

I first set a WebQuery for the market index latest price with automatic refresh every 30 minutes. Then, apply VBA macro to call a sound file.

I modified the VBA codes taken from ExcelTip.com and J-Walk.com as follows:

Public Declare Function sndPlaySound Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
-------------------
Sub PlayWavFile(WavFileName As String, Wait As Boolean)

If Dir(WavFileName) = "" Then Exit Sub
If Wait Then
sndPlaySound WavFileName, 0
Else
sndPlaySound WavFileName, 1
End If

End Sub
-----------------------------
Sub PlaySoundAlert()

For i = 1 To 5

PlayWavFile "c:\WINDOWS\Media\notify.wav", True

Next i

End Sub
-----------------------------
Function Alarm(V)

x = Range("alert")

If V <= x Then

Call PlaySoundAlert

Alarm = True
Exit Function
Else
Alarm = False

End If

End Function

Portfolio Optimisation (Shares & Funds)

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]