Nested Calculations In Power Query

Michael Humpherys shows how to use nested calculations in Power Query to make financial calculations easier:

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.

That is a novel use of the “table in a cell” technique.

Related Posts

Bubbling Up HTTP Status Errors In Power Query

Tony McGovern takes us through a method involving Power Query + M of giving end users useful information when a web request fails: So how does this relate to error tables? Like most well-documented APIs, the U.S. Census Bureau API has a page devoted to listing and describing all the possible response codes that can be […]

Read More

Power BI Embedding Techniques

Marc Lelijveld takes us through the different ways we can embed Power BI dashboards: In the end of 2018 Microsoft announced a great new feature which allows you to secure embed Power BI content to client applications or sites and keep security still in place. Actually, it almost works the same as the publish to […]

Read More

Categories

August 2018
MTWTFSS
« Jul Sep »
 12345
6789101112
13141516171819
20212223242526
2728293031