Exploring the Future: Forecasting and Future Values

Forecasting is a commonly used method to estimate the future value of investments. Power BI is a great tool for visualizing forecasts in an effective way based on DAX calculations. Financial functions in DAX allow for analyzing multiple scenarios to investigate the result of investments.

The business case in this chapter focuses on insights into the value of an investment over time. As a specific example, think of acquiring a property to rent out: after an initial investment, incoming and outgoing cash flows are to be expected in the future. The value of a single dollar in the future is different from a dollar in the present; this is due to inflation, but is also related to the option to invest the dollar from the present to earn a return over time. Future cash flows therefore need to be adjusted to make them comparable to today's investment and ultimately to answer the question: is this a good investment to make?

As...

Financial calculations

If you want to buy a house, most of the time you will need to take out a loan. You then have to pay interest over the course of the loan, in combination with regular payback. But when doing the math on this, inflation is a factor to take into account as well: if you had a loan of, say, $100,000, and you only paid interest, in 20 years your debt would still be $100,000 nominally, but the worth of it would be much less.

The same is true when making an investment. If you have $100,000 and you plan to invest it by acquiring a property, multiple financial elements will be part of the equation:

• The initial investment (of acquiring the property)
• Future incoming cash flows (tenants paying rent)
• Future outgoing cash flows (maintenance, taxes, and other expenses)
• The residual value (the worth of the property at the end of your period of investigation)

The question is, given these four elements and expectations about future devaluation...

Financial DAX functions

DAX has borrowed many financial functions straight from Excel. Of these, two are applicable to this chapter's case study in particular: XNPV and XIRR. The X in the name of these functions suggests that they are table aggregations, and indeed, the first argument of each is a table.

XNPV can be used to calculate the NPV of a table of cash flows:

XNPV(CashFlowTable, <value>, <date>, Rate)

Note that the cash flows do not need to be in the table: you provide the values of the cash flows as an expression that is evaluated in row context on the CashFlowTable. Similarly, an expression returning a date is evaluated for each row. This means that discounting is done on a daily basis, and the (annual) n value from the formulas in the previous section is calculated (for the m^th row in the table) as:

The XIRR value tries to approximate the IRR for a table of cash flows:

XIRR(CashFlowTable, <value>, <date>)

Like...

The business case and model

In the remainder of this chapter, we look at a portfolio of real estate properties. Different properties may have different life cycle durations. Each property has:

• An investment in year 0, cash flows, and a residual value.
• Cash flows with a recurring pattern during the life span of the property, either positive (income) or negative (expenses).
• Positive or negative cash flows with an irregular pattern, like maintenance.

You can download this model, 2.6 Exploring the future.pbix, from https://github.com/PacktPublishing/Extreme-DAX/tree/main/Chapter2.6.

The model contains three fact tables: fPosCashFlows for positive recurring cash flows, fNegCashFlows for negative recurring cash flows, and fIrregular for irregular cash flows.

There can be many different cash flow drivers; to be able to analyze them separately, there is a filter table with the type of cash flow for each fact table:

Figure 2.6.4...

Calculating Future Value (FV)

As the first step in the calculations, we need to calculate the nominal or future value of all cash flows. Remember that we have multiple parts to calculate: initial investment, recurring cash flows, irregular cash flows, and residual value. We will start with the easy part.

Initial investment and residual value

In our model, both the initial investment and residual value are attributes of a property. We could simply total up the values in the corresponding columns in the Property table, but for our analysis, the values should be mapped onto the correct year: the initial investment in year 0, and the residual value in the last year of the property's life cycle.

To help with mapping the initial investment to year 0, an additional column, Initial period, is in the Property table. This column always contains the value 0. The initial investment is calculated with the DAX measure below:

FV Initial Investment =
CALCULATE(
    SUM(&apos...

Calculating Net Present Value (NPV)

The NPV is the sum of the present values of all cash flows over the years. Remember the definition of PV:

So, to calculate the PV of a cash flow in year n, we need to take the discount rate and divide the FV by a power of the discount rate. As an example, if you wanted to create a calculated column in the Property table with the PV of the residual value of each property (we are sure you don't want to do that by now!), you could do that with the formula below:

PV Residual Value =
VAR EndYearNr = 
    LOOKUPVALUE('Year'[YearNr], 'Year'[Year], Property[End Year])
VAR DiscountFactor =
    (1 + [Discount value]) ^ EndYearNr
RETURN
DIVIDE(Property[Residual Value], DiscountFactor)

In normal language: we take the property's end year, find the corresponding year number, calculate the discount factor with the year number as power, and divide the residual value by the discount factor. The discount factor is 1...

Calculating the Internal Rate of Return (IRR)

Now that we know how to calculate the NPV in our model, calculating the IRR is easy, as it is just an application of the XIRR function. Before we do that, it is worthwhile to look more closely at what the IRR is.

In the chart below, the NPV is plotted for a single property against different possible values of the discount rate:

Figure 2.6.16: NPV for varying discount rates

It is not easy to see in this chart, but the line has a slight curve; which is to be expected as the NPV is some complex polynomial function of the discount rate. Remember that the definition of IRR is the discount rate at which the NPV equals zero. The chart contains a (horizontal) constant line at zero to allow for visually determining where the IRR must be. The vertical, dotted line is an approximation of the IRR. As can be seen in the chart, for some discount rates, the NPV for this property is positive, but for other discount rates, it is negative...

Calculating cost-covering rent

The last topic in this chapter is about cost-covering rent, or CCR for short. This is the answer to the question: what is the minimum rent to charge property tenants to make the investment break even?

Let's go back to the NPV formula once again:

The IRR is calculated by solving this formula for the rate r at zero NPV:

The cost-covering rent also comes from solving the NPV formula, but now for (roughly) the future values:

To be more precise, while our future values are composed of multiple components, we only want to solve for the (initial) recurring positive cash flows in this case. The question is therefore: instead of the positive recurring cash flows in the fPosCashFlows table, and specifically those with Type 2 (rent), which values can we use to get a zero NPV?

Like with the IRR, there is no direct way of calculating this. And worse, there is no DAX function that will solve this equation. We therefore need...

Summary

In this chapter, you have learned about financial metrics for analyzing the future of investments. We have discussed Future Value, Present Value, Net Present Value, and Internal Rate of Return, all of which are common metrics that are used by investment analysts around the world. They are so common that DAX offers some specific functions to calculate them: XNPV and XIRR.

In creating a model for dynamic financial analysis, you have learned how to use what-if parameters in complex calculations. We have seen that the NPV metric can be computed without the XNPV function as well, which opens up additional possibilities to view results at a lower level of detail than the all-up NPV.

The IRR is a metric that is not easily calculated, and an approximative method is needed to find a "good enough" result. This is what the XIRR function implements. We have presented an alternative approach through DAX which, although without much added value in calculating IRR, proved...