VIDEO: Using Excel to Size a Muni Bond Deal

Posted by Peter Orr on Thu, Aug 30, 2012 @ 08:00 AM


The public finance business is a mystery to the vast majority of the population. People have usually heard of municipal bonds before and may have some vague sense that it has something to do with dodging taxes but beyond that - what pays them back, how they come into existence, who sells them (big bad banks), buys them (rich people?) and what they finance is clouded in a dense fog. The video here shines a bit of light on one tiny piece of that market - how to size and structure a muni bond deal using Excel. In the process it details the little bit of Excel formula magic that every aspiring bond structuring artist must understand in order to evolve into a true IB muni bond quant.  Let us know what you think!

ps You can download the spreadsheet built in this video HERE.

Tags: financial model, Spreadsheets, municipal finance software, debt structuring, bond sizing

Are munis over-hedged with swaps? pt2

Posted by Peter Orr on Sun, Aug 09, 2009 @ 08:54 AM

"Our lives improve only when we take chances - and the first and most difficult risk we can take is to be honest with ourselves."

- Walter Anderson

Although it may not look related out of the gate, this post is a continuation of the prior post on LIBOR swaps over-hedging tax-exempt variable rate bonds. I want to start by looking at how one might build a reasonable interest rate model that would facilitate calculating this % LIBOR correctly, so that we expect to minimize the volatility of our synthetic fixed rate structure. Let's say your job is to build an interest rate model that captures the uncertainty inherent in SIFMA and LIBOR. This would be an unusual task for "quants" in public finance whose primary responsibility is coming up with accurate and often elaborate variations of present value ideas. The "model" used most frequently among front-line in investment bankers/advisors in this sector and in part due to an overreliance on spreadsheets looks something like this:


Some historic average over a selected time-period is used to create a static, flat, deterministic assumption for short rates over the time horizon of the analysis. This IS a type of interest rate model no doubt though one whose strength is not in capturing uncertainty/variability.

If an analyst were trying to create a SIFMA and LIBOR market model using two risk factors, perhaps un-intuitively s/he would not want to use "SIFMA" and "LIBOR" as the risk factors themselves. A detailed reason why is beyond the scope of this post (though you can find an outstanding thorough treatment here), but to put it simply, too much of the variability in SIFMA is also present in LIBOR. Let's face it, as US$ denominated short term interest rates, both SIFMA and LIBOR will be driven largely by changes in US monetary policy.

The better choice for a 2 factor model is LIBOR and SIFMA/LIBOR ratios. SIFMA/LIBOR ratios better reflect the unique component of risk in SIFMA itself i.e. the taxable/tax-exempt relationship. But how does this relate to the correlation impact on swap structure mentioned in the first post? It turns out that historically and on average, as LIBOR falls SIFMA/LIBOR ratios tend to go up and vice versa. In the industry vernacular bankers call this "yield compression" and it has a number of reasonable economic and technical explanations.

Scatter of 1M LIBOR vs SIFMA/1M LIBOR jul89 to jan09

How do we capture this in a two-factor interest rate model that doesn't take a PhD to understand? For details on that you can read this and/or get a spreadsheet example. Suffice it to say, it really isn't so bad. To ultimately answer the original question, does this inverse relationship between rates (LIBOR) and ratios (SIFMA/LIBOR) impact the *right* percentage of LIBOR to use when hedging tax-exempt variable rate bonds? Absolutely. The graph below shows the LIBOR swap % that minimizes debt service volatility at different levels of expected correlation between LIBOR and SIFMA/LIBOR ratios.

Swap % LIBOR vs Expected Correction Minimum Risk Solutions

The bottom line is that using simple averages for this LIBOR swap hedge calculation does 2 things: a) ignores the fact that these structures are not equivalent to fixed rate bonds, a fact that's been sometimes painfully understood over the last 18 months and b) implicitly assumes a correlation of zero between rates and ratios which leads to a hedge ratio that is too high, and ultimately more LIBOR swap than is necessary. What are the cash flow and mark to market ramifications of this over-hedging? Stay tuned for the 3rd and final installment on this topic. In the meantime and if you're involved in the biz, how do YOU do this calculation?

Tags: Spreadsheets, SIFMA, Tax and Accounting Issues, libor

Behavioral Finance, Spreadsheets, and Bad decisions

Posted by Peter Orr on Sun, Jan 04, 2009 @ 11:15 AM

"The whole secret of mysticism is this: that man can understand everything by the help of what he does not understand. The morbid logician seeks to make everything lucid, and succeeds in making everything mysterious."

- G.K. Chesterton

Behavioral finance has been heralded as at once a new sunrise and false dawn in the annals of financial economics. However, behavioral finance has no unifying theory at this point though it has exposed a number of "cognitive illusions" which we human types tend to display when making financial decisions. And as often as those would claim that insights from behavioral finance sound the death knell for the efficient market hypothesis, others say it's impossible to determine whether the market is truly inefficient or that the market model being tested is wrong. Since behavioral finance offers no model of its own, it's impossible to test market efficiency under its finding. I wouldn't presume to add any real insight into this debate; I say let the debate rage on and a thousand more PhDs be granted. That said, I do question how or if the financial technology we surround ourselves with has been a contributor to our current situation…

Some behavioral finance findings relate to heuristic decision-making, the "rules of thumb" or educated guesses that we make in the face of complicated problems and uncertainty. For example, availability bias is the tendency towards overweighting information that is easily attained. Anchoring is the tendency towards extrapolating recent trends, possibly leading to an under-reaction to changing conditions. Overconfidence leads people towards over-estimating their predictive skills. Each of these three phenomena has been studied and documented as common in the human condition; even evolutionary psychologists have reasonable theories for some of these behaviors. But so what?

Consider these findings as they interrelate with our technology. The one nearly ubiquitous tool available to the masses in finance is the spreadsheet. I love spreadsheets. Spreadsheets can't be beat for certain purposes. However, I find that for measuring potential variability in a multi-factor risk setting, unenhanced spreadsheets display some pretty major shortcomings which I won't belabor here. Suffice it to say that in the absence of any other tool to more powerfully process information, if a spreadsheet is all that's available, an analyst will use a spreadsheet. People are forced to make a decision with what they've got, so often the only information that goes in is the stuff that can be reasonably quickly generated in a spreadsheet. Further, our natural inclination towards anchoring with recent data, as well as natural overconfidence in forecasts makes the spreadsheet the ideal medium for us to completely delude ourselves.

A MAD Example

Let me give you just one (of many) examples that frankly doesn't make any sense to me, particularly in this modern financial era. One liability related metric understandably deemed important by many analysts and certainly the rating agencies is MADS, or Maximum Annual Debt Service. It is supposed to represent the maximum of principal and interest payments that might be made by an issuer of debt over an annual fiscal cycle. It is one of those metrics that can be easily calculated in a spreadsheet by a user with only modest skills. However, this same user and likely worse, the audience for her/his analysis may be suffering badly from the cognitive issues described above.

The number of misunderstood, under-appreciated, and heroic assumptions that go into calculating MADS can be staggering. What assumption was used for calculating possible debt service on variable rate bonds or commercial paper? How about the performance of hedges such as interest rate swaps? Any basis variability? How about the likelihood of debt acceleration? Or perhaps a liquidity crunch which leads to either expensive or unavailable letters or lines of credit? What happens to debt service and MADS then? What does the "maximum" in MADS mean when all of the market assumptions that go into it are based upon some 10 or 20 year average, whose sole redeeming feature is that it's easily entered in a spreadsheet?

And MADS is an important number because it often is the denominator for statistics like debt service coverage ratios which are relied upon by investors, rating agencies, and even bond trustees. These are legally required calculations, and yet the amount of time and energy that goes into understanding their potential variability is often next to nothing.

IMHO, this is low hanging fruit which must be changed if we're going to improve disclosure, increase the value of our information and the density of its content, and ultimately enable people to make better decisions. What am I missing? What do you think?

Tags: financial economy, Financial Decisions, Behavioral Finance, Financial Technology, Spreadsheets, Maximum Annual Debt Service (MADS)