Share Portfolio Optimiser v1.0

 

 

About the Model

The optimal portfolio theory was developed in the 1950s by Harry Markowitz who won a Nobel Prize for his work. The theory is based on the efficient market hypothesis where the optimal portfolio can be found graphically as a tangent to the risk-free interest rate.

 

Initially the variances and covariances of all products in the portfolio are calculated. Random weighting scenarios of these products are then generated to find the combination with the highest return for the lowest risk. Risk is a function of the product's historical volatility.

 

To find this optimum point, the maximum Sharpe Ratio is calculated, where:

 

Sharpe Ratio = (Portfolio Return - Risk Free Rate) / Portfolio Standard Deviation

 

To run the model, choose the share names in the ‘Input’ sheet. Up to 30 shares can be chosen from different markets. However, the program will take longer to run is you choose more shares. You also need to choose a time horizon that historic data will based on. The model will then download the necessary data from the internet.

 

At completion, the optimal portfolio with the weightings of each share are displayed in the ‘Outputs' sheet.

 

 

System Requirements

Windows 98/Me/NT/2000/XP/Vista or later and;

Microsoft Excel version 2000 or later.

 

 

About the Author

Excel Specialists have created this model.

 

We can customize this model further in order to meet your needs, for example

 

- We can expand the stock selection to include other markets (e.g. Japan, France etc)

- We increase the number of shares that you can include in your portfolio

- We can increase the time-horizon over which the historic data is analysed

 

For more details please see our Product Adaptations page.

 

 

Disclaimer

The information generated and included in the above model is for illustrative purposes only. We do not guarantee the results or whether they are applicable to your own unique financial situation. We strongly advise that you should seek the advice of qualified professionals regarding any financial decisions.