|
Solver.com
From Frontline Systems, developers of the Excel Solver.
|
|
|
|
|
Bond Immunization Model |
|
|
|
|
|
|
| An
investor wants to put together a portfolio, drawing from a set of 5 candidate
bonds. |
|
|
| What
is the best combination of bonds to get the optimum yield with a given
investment time horizon ? |
|
|
|
|
|
|
|
|
|
| |
Bond 1 |
Bond 2 |
Bond 3 |
Bond 4 |
Bond 5 |
Bond 6 |
|
Total |
| Portfolio % |
20.00% |
20.00% |
20.00% |
20.00% |
10.00% |
10.00% |
|
100.00% |
| Duration
(Years) |
2.8 |
3.1 |
3.7 |
3.5 |
3.8 |
4 |
|
|
| Yield to Maturity |
8.00% |
6.00% |
10.00% |
9.00% |
8.00% |
5.00% |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Investment Time Horizon |
3.8 |
|
|
|
|
|
|
|
Portfolio Duration |
3.4 |
|
|
|
|
|
|
|
Portfolio Yield |
7.90% |
|
|
|
|
|
|
|
|
|
|
|
| Problem |
|
|
|
|
|
|
|
|
| An
investor wants to put together a portfolio consisting of up to 6 different
bonds. To minimize risk of |
| loss of principal value due to interest rate fluctuations
and to assure enough cash-flow at a certain point |
| in
the future, he wants to make sure that the average duration of the bonds
equals his investment time |
| horizon. How should the investor choose his
portfolio to optimize the combined yield of the bonds, |
| while
making sure that the duration of the portfolio equals the investment time
horizon? The duration |
| and the yield
to maturity are known for each bond. |
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
| Solution |
|
|
|
|
|
|
|
|
| 1)
The variables are the percentages or fractions of our available funds to
invest in each bond. In |
| worksheet BOND1
these are given the name Portfolio_fractions. |
|
|
|
|
| 2) The
constraints are very simple. First we have the logical constraints: |
|
|
|
| |
Portfolio_fractions >= 0 via the Assume Non-Negative option |
|
|
| |
Portfolio_total = 1 |
|
|
|
|
|
|
| Then there is the constraint to make sure that the
portfolio duration equals the investment time horizon: |
| |
Portfolio_duration = Time_horizon |
|
|
|
|
|
| 3) The
objective is to maximize the portfolio yield. This is given the name
Portfolio_yield |
|
|
| |
|
|
|
|
|
|
|
|
| Remarks |
|
|
|
|
|
|
|
|
| In this model we assume that the duration of the bond is
known. In worksheet BOND2 we will see how |
| to use EXCEL's
build-in functions to calculate the duration of each bond. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
