Suppose we have a number of investment portfolios managed  by separate managers, that each start with $100 on 1/1/2015, earn a random return each day, and receive external cash flows on certain days (for the sake of an example, an inflow of $10 at the end of every Monday).

We would like to measure the rate of return achieved by our investments, for a selected manager and time period. How can we achieve this with Power BI?

Here is the report I will be creating:

Data model setup

A sample of the source data looks like this:

The investment data is at the granularity of Manager and Date. ‘Value Open’ and ‘Value Close’ are the portfolio opening/closing values on a given day, and ‘Cashflow’ is the external cash flow occurring at the end of that day. ‘Return’ is the gain or loss in value of the portfolio due to investment returns.

Taking an example, on 5 January 2015, Abel began the day with a portfolio valued at $99.60. The portfolio returned ($0.09) over the day (a negative return), and an additional $10.00 was invested at the end of the day. The closing value on this date was $109.52.

In my sample data model, I have loaded the above data to a table named ‘Value’ and related this to a Date dimension table

Return Measures

There are a number of ways of measuring investment returns, but for this post I am going to focus on Time-Weighted and Money-Weighted Rates of Return.

The idea of Time-Weighted Rate of Return is that it measures the return on the portfolio independent of when cash flows into or out of the portfolio may have been made (know as external cash flows).

To calculate the Time-Weighted Rate of Return we need to geometrically link returns (from the first date to the last date) between external cash flows, ensuring that the external cash flows are not included included in any return values. In practice, we can just include every date in the calculation regardless of whether cash flow is positive, zero or blank.

Before creating the return measures, I created some base measures:

Value Open Total = 
SUM ( 'Value'[Value Open] )

Value Close Total = 
SUM ( 'Value'[Value Close] )

Cashflow Total = 
SUM ( 'Value'[Cashflow] )

Then the Time-Weighted Return measure can be defined as:

Time-Weighted Return (non annualized) = 
PRODUCTX (
    'Date',
    DIVIDE ( [Value Close Total] - [Cashflow Total], [Value Open Total] )
)
    - 1

This measure iterates over all dates in the current filter context, calculating the ratio between the closing value (excluding any cashflow) and the opening value on each day. It then multiplies these ratios using PRODUCTX, and subtracts 1 to give a percentage return.

This is the return in the current period rather than an annualised return. It can be annualised using this formula (assuming a 365 day year):
Annualised Return = (1+Return)^365/NumDays

Time-Weighted Return (annualized) = 
POWER (
    1 + [Time-Weighted Return (non annualized)],
    DIVIDE ( 365, COUNTROWS ( 'Date' ) )
)
    - 1

The idea of Money-Weighted Rate of Return is different. It is calculated from the point of view of the investor who is controlling external cash flows. This investor only cares about the money invested or withdrawn over time. The calculation of Money-Weighted Rate of Return is therefore a form of an Internal Rate of Return calculation. It gives the annual rate of return such that the present values of cash outflows and cash inflows are equal.

Since it is a form of Internal Rate of Return, we can use the DAX XIRR function to calculate it! The measure definition is more complicated than Time-Weighted Rate of Return because the first and last dates are special cases, and the first date used in the calculation (which is treated as time zero by XIRR) should be one day before the first date selected.

The cash flows to be used in return calculation which will be passed to XIRR are:

An important observation is that the first two types of cash flow are negative (assuming the opening value and external cashflow are positive) as these represent cash outflows by the investor. The final cashflow represents the value that could be withdrawn by the investor on the last date selected, so it is positive (assuming the closing value is positive).

Here is how I have written the measure to achieve this:

Money-Weighted Return (annualized) = 
VAR MinDate = 
    MIN ( 'Date'[Date] )
VAR MaxDate = 
    MAX ( 'Date'[Date] )
VAR InitialCF =
    ( -1 ) * [Value Start Of Period]
VAR FinalCF =
    [Value End Of Period]
    
-- Construct Cashflow tables
VAR CashflowInitial = 
    ROW ( 
        "@Date", MinDate - 1,
        "@Cashflow", InitialCF
    )
VAR CashflowDuringPeriod = 
    SELECTCOLUMNS ( 
        SUMMARIZE ( 'Value', 'Date'[Date] ), -- Could also use VALUES ( 'Date'[Date] )
        "@Date", 'Date'[Date],
        "@Cashflow", (-1) * [Cashflow Total]
    )
VAR CashflowFinal = 
    ROW (
        "@Date", MaxDate,
        "@Cashflow", FinalCF
    )
VAR CashflowCombined = 
    UNION ( CashflowInitial, CashflowDuringPeriod, CashflowFinal )

RETURN
    IFERROR (
        XIRR (
            CashflowCombined,
            [@Cashflow],
            [@Date]
        ),
        BLANK ()
    )

This measure constructs three tables of cash flows, corresponding to the three categories of cash flow above: CashflowInitial, CashflowDuringPeriod, and CashflowFinal.

Each of these tables contains columns ‘@Date’ and ‘@Cashflow’. These are then combined with UNION and passed to XIRR. XIRR has no problem with duplicate dates, and will internally sum the cashflow by date.

Looking at the report

In my report above, I have included some visuals that allow us to break down the portfolio performance over any selected time period.

For investment manager Abel, the investment began 2015 with a value of $100, and ended the year with a value of $641. Of this total increase in value of $541, $520 was due to external cash flows (i.e. the $10 weekly investments), and $21 was investment returns, as can be seen from the Investment Waterfall visual:

For the percentage return metrics, Abel achieved a Time-Weighted Return of 2.8% and a Money-Weighted Return of 5.9%. This implies that Abel’s timing of adding cash to the portfolio was relatively favourable when compared with a strategy of simply investing up-front and letting the portfolio return what it will.

If we instead select both managers, we can see that the returns are unfortunately negative, so Abigail’s investment returns are dragging overall performance down:

The beauty of return measures such as those I have presented here is that they are adaptable to any filter context. On the second page of the report I have broken down monthly Money-Weighted Return percentages by Manager:

You can imagine a more complex data set including more dimensions, such as client, asset class, or currency where you can compare returns across any of these dimensions.

If you work with any data involving investment returns or similar calculations, I encourage you to try adapting the techniques I have presented here to your data set.