Wednesday, 29 April 2009

Selling a 30 Year Treasury with a 4% coupon in a harsh environment

For those non-specialists in fixed-income mathematics (which includes myself) who do not have access to a more sophisticated modeling tool, Excel has a simple function to assess what would happen to prices in the secondary market for a 30 year bond with a current coupon on it, if the yield to maturity has to rise to reflect weak demand, high inflation or increasing skepticism that there is the political will to ever pay off the public sector debt - or, as could be the case, all three of those taken together.

The function is part of the financial set and called PRICE. It takes several parameters that are explained in the Help on this Function in Excel, and it is possible to see how much a long Treasury bond with a 4% coupon would drop in price if it was required to yield say 8% to maturity.

The following example is deliberately a simplification of the matter - so the professionals in this area may want to move on to another article now - in a number of respects.
Primarily the setup below assumes that the calculation of the re-sale price is being made across the whole life of the bond - whereas some time would obviously have to elapse before the required yield would have to go to the presumed 8%. However even making allowances for that the results are not dramatically dis-similar to those shown below.

Entering the following parameters into the PRICE function in Excel would produce the result for a 30 Year bond with a 4% coupon that had to yield 8% to maturity

Settlement date 15 May 2009
Maturity date 15 May 2039
Percent semiannual coupon 4.00%
Percent yield 8.00%
Redemption value 100
Frequency is semiannual 2
30/360 basis 0

The secondary market bond price for the above set of parameters would be 54.75

In other words the re-sale value of the bond would be just 55% of what was paid for it at issuance.

No comments:

Post a Comment