The aim of this Analysis Toolbox is to produce a model to help with decision making relative to investments in equities and bonds. This paper is produced as a first brief exposition to help understand the ideas & techniques behind the model. We use the Markowitz theory to calculate the percentage of the total fund that can be invested in each of five firms. The model can of course be extended to as many firms as the investment situation demands.

Image one

Image One shows us the initial excel spreadsheet. The five stocks are listed across the top, each having a fifth share initially. The expected returns are derived from the Price Changes table. For example the figure in B4 of -0.00848 comes form =AVERAGE(B23:B32) etc. The figures in the Price Changes table were obtained manually from Yahoo Finance. The ToolBox release will have VBA download code. The diagonal of the Variance/CoVariance table utilises the VARP function (e.g. B11 =VARP(B23:B32)) This function is described in the technical notes, but understanding is not required for use of the ToolBox.

The aim of the Markowitz analysis is to minimise the risk for the targetted rate of return. In Excel we have the solver tool which can accomplish this task for us.

Image Two

Images One and Two show the before and after of solver use. In Image One we see the target cell as I19 (Return), cell changes B6 to F6, which are he percentages for each firm in the portfolio, and the Constraints which are fairly self explanatory. In Image Two we have executed the solver procedure and obtained the result that the minimum risk for the targetted return will be achieved by holding 100% RR.L shares.