The F9 Problem
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, Coupon As Double, Yield As Double)
Dim price As Double
accumulator = 0
firstcoup = WorksheetFunction.CoupPcd(Settlement, Maturity, 2, 1)
priorcoup = firstcoup
Do 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 = nextcoup
Loop
‘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 = price
End Function
Function EnduringModDur(Settlement As Date, Maturity As Date, Coupon As Double, Yield As Double)
Dim price As Double
firstcoup = 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 = 0
priorcoup = firstcoup
Do 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 = nextcoup
Loop
‘add maturity flow and last coupon
accumulator = accumulator + (100 * x / (1 + Yield / 2) ^ (2 * x)) / price
EnduringModDur = accumulator / (1 + Yield / 2)
End Function
Leave a Reply
Dear Michael Ashton,
Your code works fine and the answers match with Excel’s Price formula for bonds that have a remaining maturity of more than 6 months. However, where the remaining tenor is less than 6 months, the answers from Excel’s Price formula and your code are different, albeit after 2 decimal places only.
The above comparison is of course based on positive yields. Can you kindly reconcile the above and advise why there is a difference?
Thanks.
It appears that inside of 6 months, Excel uses simple interest in its discount function rather than compounding (so where I do ^(2*x) they simply make the discount factor 1+ yield * x). If you incorporate a conditional statement where you change that factor if the maturity is 6 months or less, this will match Excel.
That’s not the way that the classic texts do it, but it’s the way Bloomberg does it so probably worth making that adjustment. Thanks for pointing it out.