Bond Immunization Model |
|
|
|
|
|
|
| An
investor wants to put together a portfolio, consisting out of a maximum of 6
bonds. |
|
|
| What
is the best combination of bonds to get the optimum yield with a given
investment time horizon ? |
| The period from
settlement to maturity is 4 years for each bond. |
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
Bond 1 |
Bond 2 |
Bond 3 |
Bond 4 |
Bond 5 |
Bond 6 |
|
Total |
| Portfolio % |
20.00% |
20.00% |
20.00% |
20.00% |
20.00% |
20.00% |
|
120.00% |
| Yield to Maturity |
8.00% |
6.00% |
9.00% |
10.00% |
7.00% |
9.00% |
|
|
| Coupon Payment |
$150 |
$100 |
$120 |
$175 |
$0 |
$125 |
|
|
| Face Value |
$1,000 |
$1,000 |
$1,000 |
$1,000 |
$1,000 |
$1,000 |
|
|
| |
|
|
|
|
|
|
|
|
| Duration
(Years) |
3.3563 |
3.52136 |
3.43042 |
3.27448 |
4 |
3.41532 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Investment Time Horizon |
|
|
3.5 |
|
|
|
|
|
Portfolio Duration |
|
|
4.19957 |
|
|
|
|
|
Portfolio Yield |
|
|
9.80% |
|
|
|
|
|
|
|
|
|
|
|
| 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? All
bonds |
| mature in 4 years and have one annual interest payment. The
annual payments, the yield and the |
| face values of the bonds are
all known |
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
| 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 |
|
|
|
|
|
|
|
|
| The
solution is the same as in worksheet BOND1. The difference is that instead of
the duration of |
| each bond being
given, it is now calculated from the yield, annual coupon and face value. |
|
|
| The
Excel function DURATION is only available if you have installed the Analysis
ToolPak add-in. If |
| you
see #NAME? on the worksheet, choose Tools Add-Ins..., click to put a check
mark next to |
| Analysis
ToolPak, then click OK. If Analysis ToolPak is missing from the list of
available add-ins, you |
| may need to
install it from the Excel or Office CD. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
