Markowitz Portfolio Optimizer

(1)    The name of the VBA macro is Optimizer. Please feel free to modify the code. I have put my name in the header. This is header of the VBA code.

'Written by Satendra Tiwari. Hoping that some one would improve this code

'This code works with 4 assets but more number of assets can be added easily

File can be downloaded from here :-

http://www.4shared.com/file/CtEc0A2u/MVOptimizer.html?

(2)    This code takes 4 assets but it could be modified for more number of assets

(3)    The Inputs are (a) Minimum and maximum weight for optimization. (b) Number of points to be generated on the efficient frontier (c) Assets mean returns and standard deviations

(d) Correlation matrix

       (4)  This program accounts for “shorting of the assets” so you can optimize a given portfolio assuming that you can short some assets. Some of the weights then would be negative.

       (5) If you do not want to short any assets then put the minimum weight as 0.

      (6) This workbook has three sheets –(a) Input – contains all the input related info (2) Calculations – contains all the intermediation calculations done by the optimizer (3) Plot – shows the plot of the efficient frontier.

     (7) Also note that maximum return on the frontier is calculated as->  1.6 times max of all the means.

    (8)  As you go through the code, you will notice that -the logic is to find the global minimum and then using that minimum calculate the other points on the frontier.

    (9) This program uses MS Solver. I am also attaching the doc file to configure the solver to run this macro.

http://www.4shared.com/file/SLhVqLX7/Configure.html?

     (10) Please note that default  solver uses generalized reduced gradient method to come up with optimum values. 

  (11) Please be aware that frontier would be very sensitive to the inputs – mean and std deviations.  And in real world there would be some errors while estimating the means and std deviation (especially for  the thinly traded assets or the assets that are not traded at all)…  I think that it would be a good idea  to incorporate the capital market expectations and then simulate(Monte Carlo sims) the returns for a given level of risk. This would produce the probability distributions of the returns for a given level of risk.

Macro is password protected. Mail me at my mail id to get passwd for VBA code!