Fixed Income Portfolio Management – Excel Solver

Let’s try and understand something very basic in Fixed Income Portfolio Management. I am not an expert or a fund manager so it may have some subjective issues.

Bond Portfolio – Download the Sheet

Area marked in brown is to be edited. Though there is provision for only 9 instruments in attached excel, one can extend the cells to the right making a note that all formulas are then updated respectively. There are income funds which have a mandate to keep the maturity profile of the fund limited to some number. For. E.g. Birla Dynamic normally keeps the duration near to 3. So how does one keep constant watch on it? How does one optimize return with respective duration? How should one allocate his funds to enjoy maximum convexity other things (i.e. duration, yield) remaining constant. How can one optimize returns of the portfolio following risk mandates like maximum allocation to one security should not be more than 30%, maximum in Gsec should not be more than 50%, etc..

Yes excel solves every above mentioned aspect. “SOLVER” is an optimizer tool which helps you generate better results with in hand parameters.

In the attached sheet I have highlighted two tables i.e. “USE SOLVER HERE” and “SOLVER PARAMETERS”. One will have to work on the above tables once bonds/NCDs/other papers information is entered in the brown cells. Let’s try out one case which will help you understand in a better way.

 

Now using solver tool we are asking excel to return us the best portfolio allocation to get maximum yield keeping some parameters in mind. Just to understand one conditions we have specified is that $B:$30:$F$30<=$N$3 i.e. Any gilt paper should not exceed 30% of the portfolio value.

If I was not using Solver than probably would randomly allocated money to papers available keeping mandate in mind.

 

This could have been one of the results. Yield here is tad low than optimum yield possible.

Hence one performs optimum adhering to mandates of the scheme using solver.

Appreciate your inputs…

More about Kush Sonigara on Google+