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