• 202 days Could Crypto Overtake Traditional Investment?
  • 207 days Americans Still Quitting Jobs At Record Pace
  • 209 days FinTech Startups Tapping VC Money for ‘Immigrant Banking’
  • 212 days Is The Dollar Too Strong?
  • 212 days Big Tech Disappoints Investors on Earnings Calls
  • 213 days Fear And Celebration On Twitter as Musk Takes The Reins
  • 215 days China Is Quietly Trying To Distance Itself From Russia
  • 215 days Tech and Internet Giants’ Earnings In Focus After Netflix’s Stinker
  • 219 days Crypto Investors Won Big In 2021
  • 219 days The ‘Metaverse’ Economy Could be Worth $13 Trillion By 2030
  • 220 days Food Prices Are Skyrocketing As Putin’s War Persists
  • 222 days Pentagon Resignations Illustrate Our ‘Commercial’ Defense Dilemma
  • 223 days US Banks Shrug off Nearly $15 Billion In Russian Write-Offs
  • 226 days Cannabis Stocks in Holding Pattern Despite Positive Momentum
  • 227 days Is Musk A Bastion Of Free Speech Or Will His Absolutist Stance Backfire?
  • 227 days Two ETFs That Could Hedge Against Extreme Market Volatility
  • 229 days Are NFTs About To Take Over Gaming?
  • 230 days Europe’s Economy Is On The Brink As Putin’s War Escalates
  • 233 days What’s Causing Inflation In The United States?
  • 234 days Intel Joins Russian Exodus as Chip Shortage Digs In
  1. Home
  2. Markets
  3. Other

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

 

Back to homepage

Leave a comment

Leave a comment