Performance Attribution Time Series Visualization

-

Part V: Visualization

The previous part finalized the database function that produces cumulative Cariño scaled Brinson-Fachler performance attribution time series. It is quite natural to visualize these results using charts, and that is what I am developing in this part. For wider access, and easier interpretation, I am using MS Excel to get the data and draw the charts. As this blog is really about SQL, I am not going to go into detail on how to build such an Excel workbook - this is intended more as a tool to actually look at the results provided by the database. Perhaps a more modern tool for visualization would be MS Power BI (or some other BI tool).

The Excel file can be found in my GitHub repository, alongside the SQL scripts used in the preceding parts. Obviously, you need a relatively recent version of MS Excel to use it.

Making the Connection

The Excel workbook already has the required data queries, but it needs to connect to the correct database. To modify the connection, go to one of the data sheets, e.g. PerfTest Attribution sheet, and select one of the data cells. From the Query ribbon, select Edit. This should open the Power Query Editor. There, from the Home ribbon, select Data source settings. There should be only one data source (besides "Current Workbook") - select it and click Change Source. You should get a window where you can enter the Server and Database  for the connection. These should be the same you get in SQL Server Management Studio, when you are able to run the queries in parts I-IV, and run select @@servername, db_name();. Click OK, and close all the Power Query Editor windows; hopefully, you are all set. Test by selecting Refresh All from the Data ribbon.

Serious Series

On the Main sheet, you should see two charts. The first - the whole idea being about performance attribution time series - shows you the excess return of the selected item of the structure, and its breakdown (attribution) into allocation, selection, and interaction effects. For example, this one shows us that the underperformance (excess return) was almost -1 % for the whole period, allocation to asset classes was about zero, negative selection of ca. -1.1 % was the reason for the underperformance, while interaction effect was a bit above +0.1 %.


Now, there are some settings behind this chart. The most important are in the upper left corner of the sheet:


The Portfolio, Benchmark, and Structure must be selected using drop down menus, while the dates can be entered freely. Of course, as long as the data only cover Sept 29 to Oct 31, 2023, there's no point trying to go outside of that range.

The second chart shows the selected element of results in more detail. Since selection seems to be the problem above, let's look at that in more detail:


As we are using the Asset Class structure, the first level of the structure are the asset classes Bond, Cash, and Equity. Its hardly surprising that the selection effect comes almost entirely from Equity, as it is the biggest asset class and the also the most volatile. If you wish to check the weights, select Portfolio weight or Benchmark weight in the drop down menu in cell P1 to change the chart to show those. Other items that can be looked into are the returns, return contributions, and attribution effects.

Going Deeper

So - it's equities where something went wrong. In fact, our return in Equity was -2.48 % when in benchmark it was -0.66 %, an underperformance of -1.82 %. But what caused it? Selecting 1 - Equity in cell B23 drop down menu changes the charts. Looking at level 1 - Equity attribution, we can see it was both the allocation effect, cumulatively -0.77 %, and selection, -1.36 % (interaction was +0.31 %):

In the second chart, we can have a closer look at both of these effects. In allocation, it looks like everything went wrong. If you have a look at benchmark returns and weight differences (using cell P1 drop down menu, not shown here), you can see that US equities was the best performing equity region, while it was massively underweighted (~44 % in portfolio, ~72 % in benchmark), and all the other regions were then overweighted. 

What about selection within equities? It seems we were very close to the benchmark in all regions, except in the US. Actually, US underperformance explains -1.23 % of the -1.36 % selection effect within equities.

Once more, let's dig deeper. Selecting 2 - Equity - US in cell B23 drop down menu shows the Sectors within US equities - only two in the test data, Consumer and Services. This time, allocation between these two sectors was positive +0.70 %, but selection quite negative -3.61 % and interaction +1.18 %, summing up to -1.73 %, the difference between portfolio return (-1.51 %) and benchmark return (+0.22 %). 

So, allocation was successful, overweighting Services, which had a better benchmark return. Selection was negative in both sectors, -2.51 % in Consumer and -1.10 % in Services (not shown in charts). Again, it is possible to delve deeper and look into e.g. the Consumer sector. Now we are at the Instrument level, so there's really no selection effect, as there is nothing to select from within just the one instrument. Interaction is thus also zero. (A small selection effect may arise from trading, and a very, very small selection effect from rounding the market values.) The differences of weights in individual stocks in the portfolio and benchmark explains (almost) all of the return difference, and is attributed to allocation effect at this level. 

Explaining our rather massive underperformance of -7.92 % in Consumer is the fact that we only invested in two of the four Instruments in the benchmark: Untealt and Visla. While Untealt was the best performing of the four, and the overweight gave a positive allocation effect of +1.87 %, Visla had a terrible month with a -20 % return and our big overweight resulted in a negative allocation effect of -6.39 %.

The other two, Billy and Imazing, also had positive returns in the benchmark (and better than total sector benchmark), so having zero position in the portfolio resulted in negative allocation effect for them, too.

Final thoughts

This concludes the series, which started by setting up a test database in MS SQL Server for portfolio and benchmark instruments, transactions, and prices, and then built the daily positions, weights and returns from that using database views and user defined functions. Performance attribution calculations were added using another set of functions. Finally, this part served as a simple visualization of the results.

Brinson-Fachler is one of the earliest, and probably most used, attribution models. It relies on a hierarchically defined decision tree and strictly on/off type qualities of the instruments. A risk factor based performance attribution can, with limitations, be implemented in SQL as well, although the process is quite a bit more complex. E.g. a modern fixed income performance attribution model would probably rely on a lot of interest rate and spread curve data and revaluation of the portfolio and benchmark using simulated curve levels. The valuation models required would most probably need to be implemented in some other environment, with the results stored into the database. Equity models could also be based on risk factors.








Comments

Post a Comment

Popular posts from this blog

Calculating IRR, DPI, RVPI, and TVPI, Part III

-
Image
In Part I , I used sample data on transactions, prices, and FX rates to calculate market values, cash flows, and DPI, RVPI, and TVPI ratios for Private Equity funds. The resulting view's output was a time series with every cash flow and valuation date, and data calculated in two currencies, the Instrument Currency and the Portfolio Currency. In Part II , I discussed how to calculate IRR (internal rate of return) in SQL using a recursive CTE, and went through an example with code. In this part, I will use SQL Server to calculate the IRRs for the funds using the view set up in Part I . Results will be presented in both currencies, and for the portfolio as a whole, in Portfolio Currency. I will build an inline table-valued function (TVF) with two date parameters to limit the calculation period, if required, to enable reporting a specific time period. An inline TVF has a lot of advantages compared to a multi-statement TVF . When using an inline TVF, SQL Server can treat it much like a...
Read more

Calculating IRR, DPI, RVPI, and TVPI, Part II

-
Image
In the previous part , I used sample data on transactions, prices, and FX rates to calculate market values, cash flows, and DPI, RVPI, and TVPI ratios for Private Equity funds. The output was a time series with every cash flow and valuation date, and data calculated in two currencies, the Instrument Currency and the Portfolio Currency. In this part, I will use SQL Server to calculate internal rate of return (IRR) for some simple sample data. In the next part, Part III , I will build an inline table-valued function (TVF) to calculate IRRs in two currencies for the funds and the total portfolio using the data prepared in Part I . The function will have two date parameters to limit the calculation period, if required, to enable reporting a specific time period. But, let's focus for a while on calculating the IRR with a set-based SQL query. The whole SQL script is available in my  GitHub by the name IRR Simple.sql . Internal Rate of Return, IRR By definition, internal rate of return...
Read more

Calculating IRR, DPI, RVPI, and TVPI, Part I

-
Image
In the previous parts (see Performance Attribution  Part I to start with) I have set up a MS SQL Server database, the required tables and data to calculate time-weighted returns and Brinson-Fachler performance attribution for some sample data. However, private assets, e.g. private equity, private credit, and real estate funds, are an increasingly important part of professional investors' portfolios. Instead of time-weighted returns, sharpe ratios etc. commonly used for liquid assets, the private asset classes often use other measures. Perhaps the most important of these is IRR or internal rate of return. Other statistics usually presented on fund provider reports and, increasingly, asset owner private asset portfolios, include ratios like DPI (Distributions to Paid-In capital), RVPI (Residual Value to Paid-In capital), and TVPI (Total Value to Paid-In capital). Here, I am going present how to calculate these using SQL. This first part will set up the data and a view providing the ...
Read more