Performance Attribution

Part III: Brinson-Fachler Performance Attribution, Daily

In parts I and II, we set up a test database with some data and three views producing performance time series data for different portfolio structures. In this part, I will add two user-defined functions to give us the following:

• Performance time series of a portfolio and a benchmark combined
• Daily performance attribution and performance contribution results

The functions are rather long, approximately 50 and 160 lines of code, respectively, so the full version of the latter will only be available in my GitHub repository.

Benchmarking

Let's start by adding benchmark data to our portfolio time series. The view vPerformanceTimeSeries was developed in Part II and computes portfolio market values, cash flows, and returns in the structures defined in table Structure. In this test environment, any portfolio can act as a benchmark - there are no specialized tables for benchmark data. Thus, we want to combine, for each day and structure element, the performance data of two portfolios, while keeping in mind that there may be structure elements with no data available for either one of the portfolios.

We might be able to build a view for that purpose, but an inline table-valued user-defined function (TVF) is much easier to use for this. An inline table-valued function is much like a view, as it must be defined in a single select statement, but it can be given parameters, which then can be used in the statement. Also, an inline TVF can usually filter data, like a view, when joined to other objects or limited through a where clause in the select using it. This is in contrast to a multi-statement TVF, which is much closer to a stored procedure. While in a multi-statement TVF you can have loops and use table variables for intermediate results etc., it will always process and return everything the same way, irrespective of any further joins or filtering, which can be very time-consuming in itself and causes problems for the query optimizer, which has very little to work on, as it does not really see what's expected to come out. Well, at least that used to be the case, the newest SQL Server versions try to overcome this to an extent (see here).

Because we need data on two portfolios and either one of them might have no data while the other one does, I am using a full outer join to combine the two data sets. Filtering the portfolio and benchmark only after the join would be problematic, since there are rows we need where these columns would be null. Instead, by giving the function two parameters @portfolioik and @bm_portfolioik, I can use those to filter the data before the join, in two separate select statements:

create or alter function PerfTest.uftPerformanceTimeSeries(@portfolioik int, @bm_portfolioik int) returns table as

return (

	select
		isnull(pf.StructureIK, bm.StructureIK) as StructureIK,
		isnull(pf.LevelCnt, bm.LevelCnt) as LevelCnt,
		isnull(pf.LevelNr, bm.LevelNr) as LevelNr,
		isnull(pf.Level1Name, bm.Level1Name) as Level1Name,  
		isnull(pf.Level1, bm.Level1) as Level1,
		isnull(pf.Level2Name, bm.Level2Name) as Level2Name,
		isnull(pf.Level2, bm.Level2) as Level2,
		isnull(pf.Level3Name, bm.Level3Name) as Level3Name,
		isnull(pf.Level3, bm.Level3) as Level3,
		isnull(pf.Level4Name, bm.Level4Name) as Level4Name,
		isnull(pf.Level4, bm.Level4) as Level4,
		isnull(pf.Todate, bm.Todate) as Todate,
		pf.MarketValuePC,
		pf.CashFlowPC,
		pf.lag_MarketValuePC,
		pf.ReturnPC,
		pf.ReturnPCPerc,
		pf.ReturnPCLog,
		bm.MarketValuePC as bm_MarketValuePC,
		bm.CashFlowPC as bm_CashFlowPC,
		bm.lag_MarketValuePC as bm_lag_MarketValuePC,
		bm.ReturnPC as bm_ReturnPC,
		bm.ReturnPCPerc as bm_ReturnPCPerc,
		bm.ReturnPCLog as bm_ReturnPCLog
	from
		(select * from PerfTest.vPerformanceTimeSeries where PortfolioIK = @portfolioik) pf
		full outer join
		(select * from PerfTest.vPerformanceTimeSeries where PortfolioIK = @bm_portfolioik) bm
		on	bm.StructureIK = pf.StructureIK
			and bm.LevelNr = pf.LevelNr
			and (bm.LevelNr <= 0 or bm.Level1 = pf.Level1)
			and (bm.LevelNr <= 1 or bm.Level2 = pf.Level2)
			and (bm.LevelNr <= 2 or bm.Level3 = pf.Level3)
			and (bm.LevelNr <= 3 or bm.Level4 = pf.Level4)
			and bm.Todate = pf.Todate

);

The join to combine these two sets of portfolio and benchmark data is rather complex, as it has to join all structure levels used. This function could also be defined to take a third parameter, @structureik, to only provide data for one selected structure. But being an inline TVF it should be able to use the same filtering as efficiently also if it is provided in the statement using the function.

The structure data and date are processed through an isnull() function to always return data for these columns, even if there are none for one of the portfolios.

Let's have a look at the data this function provides. In this sample query I am asking for a very limited data set, setting the function parameters for portfolioIKs as 1 and 2 and limiting the results to GB consumer sector equities on 20th October using structure 1:

select Level4, MarketValuePC, CashFlowPC, lag_MarketValuePC, ReturnPC, ReturnPCPerc, bm_MarketValuePC, bm_CashFlowPC, bm_lag_MarketValuePC, bm_ReturnPC, bm_ReturnPCPerc
from PerfTest.uftPerformanceTimeSeries(1, 2) 
where StructureIK = 1 and LevelNr = 4 and Level1 = 'Equity' and Level2 = 'GB' and Level3 = 'Consumer' and Todate = '2023-10-20' 
order by Level4

And the results, in SQL Server Management Studio, look like this:

Note that there are five equities in total, while one of them (Nofever) is only in the portfolio, but not in the benchmark, while another one (Ziag) is only in the benchmark, but not in the portfolio. You may also note that the Portfolio position in Ask has been fully sold during the day, as its MarketValuePC is zero but there is a significant CashFlowPC. And if you look at the returns, the absolute ReturnPC differs from bm_ReturnPC due to different weights, while the percentage returns ReturnPCPerc and bm_ReturnPCPerc are very close to being equal. The percentage return differences in RAT and Ustra are due purely to rounding, as market values are rounded to two decimals before calculation. Without the rounding, they would be equal, as the returns are based solely on daily price and exchange rate changes, which do not depend on portfolio or quantity. For Ask, on the other hand, the sell price determines portfolio return, which makes it differ from the benchmark.

Performance Attribution (Finally)

After a lengthy setup, a few views, and a function to build the combined performance data of the portfolio and its benchmark, for the defined measurement structures, we are finally ready to do some real performance attribution. Like explained before, the aim here is to produce measures that help us evaluate the decisions made. This is done by breaking up the excess return (positive or negative) of the portfolio versus its benchmark to effects based on each decision making level. 

Let's start by defining the daily attribution results in another inline TVF, uftPerformanceAttribution, taking the same two parameters @portfolioik and @bm_portfolioik as uftPerformanceTimeSeries. The script for the function is about 160 lines long, so I am not including it all here. I start by getting the required performance time series data for the calculations. For this, it is sufficient to join the uftPerformanceTimeSeries to itself to get the upper level data for each item for the same day and structure:

from
	PerfTest.uftPerformanceTimeSeries(@portfolioik, @bm_portfolioik) n
	-- get upper level data for each portfolio item ("subportfolio", row)
	inner join PerfTest.uftPerformanceTimeSeries(@portfolioik, @bm_portfolioik) up 
		on	up.StructureIK = n.StructureIK 
			and up.Todate = n.Todate
			and (
				-- top level: use itself as upper node
				(up.LevelNr = 0 and n.LevelNr = 0) 
				-- other levels: upper level LevelNr = item LevelNr-1, and match all upper level identifiers
				or (
					n.LevelNr > 0
					and up.LevelNr = n.LevelNr - 1
					and (n.LevelNr <= 1 or up.Level1 = n.Level1)
					and (n.LevelNr <= 2 or up.Level2 = n.Level2)
					and (n.LevelNr <= 3 or up.Level3 = n.Level3)
				)
			)

For the top level (i.e. the total portfolio), the join is made to itself; for the levels just below it, like AssetClasses Equity, Bond, and Cash, the upper level is the total portfolio, and so on. Since the attribution effects are meant to explain the excess performance of different decisions, each decision making level is analyzed independently of the others, i.e. only in the context of its upper level.

With the performance data of each portfolio element now combined to its upper level data, I first convert the numeric values to float for maximum accuracy, avoiding rounding errors (only a part shown here).

cross apply (
	-- convert two decimal numeric values to float for increased accuracy, calculate weight to upper level
	-- use isnull to replace null with zero, helps with later calculations
	select
		convert(float, isnull(n.MarketValuePC, 0)) as MarketValuePC,
		isnull(convert(float, n.MarketValuePC) / nullif(up.MarketValuePC, 0), 0) as Wgt,
                [...]

		isnull(up.bm_ReturnPCPerc, 0) as up_bm_ReturnPCPerc
) d				

At the same time, I can use isnull() to replace null values with zeros, and calculate some values like weights based on the market values divided by the upper node market values. Note the use of nullif(), which replaces a specific value (zero in this case) with null, in the divisor, to prevent division by zero errors.

Make Your Contribution

First, let's calculate the performance contribution of each item. Contribution, as a concept, is simpler than attribution, as it does not take into account any benchmark data. As a basic example, if you had two investments A and B with equal weights, and A's return was 10 % and B's was 6 %, your total portfolio return was 8 %. The performance contribution of A was the 50 % x 10 % = 5 %, and of B 50 % x 6 % = 3 %, the sum of which 5 % + 3 % = 8 %, the total portfolio return. The same can of course be applied to the benchmark, as well.

cross apply (
	-- calculate daily contribution to portfolio return % and benchmark return %
	select
		case
			-- scale daily returns: if upper level EUR return = 0 then upper level percentage return = 0 and contributions are item EUR return / upper level lagged market value (summing up to zero).
			when d.up_ReturnPC = 0 then d.ReturnPC / nullif(d.up_lag_MarketValuePC, 0)
			-- otherwise just scale the return by the item contribution to upper level EUR return, no need to calculate percentage returns as both have the same denominator
			else d.up_ReturnPCPerc * d.ReturnPC / d.up_ReturnPC
		end as ContribPerc,
		case
			when d.up_bm_ReturnPC = 0 then d.bm_ReturnPC / nullif(d.up_bm_lag_MarketValuePC, 0)
			else d.up_bm_ReturnPCPerc * d.bm_ReturnPC / d.up_bm_ReturnPC
		end as bm_ContribPerc
	) c

However, in some circumstances, the percentage returns are ill defined (e.g., when using derivatives). Also, the adjustment I made (see Part II) when cash flows are very large would lead to inconsistent reutnrs when combined with other returns. We need a definition of performance contribution that can handle such situations. Using currency returns, it is also possible to just calculate each item's share of upper level currency return (as these, by definition, sum up to 100 %), and define contribution as that share of upper level percentage return. In the above example, let's say total invested in A and B was €100. Then upper level EUR return is then €50 x 10 % + €50 x 6 % = €8. Performance contributions of A and B, respectively, are 8 % x €5 / €8 = 5 % and 8 % x €3 / €8 = 3 %, as before. 

However, the above is in the else part of the calculation. If upper level return happens to be zero, the formula does not work at all. Let's say return on A was a negative 6%, offsetting B's positive 6 %, and the total portfolio return was zero. Still, we'd like to have some reasonable performance contribution numbers. And, for all this to make sense, why should having a different return for A have an effect for B's results? Well, it shouldn't. If upper level currency return is zero, I will define performance contribution as the currency return divided by the upper node lagged market value. This should work as well, as the upper node return percent is the sum of those item currency returns, divided by the lagged market value. For B it would now be calculated as €3 / €100 = 3 %, the same as before. (For A it would obviously be -3 %.) 

The small catch here is, and the reason the latter formula is not always used, that it requires a market value for the upper level, so again e.g. pure derivatives may be a problem. If there is no return nor market value on the upper level, and return percentage there hence is undefined, performance contributions will be undefined, too.

Attribution (Really)

The Brinson-Fachler attribution is calculated as follows. Again I am rather using currency returns instead of percentage returns and weights, as they are better defined. In the comments within the SQL code, the formulas are, however, the "standard" Brinson-Fachler formulas; the code makes some exceptions to these.

cross apply (
	-- calculate daily attribution, matching upper level return difference
	-- using EUR returns (ReturnPC) and market values (lag_MarketValuePC) instead of weights and percentage returns to make calculations work better when up_lag_MarketValuePC is zero (e.g. derivative subportfolios).
	-- R = return (perc)	r = return (log)	W = Weight	U = upper level	p = portfolio	b = benchmark
	select
		-- allocation, by default: (Wp - Wb) * (Rb - RUb). 
		-- if Rp is defined (market value exists) but Rb is not: use Rp instead of Rb (will show effect as allocation instead of interaction) 
		(d.lag_Wgt - d.bm_lag_Wgt)
			* (case
				when d.lag_MarketValuePC <> 0.0 and d.bm_lag_MarketValuePC = 0 then d.ReturnPC / d.lag_MarketValuePC
				else isnull(d.bm_ReturnPC / nullif(d.bm_lag_MarketValuePC, 0), 0.0)
			end - isnull(d.up_bm_ReturnPC / nullif(d.up_bm_lag_MarketValuePC, 0), 0.0)) as Allocation,
		-- selection, by default: (Rp - Rb) * Wb
		-- if Rp is not defined (market value does not exist) or Rb is not defined: 0 (will show as interaction instead of selection)
		-- if Wb = 0: use Wp instead of Wb
		case
			when d.lag_MarketValuePC = 0 or d.bm_lag_MarketValuePC = 0 then 0.0
			else (d.ReturnPC / d.lag_MarketValuePC - d.bm_ReturnPC / d.bm_lag_MarketValuePC)
				* case 
					when d.bm_lag_MarketValuePC = 0 or d.up_bm_lag_MarketValuePC = 0 then d.lag_Wgt
					else d.bm_lag_Wgt
				end 
			end as Selection,
		-- interaction, by default: (Rp - Rb) * (Wp - Wb)
		-- if Rp is not defined (market value does not exist): Cp (contribution to upper level performance)
		-- if Wb = 0: 0  (effect shown in allocation)
		case
			when d.lag_MarketValuePC = 0 then d.ReturnPC / nullif(d.up_lag_MarketValuePC, 0)
			when d.bm_lag_MarketValuePC = 0 or d.up_bm_lag_MarketValuePC = 0 then 0.0 
			else (d.ReturnPC / d.lag_MarketValuePC - d.bm_ReturnPC / d.bm_lag_MarketValuePC) * (d.lag_Wgt - d.bm_lag_Wgt)
		end as Interaction
) a

The allocation effect is the effect of over or underweighting something, say, a sector within equities. To isolate the allocation decision from security selection, only benchmark returns are used to figure out if the decision was good or bad. If sector A had a better benchmark return that equities and it was overweighted, the formula gives a positive allocation effect, as both the weight difference (Wp - Wb) and the return difference (Rb - RUb) are positive. The allocation effect is also positive, if both the weight difference and the return difference are negative. If they are of opposite signs, like when overweighting an underperforming sector, the effect is negative.

I am making an adjustment here for off-benchmark assets. If we invested in a sector that was not in the benchmark at all, the standard formula would reduce to Wp * -RUb. Imagine a period where equities had a very positive performance. Any off-benchmark assets would be, by the standard formula, very negative allocation decision, as their benchmark return is zero. This would distort the results. In case there is no benchmark weight, I am using portfolio return instead of benchmark return, and the formula then becomes Wp * (Rp - RUb). With the new formula, let's say we invested in a high-beta sector, which is not in the benchmark; the return would likely be better than the benchmark, and this allocation decision would be positive.

The selection effect tells us how we succeeded within an item. To keep to the same example as above, it tells us if we added value within each sector within equities. Here, we isolate the allocation decision by using only benchmark weights, and comparing the portfolio return to the benchmark return within each sector. In short, if portfolio return is higher than benchmark return, i.e. (Rp - Rb) is positive, the effect is also positive (assuming benchmark weights are positive) and it is scaled by benchmark weight.

Again, I am making an adjustment. For those off-benchmark bets, even the standard formula would show zero effect, as benchmark weight is zero. But, if the portfolio has no investments in a given sector, the standard formula would show -Rb * Wb as the selection effect. This does not make much sense, as we did not actually do any selection - it was really an allocation decision, and already shown there. So I am setting selection effect to zero also in this case.

The interaction effect covers the residue, the product of weight difference and return difference. Quite often it is very small, and I find it better to just combine it to the selection effect, but sometimes there is a case to present it separately. So better to keep it separate in the calculations just in case. If we overweighted a sector, and its benchmark return was better than the benchmark return on equities, the allocation effect is positive. If our sector return was better than the benchmark's, the selection effect is positive, too. In this case, also the interaction effect is positive, as both the return difference (Rp - Rb) and weight difference (Wp - Wb) are positive.

Here, I have to take into account the adjustments I made for the allocation and selection effect formulas. The interaction effect must have the opposite adjustment for everything to add up. Hence, I am showing zero interaction effect for off-benchmark bets where benchmark weight is zero, and using portfolio contribution to upper level performance if portfolio return percentage is not defined. The latter can be the case especially with derivatives.

As an example, let's look at how the portfolio did against the benchmark in British equities on October 20th using structure 1:

select Level3, lag_Wgt, ReturnPCPerc, up_ReturnPCPerc, bm_lag_Wgt, bm_ReturnPCPerc, up_bm_ReturnPCPerc,
       ContribPerc, bm_ContribPerc, Allocation, Selection, Interaction
from PerfTest.uftPerformanceAttribution(1, 2) 
where StructureIK = 1 and LevelNr = 3 and Level1 = 'Equity' and Level2 = 'GB' and Todate = '2023-10-20'
order by Level3

The output, with an added title row to color code some inputs and outputs, and some calculations in Excel (available in GitHub), looks like this:

For example, in the Consumer sector, the portfolio weight (out of British equities) was 44.772 % (the lag_Wgt represents the weight at the start of the day. Wgt would be end of day weight). In the benchmark the Consumer sector was only 39.185 %, so this sector was overweighted. Portfolio return was -0.735 %, a little better than in the benchmark, -1-257 %. The British equities returns (the up_Returns) were -1.813 % and -2-027 % for the portfolio and benchmark, respectively. 

Thus, the contribution to British equities return from the Consumer sector was 44.772 % * -0.735 % = -0.329 %. The allocation decision was positive, as we overweighted a sector where benchmark return was better (less negative in this case) than upper level benchmark return: (44.772 % - 39.185 %) * (-1.257 % - (-2.027 %)) = 0.043 %. Selection was also positive, since our return in this sector was better than the benchmark's: 39.185 % * (-0.735 % - (-1-257 %)) = 0.205 %. The residual, interaction, is (44.772 % - 39.185 %) * (-0.735 % - (-1-257 %)) = 0.029 %.

And, indeed, the sum of the return contributions matches the British equities return: -0.329 % + -0813 % + -0.672 % = -1.813 %. Also, the sums of the performance attribution components fully explain the excess return in British equities 0.021 % + 0.156 % + 0.036 % = -1.813 % - (-2-027 %) = 0.214 %.

The excess return we made in British equities is, hence, mostly explained by good stock picking in the Consumer sector. Sector allocation had a positive, but much smaller, effect.

Next: Calculating the contribution and attribution effects as a time series.