It was an interesting project from both finance and IT points of view. I thought I would share it - it might be relevant to others' as there isn't that much documentation (on the actual implementation) as I found out when I was doing research for the project. I would also welcome any feedback.
Finance background: There are 3 common methods of measuring portfolio performance: Time Weighted Rate of Return (TWRR), Money Weighted Rate of Return (MWRR) and the Modified Dietz method (MDietz).
The TWRR method is the one that is recommended by the Global Investment Performance Standards (GIPS) as it allows direct comparison of performance results between different asset managers. It isn't influenced by the amount of money invested in the portfolio (or fund) on any given moment. The period returns in this methodology are geometrically chain-linked as if the monetary amount invested in each of the periods was 1. This measure thus makes the most sense for evaluation of the asset manager's performance irrespective of the fluctuation of the amount of money under his/ her management (of which he/she usually has no control).
On the other hand, the MWRR (which is the same concept as the Internal Rate of Return, IRR - or the XIRR function as implemented in Excel) is influenced by the amount of money at any given period. Imagine that we had $10 invested in the first period and only $1 in the second one. Using MWRR, the fund's return will be influenced 10x more by the first period return than the 2nd period's return. This is a suitable measure if you want to evaluate performance of an asset manager that has full discretion of the amount of money invested at any time (or to evaluate your own, investor's, performance when trying to time the markets).
The MDietz method is an approximation method of the MWRR. In the formula, the numerator is the amount of money earned on the investments. The denominator is the weighted average amount of money for the the given period.
Implementation: The pre-requisites for the computation of the 3 performance measures are as follows:
- Available valuations of the portfolios on any day of in/out flows in the portfolio (TWRR). This technically means that you need to have daily valuations of the portfolio (at the level of granularity you're using for your calculations, e.g. per asset class etc) available to feed your computations. For the MWRR and MDietz methods you only need the begining and ending market values for the whole period. N.B. that the market values of the portfolio on any given day need to include accrued interest for coupon paying fixed income securities.
- Dated list of all transactions that occurred in the period.
- Dated list of all income (dividends, coupons) received and any expenditures (fees, taxes...) paid in the period.
- If your transactions do not include any bond redemptions (due to their maturing, calls, puts), you will need to include them as well.
I implemented the functions directly inside the SQL server. As said before, you can use Excel for MWRR -- using the XIRR function -- however be aware that Excel's implementation of XIRR is notoriously unreliable.
In order not to reinvent the wheel (and write all the functions from scratch), I used the fantastic package of CLR functions for the SQL server - the XLeratorDB/financial (http://westclintech.com) - which comes with the 3 above mentioned functions as standard.
In order to facilitate the usage of their functions, I wrapped the XLeratorDB functions in my UDF (user defined functions) like so (This is a simplified version of my actual queries):
TWRR:
ALTER FUNCTION [TWRR]
( @Portfolio AS char(100),
@From_date AS date,
@To_date AS date
)
RETURNS float
AS
BEGIN
RETURN
(
SELECT wct.TWRR(c,d,mv) as TWRR
FROM (
--transactions
SELECT [Date], sum(Amount), 'False'
FROM Transactions
WHERE Portfolio = @Portfolio
AND [Date] BETWEEN @From_date AND @To_date
GROUP BY [Date]
UNION ALL
-- Income (dividends, interest...)- they are effectively outflows from the portfolio (fees and taxes are treated as inflows)
SELECT [Date], -sum(Amount), 'False'
FROM Income
WHERE Portfolio = @Portfolio
AND [Date] BETWEEN @From_date AND @To_date
GROUP BY [Date]
UNION ALL
-- Bond redemptions (not included in the transactions)
SELECT [Date], -sum(Amount), 'False'
FROM Redemptions
WHERE Portfolio = @Portfolio
AND [Date] BETWEEN @From_date AND @To_date
GROUP BY [Date]
UNION ALL
--market values
SELECT [Date], -sum(MV), 'True'
FROM Market_values
WHERE port.Portfolio_group_name = @Portfolio_group
AND [Date] BETWEEN dateadd(day, -1, @From_date) AND @To_date --the first market value needs to be 1 day before the period start
GROUP BY [Date]
) n(d, c, mv)
)
END
MWRR:
ALTER FUNCTION [MWRR]
( @Portfolio AS char(100),
@From_date AS date,
@To_date AS date
)
RETURNS float
AS
BEGIN
RETURN
(
SELECT POWER(1+wct.XIRR(c,d,NULL), wct.YEARFRAC(@From_date, @To_date ,1))-1 as MWRR
FROM (
--transactions
SELECT [Date], -sum(Amount)
FROM Transactions
WHERE Portfolio = @Portfolio
AND [Date] BETWEEN @From_date AND @To_date
GROUP BY [Date]
UNION ALL
-- Income (dividends, interest...)- they are effectively outflows from the portfolio (fees and taxes are treated as inflows)
SELECT [Date], sum(Amount)
FROM Income
WHERE Portfolio = @Portfolio
AND [Date] BETWEEN @From_date AND @To_date
GROUP BY [Date]
UNION ALL
-- Bond redemptions (not included in the transactions). Note that the amount values have positive signs
SELECT [Date], sum(Amount)
FROM Redemptions
WHERE Portfolio = @Portfolio
AND [Date] BETWEEN @From_date AND @To_date
GROUP BY [Date]
UNION ALL
--opening market value for the period
SELECT [Date], -sum(MV)
FROM Market_values
WHERE port.Portfolio_group_name = @Portfolio_group
AND [Date] = dateadd(day, -1, @From_date) --the opening market value needs to be 1 day before the period start
GROUP BY [Date]
UNION ALL
--closing market value for the period
SELECT [Date], sum(MV)
FROM Market_values
WHERE port.Portfolio_group_name = @Portfolio_group
AND [Date] = @To_date
GROUP BY [Date]
) n(d, c)
)
END
MDietz:
ALTER FUNCTION [MDietz]
( @Portfolio AS char(100),
@From_date AS date,
@To_date AS date
)
RETURNS float
AS
BEGIN
RETURN
(
SELECT wct.EMDIETZ(c,d) as Modified_Dietz
FROM (
--transactions
SELECT [Date], sum(Amount)
FROM Transactions
WHERE Portfolio = @Portfolio
AND [Date] BETWEEN @From_date AND @To_date
GROUP BY [Date]
UNION ALL
-- Income (dividends, interest...)- they are effectively outflows from the portfolio (fees and taxes are treated as inflows)
SELECT [Date], -sum(Amount)
FROM Income
WHERE Portfolio = @Portfolio
AND [Date] BETWEEN @From_date AND @To_date
GROUP BY [Date]
UNION ALL
-- Bond redemptions (not included in the transactions)
SELECT [Date], -sum(Amount)
FROM Redemptions
WHERE Portfolio = @Portfolio
AND [Date] BETWEEN @From_date AND @To_date
GROUP BY [Date]
UNION ALL
--opening market value for the period
SELECT [Date], sum(MV)
FROM Market_values
WHERE port.Portfolio_group_name = @Portfolio_group
AND [Date] = dateadd(day, -1, @From_date) --the opening market value needs to be 1 day before the period start
GROUP BY [Date]
UNION ALL
--closing market value for the period
SELECT [Date], -sum(MV)
FROM Market_values
WHERE port.Portfolio_group_name = @Portfolio_group
AND [Date] = @To_date
GROUP BY [Date]
) n(d, c)
)
END
Notes:
- For the functions syntax, refer to the functions' documentation (Doc) or here (Example).
- Note that this implementation calculates performance of segregated portfolios as opposed to individual positions or the aggregate portfolio as a whole.
- The XIRR function returns an annualized rate of return. That rate is de-annualized using the POWER function.
- The usage of the final functions is as simple as:
- SELECT TWRR('Equities', '2013-01-01', '2013-12-31')
- I'm currently trying to figure out the proper handling of net short positions - since the GIPS formula nor the implementation seem to properly account for them. If anyone has an idea, please share.
