All around the world, investors and traders and even fancy hedge-fund guys are dealing with something that denizens of the inflation-linked bond world have been dealing with for some time.
I call it the F9 problem. Please come with me as I descend into geekdom.
You would be surprised to learn how many of the world's major traders of bonds and derivatives rely for a significant amount of their analysis on the infrastructure of Microsoft Excel. While many major dealers have sophisticated calculation engines and desktop applications, nothing has yet been designed that offers the flexibility and transparency of Excel for designing real-time analytical functions on the fly. Bloomberg and other data providers have also built add-ins for Excel such that a subscriber can pull in real-time data into these customized calculation tools, which means that an Excel-based platform can be used to manage real-time trading.
When I have taught bond math, or programs like inflation modeling at the New York Society of Securities Analysts, I have had students design spreadsheets that built yield curves, calculated duration and convexity, valued vanilla derivative products, and so on. There are few better ways to learn the nuts and bolts of bond math than to build a spreadsheet to build a LIBOR swap curve. And, if you are doing anything very unique at all, being able to see and follow the whole calculation (and possibly amend or append additional calculations as necessary) is invaluable. When I was trading at two different Wall Street shops, the inflation book's risk was pulled into my spreadsheets daily and manipulated so that I could understand all of its dimensions. This is, in short, very common.
It turns out that two very important Excel functions in bond portfolio management are PRICE() and MDURATION(). And it also turns out that these functions return an error at negative bond yields. All over the world, right now, as nominal bonds in various countries are trading at negative yields, whole armies of portfolio managers are saying "why is my spreadsheet saying "#NUM!" everywhere? I call this the F9 problem because when you hit F9 in Excel, it calculates your workbook. And that's when you see the problem.
There is nothing about the price-from-yield formula that is insoluble at negative yields. The price of a bond is simply the sum of the present values of its cash flows. If using a single yield to maturity to price such a bond, a negative yield simply means that the present-value factors become greater than 1, rather than less than 1, in the future. This is odd, but mathematically speaking so what? There is no reason that PRICE() should produce an error at negative yields. But it does.
There is also nothing about the modified duration formula that is insoluble at negative yields. Macaulay duration is the present-value-weighted average time periods to maturity, which (aside from the weirdness of future cash flows being worth more than present cash flows, which is what a negative yield implies) has a definite solution. And modified duration, which is what MDURATION() is supposed to calculate, is simply Macaulay Duration divided by one plus the yield to maturity. While this does have the weird property that modified duration is less than Macaulay duration unless yields are negative, there's nothing disqualifying there either. So there is no reason why MDURATION() should produce an error at negative yields. But it does.
I don't know why Microsoft implemented bond functions that don't work at negative yields, except that, well, it's Microsoft and they probably didn't thoroughly test them.
The good news is that inflation-indexed bonds have long had negative yields, so inflation guys solved this problem some time ago. Indeed, it only recently occurred to me that there's a whole new cadre of frustrated fixed-income people out there.
Let me help. Here are the Visual Basic functions I use for the price from yield of TIPS or other US Treasuries, and for their modified durations. They're simply implementations of the standard textbook formulas for yield-to-price and for modified duration. They're not beautiful - I hadn't planned to share them. But they work. I believe they require the Analysis Toolpak and Analysis Toolpak - VBA add-ins, but I am not entirely sure of that. No warranty is either expressed or implied!
Function EnduringPricefromYield(Settlement As Date, Maturity As Date, CouponAs Double, Yield As Double)Dim price As Doubleaccumulator = 0firstcoup = WorksheetFunction.CoupPcd(Settlement, Maturity, 2, 1)priorcoup = firstcoupDo Until priorcoup = Maturity nextcoup = WorksheetFunction.CoupNcd(priorcoup, Maturity, 2, 1) If accumulator = 0 Then dCF = (nextcoup - Settlement) / (nextcoup- priorcoup) x = dCF / 2 Else x = x + 0.5 End If pvcashflow = Coupon * 100 / 2 / (1 + Yield / 2) ^ (2 * x) accumulator = accumulator + pvcashflow priorcoup = nextcoupLoop'add maturity flow and last coupon accumulator = accumulator + 100 / (1 + Yield / 2) ^ (2 * x)'subtract accrued int price = accumulator - WorksheetFunction.AccrInt(firstcoup, WorksheetFunction.CoupNcd
(firstcoup,Maturity, 2, 1), Settlement, Coupon, 100, 2, 1) EnduringPricefromYield = priceEnd FunctionFunction EnduringModDur(Settlement As Date, Maturity As Date, Coupon As Double,Yield As Double)Dim price As Doublefirstcoup = WorksheetFunction.CoupPcd(Settlement, Maturity, 2, 1)price = EnduringPricefromYield(Settlement, Maturity, Coupon, Yield) +
WorksheetFunction.AccrInt(firstcoup,WorksheetFunction.CoupNcd(firstcoup, Maturity, 2, 1),
Settlement, Coupon, 100,2, 1)accumulator = 0priorcoup = firstcoupDo Until priorcoup = Maturity nextcoup = WorksheetFunction.CoupNcd(priorcoup, Maturity, 2, 1) If accumulator = 0 Then dCF = (nextcoup - Settlement) / (nextcoup- priorcoup) x = dCF / 2 Else x = x + 0.5 End If pvcashflow = Coupon * 100 / 2 / (1 + Yield / 2) ^ (2 * x) accumulator = accumulator + pvcashflow / price * x priorcoup = nextcoupLoop'add maturity flow and last coupon accumulator = accumulator + (100 * x / (1 + Yield / 2) ^ (2 * x))/ price EnduringModDur = accumulator / (1 + Yield / 2)End Function
