One of the most powerful features (and there are lots) that I’ve found about Power Query is the ability to hold an entire table in a single cell. Once I’ve got a table in a cell, then I can start performing some magic otherwise impossible in the pre-Modern Excel universe.  You can download the .pbix and .xlsx formats to follow along here.

Some of that magic is nested calculations using those cellular tables. Before I get to a specific application, let me give some finance background.

Financial Background

A central problem in finance is answering the simple question: How much is this contract worth? For example, Bob might say he’ll give me \$102 in a year, and I want to know how much I should pay him for that guaranteed money. If I figure out that the value of the contract is a \$100, then I’m saying that the guaranteed \$102 in a year is worth \$100 today. This means I get a 2% interest on my \$100 investment. This is called the one-year spot rate, and there are similar rates for all sort of different time frames. Taking 1/ (1+.02) gives me the discount rate and multiplying this by the \$102 payment gets me to the \$100 value of the contract.

The next step is that I may want to know how much \$102 two years from now is worth next year. So instead of figuring out what it is worth today, I want to know what it will be worth in a year. To figure this out, I need something called the forward rate, which tells me the annual interest rate one year in the future, in this example.

With the forward rates, I can take a complex series of future payments and find the value of all of those payments today, but also the value at different points in the future. The complexity is that depending on when I want to value them to and the timing of the payment I need to use different sets of forward rates and that’s the application I’ll walk through below.

Nested Calculations and Financial Modeling

I have a model that produces some monthly cash flows from a contract going out ten years. These cash flows are variable depending on different economic scenarios. To determine the value of the contract under each scenario, I need to use the forward rates to “discount” these cash flow back to the valuation date.

So I start with some monthly forward rates for each of the economic scenarios and load them into Power Query.

Next, I need to calculate the discount rate that I can multiply by the cashflow.

This lets me discount a cashflow at that time back one month, but I want to be able to discount all the way back to the beginning. To do this, I need the product of all of the discount factors from the cashflow date to the valuation date. To get this, I group all of the forward rates for each scenario.

So, my data now looks like this:

For each of these scenarios, I need to replicate these tables and filter them down the discount factors I need for the different dates. To set the dates that I want to value the contracts, I create a new column with a list of the dates I want (the next 12 months) and expand that out.

I do the same thing to add the cashflow dates that I’ll be discount from back to the valuation date. So, I’ll only want to pick up the discount factors starting from the valuation date.

With all of this setup, I’ve set up the boundaries of the discount factors that I need, so all I need to do now is filter down the table data to the discount factors that I need. To do that I add a new column using a custom function to filter the data.

The function Table.SelectRows has the following syntax:

Table.SelectRows(table as table, condition as function) as table

Since we’ve grouped the table into cells, we can pass the column [Table Data] into the SelectRows function. The real magic comes in the function. The r variable represents each record in the [Table Data] table. This allows us to perform conditional (or other) logic by comparing the rows within the [Table Data] cell and the related fields in the parent table. So, we are filtering [time] within the grouped table by comparing it to the [valuation date] and [cashflow date] in our parent table.

Where before we had a full table in each cell, now we have a grouped tables filtered down to the relevant time points.

The final step is to calculate the cumulative discount factor, for example going from time 4 back to time 2. This is easy to do with a List.Product on the filtered table’s discount rates.

Cleaning up a bit I have a nice table with all of the discount factors that I need to get the value of a complex contract cashflow under different scenarios at different points in time.

Now I can link up my discount rate table with my cashflow table to calculate the value of the contract and explode it out to 1000 different scenarios and 100s of different contracts.

As with all things Modern Excel, I can update my inputs, click a button and I’ve got all updated results!

Once finished I can load into Power BI Desktop to show the maturity date of the forward rate curve or the cashflow dates of multiple discount curves with ease!

Microsoft’s platform is the world’s most fluid & powerful data toolset.  Get the most out of it.

No one knows the Power BI ecosystem better than the folks who started the whole thing – us.

Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.

* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.

Michael Humpherys

Michael Humpherys started his studies getting his bachelor’s in physics and eventually his Masters in philosophy. Through a fortuitous series of events, he entered finance and now is an Associate of the Society of Actuaries where he has been practicing his data wrangling and model building skills for four years. When he’s not Power Pivoting, he’s at home with his wife and playing with his two kids.

1. Tim Hoogenboom says:

Who’s editing these days? … gets me to toe the \$100 value of the contract … toe? … I need to the product of all of the discount factors from the cashflow date to the valuation date… huh? I don’t mean to sound snarky, but I can’t understand what you’re saying due to the typos in here. Had to stop reading.

1. Michael Humpherys says:

My fault of course. Thanks for the catch.

2. Jason Baldessari says:

@Tim – That would be completely my fault. I apologize for missing those typos in Michael’s post. I’ve been a bit distracted and it has shown especially here. My job is to help the authors convey their stories clearly so that our readers can discuss, learn from, and expand upon these ideas that we share as a community. All I can do is humbly apologize to you, Michael, and the rest of the readers and promise to be more diligent here in the future. I appreciate you calling me out and keeping me honest.

2. Thank you for sharing. Until now, had not seen a use case for the “All Rows” feature in the “Group By” operation. You have opened a door for me.

1. Michael Humpherys says:

When I discovered it I was like “WOW! I’ve been needing this forever!” I have several other use cases that involve performing certain semi-complex allocations or creating an annual mortality table with mortality improvement factors. Its been a game-changer on top of the game-change that is Power Query! 🙂

3. Wow, this is very cool and an eye-opener for PQ use cases that go beyond traditional data wrangling. You put the T in ETL there! Would you mind sharing the solution file?

1. Michael Humpherys says:

I’ll ask P3 if they can post my solution files (Excel and PowerBI) or at least post the complete M code.

4. John Breeden says:

May I have your raw data set? Also, how did you determine or get data for monthly forward rates by period?

1. Michael Humpherys says:

I just used a linear interpolation of the Treasury yield curve for illustration purposes. I’ve been working on a way to use this type of nest calculations to get a cubic spline of the yield curve at monthly intervals and then from there determine the forward rate curve, since the linear interpolation makes the forward rate curve “spikey.”

5. Power Query – – the unsung hero of Excel users everywhere.

6. I worked through your example on Forward Rates using Power Query and I could not have done that without you! Your page, https://powerpivotpro.com/2018/08/power-query-magic-nested-calculations-in-power-query-finance-application/

In my query, I followed the setting up of the custom column according to the instructions on your page and used ={0..12} for the valuation date column. I note in your version of the query in the download file you use ={0..24} for that column. Hence, your query is >16,000 lines whereas mine is just 7,475 lines long.

Is the 0..12 correct?

1. Michael Humpherys says:

I did end up using {0..24} to make the final images more presentable. The 12 vs. 24 just extends how far out you’re looking. So, if you’re only interested in the first year you can go with 12, but if you’re interested in more than that you can increase it. You can also use List.Generate for more exotic dates, such as every other month or only months that are Fibonacci numbers.

7. Your pbix is not consitent with your text. You add 12 month in the text and 24 in the pibx.