The Paired Waterfall Chart
I came across a new (for me) style or waterfall chart last month, which I call the paired waterfall chart. It is designed to show how an investment fund’s total holdings, comprised of stocks or other vehicles and cash, changes during a time period, based on changes in value of its stocks and on proceeds from the sale of some stocks.
Here is an example of such a chart. The holdings of the fund totaled 1350 in 2015: 1100 in stock plus 250 in cash. The holdings of the same fund in 2016 totaled 1450: 675 in stock and 775 in cash. Stock A didn’t change in value. Stock B appreciated by 50 in value. Proceeds from the sale of some Stock C were 275, while the fund’s total value of Stock C declined by 250; the difference of 25 must have been additional appreciation of Stock C. Sales of some Stock D resulted in proceeds of 250 and corresponding loss in value of 250. Stock E went down 25 in value, while Stock F went up 50.
There is no provision for taking cash and buying more of a stock already in the portfolio or a new stock, so the upper set of floating bars are green only, that is, you can only gain cash. The lower bars can be red or green, but the value of your stock holdings can never drop below zero, because at worst the stock is worth nothing. These constraints make the chart slightly easier to build than a standard waterfall, though the horizontal and slanted lines are trickier than in the standard one.
For want of a better name, I call this the “Paired Waterfall Chart”. In hundreds of images of waterfall charts that Google found for me, none had this same structure. I tried to crowd source the chart’s name on Twitter, but the only suggestions that weren’t lame attempts at humor were “stacked waterfall” and “cascade”, and these refer to other chart types. If you know what this chart is really called, or if you have some examples you can share, please let me know in the comments below this article.
How To…?
Since I’ve never seen this kind of waterfall, and since nobody asked me how to make one, I have not prepared a tutorial. But someone did ask whether my charting software, Peltier Tech Charts for Excel, could make these charts. The answer was, not yet, but with the thought of perhaps selling a few licenses, I took on the task. So…
Paired Waterfall Charts in Peltier Tech Charts for Excel
The Advanced Edition of Peltier Tech Charts for Excel now includes a Paired Waterfall Chart feature, and that’s what I used to make the chart at the top of this article. The data consists of three columns of data, with labels for the horizontal axis, entries for value of the holdings, and entries for proceeds due to any sales. The top row consists of labels, and the totals in the bottom row are omitted, because the program can add better than you can.
If you are a licensee of an older Peltier Tech utility, let me know and I’ll send a discount coupon code so you can upgrade at a discount. And you should upgrade, because Peltier Tech Charts for Excel works in Microsoft 365, and the same add-in file works on both Windows and Mac.
Like all of my utility’s charts, it is easy to make the Paired Waterfall Chart. With the data selected, click the Waterfall dropdown, then click the Paired Waterfall Chart button.
This very simple dialog comes up.
Click OK, and the program generates a new worksheet with a bunch of formulas in a nice Excel Table, and the Paired Waterfall Chart (click on the image to see it full-sized).
I’m gradually transitioning as many of my program’s chart output sheets to use Excel Tables, because they make it easier for users to adjust the chart’s data after they’ve already built the chart. Tables will also make it easier when I finally come out with Peltier Tech Charts for PowerPoint and Word. The structured referencing in the tables can be a challenge, but Zack and Kevin’s book about Excel Tables helped a lot.
Sharon says
Hi,
Based on other postings both from you and Chandoo I created a stacked waterfall chart almost exactly as you show here I use a dual Axis to achieve the correct height however and have not been able to master automation of the Maximum.. some time ago I requested assistance with writing into the code you provide for Axis Max you advised I should consider hiring someone. Please consider adjusting the Macro code you use for this functionality to apply to your waterfalls or a dual axis waterfall…
Jon Peltier says
Sharon –
I can’t seem to find that earlier comment. What exactly is the problem you’re having with the maximum? Do you need different scales on two vertical axes? If not, delete one axis and the other will fit to all the data.
Jon Peltier says
Ian wrote to me offline:
I’m not a huge fan of waterfall charts but was drawn to yesterday’s article on paired waterfall charts. Very interesting and well done with the quick update to your utility.
But I don’t get the x-axis: in the example you show, surely the two green boxes shouldn’t be aligned with values greater than 1450? Just looks odd. I think it works well without the x-axis.
Just a thought
I’m not sure why the X axis is the axis you have the problem with.
The way the chart works is by taking two components of a total, and making a waterfall chart of each.
Ordinarily you might end up with something like this, with the final bars both starting at the horizontal baselines defined by their respective initial bars. There is a gap because much of the decrease in the lower bar was captured by gains in the upper bar.
We can duplicate the two separated final bars in the next category, but reset the baseline of the upper bar to the top of the lower bar, eliminating the gap and showing the two final bars in a combined total bar.
The paired waterfall simply skips the totals with the top bar floating well above the bottom bar, and goes straight to the view with the upper bar seated atop the lower bar.
Vinodh Sudhakaran says
That’s an amazing thought process in getting the chart done. Cant think of a better name too!!
Although am also looking for a similar waterfall chart but with a minor difference – not paired but something that can help compare two different objects for two different years.
My initial thought was go with the simple waterfall of taking the full value of year 1, and sum/minus up the values by the components to project the full value for year 2. But I should say, after looking at this chart it should be possible to get the comparison performed by components for year 1 and year 2.
Do you think if we can get it done?
Jon Peltier says
Vinodh –
There are a couple approaches here.
You could plot both years in the same space, shown in the first chart below. The 2017 data is in shaded bars and 2018 in heavily outlined unfilled bars.
You could plot the data sequentially, shown in the second chart. The left half of the waterfall is the 2017 data, and the right half is the 2018 data.
These are pretty good, showing the trends and the individual values. They are also pretty easy, using my Peltier Tech Charts for Excel charting add-in.
But if you want an easier comparison of the values themselves, you could plot your data in a regular column chart, either with the endpoints (top chart below) or without (bottom chart).
SB says
This chart is amazing! Would you be keen to create a tutorial of how to do this chart in Excel?
Jon Peltier says
SB –
I’m not sure I’m ready for a big write up on such an obscure variation. My comment on May 13, 2016 shows some of the details of the chart’s construction. White spaces between floating upper and lower shaded bars consist of “hidden” bars (no border, no fill), the same as the white spaces below the lower floating bars. The data is all just formulas to keep things in line, the horizontal lines are error bars on hidden XY Scatter points, and the last sloped line connects two additional XY Scatter points.