Intuitive Analytics

Intuitive Analytics frees public finance analysts and decision makers from the limitations of available software.

Subscribe by Email

Your email:

Browse by Tag

Current Articles | RSS Feed RSS Feed

4 Ways to Make Killer Public Finance Debt Service Charts, Part 2


This is a continuation from Part 1 where we showed how to graph debt service and principal prior to the refinancing on the same chart as remaining and new debt service and principal.

3. Showing new deal in isolation and aggregate in outline bars

Sometimes the new financing isn't designed to wrap around the existing debt. That said, it's still important to see what the overall debt service looks like post new financing. Suppose that the new deal is level debt service. It won't look level if the solution is glommed onto the last ten deals in a lumpy existing structure.  You'll get this

Showing New Deal in Isolation, Naive Method

Showing New Deal in Isolation, Naïve Method

You want to show the new structure is level not sitting on top of existing debt.  You can do this the same way using the technique above - total debt service after the financing is reflected by clear bars with an outline on an invisible second Y-axis. The new debt is showed in color on the primary axis.

Showing New Deal in Isolation, Better Way

Showing Level New Deal With Total DS Outline

4. Show 95% confidence Line Reflecting Risk

As I've commented on extensively before, maximum annual debt service (MADS) is meaningless without some sensitivity of debt service to market risk. Break out your trusty interest rate, basis risk, credit risk risk models and put that capital structure through the ringer. Once you've done that throw a 95 or 99% worst case line on the graph to display your risk management mojo. 

Show 95% Confidence Line

Show 95% Confidence Line

Obviously, this tweak can be added to any and all of the charts above, or even added to one of those plain, old debt service charts you use now. 

Download the Excel spreadsheet and charts used for this post here.

4 Ways to Make Killer Public Finance Debt Service Charts, Part 1


Debt service charts in public finance are as ubiquitous as business cards at a shortlist presentation and date back to before Lotus 123 offered WYSIWYG. Unfortunately, they usually don’t look much different despite just a few improvements in technology over the last 20+ years. But what can you do to make a principal and interest graph sizzle? The answer is *a bunch of stuff* particularly with Excel 2007's new graphics engine. One rule of thumb (and pet peeve of ours): do not ever make your chart in more dimensions than the data.  Debt service is expressed as amount vs time – two dimensions, not three. Three dimensional bar charts, pie charts, etc. just distort the data you're trying to accurately convey. For more details on this, and definitely if you're not already familiar, read from the master of data visualization, Professor Edward Tufte. But back to debt service…

Here are four quick ideas to add information content to those debt service visuals and prove you’re still innovating, even down into the basics of the business. The first three ideas are driven off of one Excel 07+ feature which allows you to create an invisible second Y-axis. This means you can show an outline of all sorts of interesting tidbits on the same graph, improving the vital data-ink ratio. Here are some examples:

1. Show Existing Debt Service Prior to Refinancing as Black Outline

There's a new debt service solution in town - variously and affectionately named “scoop & chuck”, “pitch & toss”, or even “extend & pretend.” Rating agencies are not big fans but it's out there nonetheless. How do you show in one graph what's been scooped and what's been chucked? It's easy. Create a 2nd vertical axis in Excel with the same scale and hash marks as the 1st vertical axis, graph the original debt service on it with bars that have a black border but "no fill". This way, the black outline of the bars not only clearly shows where prior debt service was, but also how much has been refunded - it's just the difference between the top of the black bars (unrefunded existing) and the top of the outline. 

Showing Existing Debt Service Prior to Refinancing

Showing Existing Debt Service Prior to Refinancing

Here we see clearly the issuer's debt service prior to the refinancing, in the black outline, and then what remains after refinancing in black. New bond debt service components are reflected by the other colors.  

2. Show refunded principal with a white hash mark

Expanding on the example above, if you're looking to break out debt services into principal and interest, show existing principal prior to a refinancing with a white hash mark. The difference between the white hash mark and unrefunded current principal (in black) is the amount of principal that was refunded in each maturity.

Showing Existing Principal Prior to Refinancing

Showing Existing Principal Prior to Refinancing

Dark shades of a color are principal; lighter shades interest. You can see the white hash indicates where principal was prior to the refinancing. Notice that where there's no refinancing, the hash marks coincide with the top of the bars representing original principal outstanding. You can achieve this white hash again by using the 2nd Y-axis and creating a panel of principal with data bars that have "No Fill" but a white outline. 

In Part 2 we've got two more tips and a free download for the Excel spreadsheet and charts we used for both posts. Stay tuned and let us know what you think! 

Issuers BEWARE: Tax risk is NOT in LIBOR Swaps!


The other day I was perusing the swap notes in the financial statements of a big city I won’t name. In it I found a statement in the section on swaps:

“(6) Tax Risk. The swap exposes the City to tax risk or a permanent mismatch (shortfall) between the floating rate received on the swap and the variable rate paid on the underlying variable-rate bonds due to tax law changes such that the federal or state tax exemption of municipal debt is eliminated or its value reduced.” 

I couldn’t disagree more and I’m not just being disagreeable – this is flat out wrong and it’s screwing up the issuer’s financials and potentially exposing them to legal liability. The City’s LIBOR swaps absolutely do NOT expose the City to any new tax risk. The tax risk sits in the VRDBs irrespective of whether the interest rate risk is hedged with a LIBOR swap or not. Here’s an easy example that proves it…

Say there’s a tax-exempt variable rate demand bond (VRDB)No confusion
trading at 3% with zero support costs. Let’s further assume LIBOR is at 5% which means this VRDB is trading at 60% of LIBOR. If we look at an environment where the US moves to a value added tax and the preference for tax-exempt income goes to zero, ceteris paribus (my Latin teacher would be so proud), those VRDBs will start trading at 100% of LIBOR or 5%. This is a 2% increase in cost

Now, let’s say these same VRDBs had a 60% LIBOR swap in place with the issuer paying 4% fixed. Before the value added tax change, the issuer was paying a net 4% (3% VRDB rate minus 3% floating leg of swap plus 4% fixed rate). After the event, the issuer pays 6% (5% VRDB rate minus 3% floating leg of swap plus 4% fixed rate), a 2% increase in cost.

Notice that each situation both with and without the swap show a 2% increase in cost! So can someone please explain how the “swap exposes the City” to something called “tax risk”? Of course it doesn’t. The VRDBs have the risk; the swap is utterly irrelevant.  

If there’s going to be a note on tax risk, the revised and corrected version should be:

“(6) Tax Risk. The interest paid on variable-rate bonds issued by the City is impacted, in part, by investor preference for income that is exempt from federal or state tax. Therefore a change in tax law that eliminates or reduces the value of this exemption may increase the interest expense paid by the City on these bonds.” 

Frankly what these financials succeed in accomplishing is exposing the City to legal liability due to poor and inadequate disclosure. As we can see, the reality is that all VRDBs contain “tax risk.” So the fact that this disclosure is limited to only swapped VRDBs is flat out wrong and actually understates the possible impact of a tax law change because it ignores the City’s other bonds.  With the SEC making noise about municipal disclosure quality, auditors better start understanding what they’re doing and issuer’s must beware of this type of inaccuracy. 

Want to Get Hired by More Public Finance Issuers? Know Your Position


The first word in our company name means we do a lot more than calc pv savings. We often work with investment banking or financial advisor clients to come up with the clearest way to express complex analyses to tax-exempt issuers. Frequently this leads to crafting ways to convey key messages as part of a broader strategy to get hired.  What often surprises me about these discussions is how little people know about their position. Let me explain

The concept of “strategy” is inextricably interwoven with the ideaSWOT image of “position”. How could you have a strategy in checkers if you don’t know where your pieces are, or in what direction to move them in order to improve that position relative to your opponent? Yet time and time again when I ask a banker to tell me about their position with an issuer, or their competitor’s position with an issuer, they have only a vague idea if any at all. How can you execute a sales/marketing strategy without knowing your position?

What are you bringing to the table from the issuer’s perspective? A relationship of trust with senior management or the Board, balance sheet strength in the form of letters or lines of credit or other credit product, or maybe cutting edge ideas and analytics that help illuminate the tough financial decisions the issuer faces? Perhaps some combination? How do you rank in these areas relative to your peers?

If you don’t know then you don’t know your position...and you don’t have a strategy. 
All Posts
*Various products, solutions, methodologies, processes and techniques presented and/or described on this website are proprietary to Intuitive Analytics LLC, and are multiple patents pending.