Running Product in DAX: Calculating Portfolio Returns

Recently a friend of mine asked me how to calculate investment rates of return in PowerPivot.  In order to do this, one must be able to run over a list of values using a multiplication. Currently, DAX does not have a ProductX or MultiplyX function to evaluate an expression in a specified row context followed by a final pass to multiply all computed values. I dealt with this issue in MDX years ago when attempting to do the same.  Although in that case I used the Multiply() .Net stored procedure to achieve the goal within the expression, I still recall seeing how Mosha pointed out a way to mimic a running multiplication using the following formula:

a * b = e^ ( LN(a) + LN(b) ) 

In DAX, we can leverage the same computation.  In simple terms, one can define a calculated column as:

image 

=POWER( 10,
    SUMX( 
        FILTER( Table1,
                Table1[Id] <= EARLIER( Table1[Id] )
            ),
        LOG( Table1[Values] )
    )
)

Notice we can leverage the SUMX expression over a table that grows based on the current row context, and calculate the LOG output which is summed at a second pass.  By raising the final scalar output to the power of 10, we achieve the running product effect:

image

Let’s now apply this technique to solve the question related to investment returns. Drawing from the common financial VAMI definition, we see that we must find a way to apply the logic below when determining returns over time:

Previous Rate of Return x ( 1 + Current Rate of Return )

At any given date, this formula will give us the rate at which the portfolio is performing. Notice that:

  • Although the original definition stipulates monthly returns, it is common to use it with daily returns as well.
  • The formula requires the use of a running multiplication
    I have placed the sample Excel 2013 file for this blog entry here, in case you want to follow the formulas by yourself.  The idea is we will build a hypothetical portfolio and calculate its rate of return.  For this, I downloaded closing prices from 1/3/2012 to 12/18/2012 for three stocks, and uploaded them into a PowerPivot model.

For simplicity, I did not account for dividends and I used the same date (1/3/2012) to establish a position and there were not subsequent buy orders.  Those items can surely be dealt with more DAX logic, but it is not necessary to simply illustrate the point of running multiplications.

I created a linked table with the info below. The quantity of each order serves as a parameter when using portfolio allocation to compute returns:

image

To first calculate the actual market value of the position, we use a measure defined as:

[Position]:=SUMX(
    VALUES(Prices[Ticker] ) ,
    CALCULATE(
        VALUES( Parameters[Quantity] ),
        FILTER(Parameters, Parameters[Ticker] =
EARLIER(Prices[Ticker] )  )
    )  *
    CALCULATE( MAX(  Prices[Close]  ), LASTDATE( Prices[Date] )  )
)

As we want the Portfolio Total (row total) to reflect the correct amount, we must calculate the value at the grain of the ticker symbol.  Additionally, as there is no actual relationship with the parameter table we must establish one through a calculation.  The result multiplies the order quantity for any given ticker symbol times the closing price for the trade date.  As we also want the column total to generate a valid dollar value, we use LASTDATE in the CALCULATE statement that fetches the closing price.  This is because MAX will get the maximum value for all days that make up the grand total row, but we want is the actual value for only the last date. A partial screenshot of the result shows the following:

image

We now want to compute the Daily Delta which is actually the daily rate of return.  For this it is necessary to get the last position value on the most current trade date (before today).  The position value, or market value, for trade date minus one is:

[Market Value T-1]:=CALCULATE(
    [Position],
    FILTER(
    ALLEXCEPT( Prices, Prices[Ticker] ),
    Prices[Date] =
        CALCULATE( MAX( Prices[Date] ),
        FILTER( ALLEXCEPT( Prices, Prices[Ticker] ),
            Prices[Date] < MAX( Prices[Date] )
            )
        )
    )
)

As we cannot be sure what T-1 will be, we must calculate the MAX date before today, and then retrieve the position value on that date:

image

The Daily Delta can now be easily calculated:

[Daily Delta]:=IFERROR( ([Position] – [Market Value T-1])/[Market Value T-1], BLANK() )

image

Notice at this point the Grand Total reflects the actual portfolio daily return, which is not a straight sum or average of the individual ticker symbol returns but instead is calculated at the total market value for the portfolio.

We are finally ready to apply the running product logic in order to determine compounded rates of return:

[Comp ROR]:=POWER( 10 ,
    SUMX(
        FILTER(
            ALL( Prices[Date] ),
            Prices[Date] <= MAX( Prices[Date] )
        ) ,
        LOG( 1 + [Daily Delta] )
    )
) – 1

image

For any given symbol or for the entire portfolio, we can now see what our percent gain or loss was at any given point during the life of the position. Charting this will help better understand the overall picture of our investments. In Excel 2013, it could look like this:

image

The entire report above is built using only the DAX functions discussed, as well as built-in Excel functionality (Moving average trendlines and cube functions are of great help when telling the story of investment performance over an Excel dashboard).  For example, to get the most current report date I used:

="(As of " & CUBEMEMBER("ThisWorkbookDataModel","[Prices].[Date].[All].LastChild") & ")"

Similarly, to get the portfolio return value (14.20 %), I simply queried the model from an worksheet cell with the following formula:

=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Comp ROR]")

Finally, one of the beauties of Excel 2013 PowerPivot models is that there is no need to reopen the PowerPivot window when updating a linked table value.  Simply refreshing the dashboard through the Data tab will work to generate newly computed values.  As such, what-if scenario analysis is greatly simplified.  For example, and as you can see above from the Symbol % Allocation chart, YHOO was only a small part of the portfolio market value, however it had a great run at the end – which can be verified in the Symbol Returns chart.  If I changed the parameters of my report, and allocated a much bigger order to that symbol as in:

image

The returns will be now more interesting:

image

Also, you would notice the three moving averages pointing in one direction: up :-)

About these ads

5 Responses to Running Product in DAX: Calculating Portfolio Returns

  1. Jason Thomas says:

    Great post Javier! :)

  2. ruve1k says:

    Instead of using POWER(10,….) and LOG() it would be simpler to use EXP() and LN() which use a base of e.
    See the following post on MrExcel.com

    http://www.mrexcel.com/forum/powerpivot-questions/611808-attempting-calculate-product-previous-values.html#post3033475

    • javierguillen says:

      Thanks ruve1k! I am curious to compare performance on both approaches, though I wouldnt expect a major difference; thanks for the referencing that link!

  3. Excellent work Javier! Thanks for sharing.

    • javierguillen says:

      Thanks Brent! I’m interested in expanding these into additional financial computations, to include Sharpe ratio for example, maybe you can help me!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 67 other followers

%d bloggers like this: