# The F9 Problem

By: Michael Ashton | Tue, Feb 3, 2015

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```

## Author: Michael Ashton

Michael Ashton, CFA
E-Piphany

Michael Ashton is Managing Principal at Enduring Investments LLC, a specialty consulting and investment management boutique that offers focused inflation-market expertise. He may be contacted through that site. He is on Twitter at @inflation_guy

Prior to founding Enduring Investments, Mr. Ashton worked as a trader, strategist, and salesman during a 20-year Wall Street career that included tours of duty at Deutsche Bank, Bankers Trust, Barclays Capital, and J.P. Morgan.

Since 2003 he has played an integral role in developing the U.S. inflation derivatives markets and is widely viewed as a premier subject matter expert on inflation products and inflation trading. While at Barclays, he traded the first interbank U.S. CPI swaps. He was primarily responsible for the creation of the CPI Futures contract that the Chicago Mercantile Exchange listed in February 2004 and was the lead market maker for that contract. Mr. Ashton has written extensively about the use of inflation-indexed products for hedging real exposures, including papers and book chapters on "Inflation and Commodities," "The Real-Feel Inflation Rate," "Hedging Post-Retirement Medical Liabilities," and "Liability-Driven Investment For Individuals." He frequently speaks in front of professional and retail audiences, both large and small. He runs the Inflation-Indexed Investing Association.

For many years, Mr. Ashton has written frequent market commentary, sometimes for client distribution and more recently for wider public dissemination. Mr. Ashton received a Bachelor of Arts degree in Economics from Trinity University in 1990 and was awarded his CFA charter in 2001.