Charting a Spill Range

The past several posts have illustrated various ways to apply dynamic array formulas and their spill ranges. Kent asked if we could use spill ranges to create a dynamic chart to visualize the future value of an investment portfolio. For example, start with someone’s current portfolio value and then apply an assumed growth rate and annual investment amount. This post demonstrates how to compute the annual future value and send the resulting spill range into a bar chart. Plus, when any assumptions are changed, including the number of years, the chart is automatically updated. Thanks for this great idea Kent!

Objective

Let’s take a step back and confirm our goal here. We’d like the user to be able to enter a few key assumptions, namely, the number of years in the analysis, the current portfolio value, the annual investment amount (addition to portfolio), and growth rate. We would enter these into some cells like this:

Then, we want the future value for each year to be displayed in a chart, like this:

Then, if the user changes some values, perhaps the number of years to 20, we want the chart to update accordingly:

We’ll build this using the following steps:

• Compute the future values
• Create a defined name
• Build the chart

Let’s get to it.

Note: dynamic arrays and spill ranges are available on a limited basis at the time I’m writing this post. So, your version of Excel may not have these capabilities. At the time I’m writing this, they are available to O365 subscribers with the Insiders Fast channel. If you are an O365 subscriber, you may be able to switch to the Insiders Fast update channel. See the Microsoft website for more info.

Compute the future values

First up is computing the future values of the portfolio, given the assumed growth rate and additional annual investment. We can use Excel’s future value function (FV) with the help of the SEQUENCE function.

Let’s take another look at the assumptions so we can see their cell references:

Now, the FV function computes the future value, given the interest rate (C7), number of periods (C4), periodic payment amount (C6), and present value (C5). So, we start by translating our assumptions into the FV arguments. For example, we write the following formula into B12:

=FV(C7, C4, C6, C5)

But, that formula returns only a single value, the future value at the end of 10 years. What we want to see instead are all 10 future values … one row for each year. How can we do this?

Well, we can use the SEQUENCE function. This function returns a given number of rows (and/or columns) based on its arguments. So, we just need to make a minor adjustment to our formula so that the SEQUENCE function returns 10 values … 1 through the number of years in C4:

=FV(C7,SEQUENCE(C4),C6,C5)

Since the FV function operates on a cash flow model, it returns a negative value given the assumptions above. So, we’ll need to flip the sign, which we can do a number of ways including adding a dash in front, like this:

=-FV(C7,SEQUENCE(C4),C6,C5)

We update the formula in B12 and hit enter, bam:

So far, so good. Now we just need to send these results into the chart. But, we’ll make a quick detour first and set up a defined name.

Create a defined name

At the time I’m writing this post, some Excel features (for example, charts) don’t fully support spill range references, such as B12#. Often, we can work around this limitation by using a defined name instead. We just need to create a new defined name that references the spill range, and then use the defined name instead of the spill reference.

To create the defined name, open the Name Manager (Formulas > Name Manager). Then, click New to reveal the New Name dialog. We enter the desired name, such as ChartData, and then select the formula cell and type # at the end to reference the spill range, like this:

Click OK and close. With our name set up, we are ready to create the chart.

Build the chart

We get our starter chart going by selecting any cell in the FV column and then inserting the desired chart (Insert > Clustered Column Chart).

Excel inserts a basic chart, and it looks like we are done:

But … as soon as we change the number of years, we see that our chart does not update. Don’t worry … we just need to make a minor update to the chart so that it uses our defined name.

To do so, click the Chart Tools > Select Data command. The Select Data Source dialog appears, like this:

Click the Edit button on the Legend Entries (Series) side to reveal the Edit Series dialog:

We just need to change the A1-style range reference ($B$12:$B$21) to our defined name ChartData, like this:

Now, we are good to go. We can change any assumption, including the number of years, and the chart updates accordingly.

If you have any other spill tips, please share by posting a comment below … thanks!

Sample file: ChartSpill.xlsx