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.
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.