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

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 view or a sub-query, and will, in general, be much better at estimating e.g. how to best join the function output to other tables in the query. Also, it can take predicates (from where clause) into the function, limiting data required (and hence I/O cost) earlier. For example, my function will, by default, calculate IRR for all portfolios and funds found in the data tables. As an inline function, it will be quick to calculate results for just one portfolio and fund (if so limited in the where clause), as well, while if it were a multi-statement function, it would first calculate results for all portfolios and funds, and the where clause would then just filter out all but the one required - much slower.

While an inline TVF is an elegant set-based solution, it is more difficult to set up than a multi-statement TVF. Sometimes so much so, that a multi-statement function is the only option, or may anyway be more efficient. I was unable to find any similar inline TVF solutions, although there most probably are some. Mostly IRRs seem to be solved either outside the database, or using while loops in a multi-statement TVF or stand-alone code. For this use case, maybe a couple of multi-statement functions (one for a single fund, another for a portfolio) might not be a bad idea, either. 

To set up the required view vPEFundMarketValueAndCashFlowByCCY, please see Part I.

For discussion about IRR and how to calculate it, please see Part II.

As usual, the full function code is available in my GitHub repository (uftIRR.sql).

First, Cash Flows

In Part II example I set up a table variable to hold three cash flows with respective dates, and inserted data as values. Here, I want to have the cash flows from view vPEFundMarketValueAndCashFlowByCCY, and keep the data on which Portfolio, Instrument, and Currency they belong to. The data should be limited by a beginning date (@from_date) and an end date (@to_date), for reporting a specified period. I am defining a CTE (Common Table Expression): 

with 

	-- define cash flows, using the prevailing market values to represent investment on @from_date and residual value on @to_date
	cashflows_instrument as (

		-- cash flows after @from date and on or before @to_date
		select
			PortfolioIK,
			PortfolioShortName,
			PortfolioLongName,
			PortfolioCurrency,
			InstrumentIK,
			ShortName,
			LongName,
			InstrumentCurrency,
			CalculationCurrencyType,
			CalculationCurrency,
			ToDate,
			convert(float, datediff(day, @from_date, ToDate)) / 365 as CashFlowTerm,
			-CashFlow as CashFlow,
			0 as IsEndValue
		from
			PerfTest.vPEFundMarketValueAndCashFlowByCCY
		where 
			ToDate > @from_date
			and ToDate <= @to_date
			and CashFlow <> 0 				

I am simply getting all cash flows (omitting zero cash flows, though) between the dates. Note that my date convention is to calculate from end-of-day to end-of-day, so e.g. year 2023 would be @from_date = 2022-12-31 to @to_date = 2023-12-31, and, hence, @from_date is excluded. I am also changing the sign of CashFlow, as in the view positive CashFlows are investments (additions) to the fund, and negative CashFlows are returned cash (subtractions) from the fund, while in IRR calculations they have to be of the opposite sign. The CashFlowTerm is time in years from @from_date, using 365 days as a year.

If the fund started after @from_date and ended before @to_date, I would have all the cash flows ready. However, to calculate a sub-period of the fund's life, I need to use its (negative) value on @from_date as a proxy for investment, and its value on @to_date as a proxy for final cash flow. Since I need to keep data on Portfolio, Instrument and Currency separated, I need to first get the latest date for each of those where we have a value, and then pick those values. Adding this to the CTE provides the end value as a positive cash flow on @to_date, or should the fund be closed during the calculation period, on its closing date:

		-- prevailing market value (adjusted for cash flows) on @to_date as a positive cash flow (= ending value)
		union all
		select 
			a.PortfolioIK,
			a.PortfolioShortName,
			a.PortfolioLongName,
			a.PortfolioCurrency,
			a.InstrumentIK,
			a.ShortName,
			a.LongName,
			a.InstrumentCurrency,
			a.CalculationCurrencyType,
			a.CalculationCurrency,
			c.ToDate,
			convert(float, datediff(day, @from_date, c.ToDate)) / 365 as CashFlowTerm,
			(a.MarketValue + a.CashFlowAdjustment) as CashFlow,
			1 as IsEndValue
		from
			PerfTest.vPEFundMarketValueAndCashFlowByCCY a
			inner join (
				select PortfolioIK, InstrumentIK, CalculationCurrencyType, max(ToDate) as max_date 
				from PerfTest.vPEFundMarketValueAndCashFlowByCCY 
				where ToDate <= @to_date
				group by PortfolioIK, InstrumentIK, CalculationCurrencyType
			) b	on b.PortfolioIK = a.PortfolioIK and b.InstrumentIK = a.InstrumentIK and b.CalculationCurrencyType = a.CalculationCurrencyType and b.max_date = a.ToDate 
			cross apply (
				select
					case
						when a.CumUnits > 0 then @to_date  -- fund still active on @to_date
						when a.ToDate <= @from_date then null  -- fund was closed on or before @from_date
						else a.ToDate  -- fund closed between @from_date and @to_date, use closing date
					end as ToDate
			) c
		where
			c.ToDate is not null  -- fund was closed on or before @from_date, exclude it

	),

The inner join provides the latest date, while the cross apply checks if the fund was still active and picks the date accordingly. As CashFlow, I am using MarketValue + CashFlowAdjustment, where the first term is simply the latest NAV, and the second term reflects any subsequent investments to or distributions from the fund (see Part I).

The beginning cash flow is added to the CTE in a very similar manner. 

Now having all the cash flows for the period for each Portfolio, Instrument, and Currency, I wish to add a portfolio level to that. This way, all the cash flows within a portfolio will be combined, and a Portfolio level IRR can be calculated. Note that IRRs can not be combined, chained, or averaged; you always need to recalculate from the cash flows.

	-- -- add Portfolio level. Accept PortfolioCurrency as the only currency type, others may not be using just one currency.
	cashflows as (

		select
			*
		from
			cashflows_instrument

		union all

		select
			PortfolioIK,
			PortfolioShortName,
			PortfolioLongName,
			PortfolioCurrency,
			-1 as InstrumentIK,
			'Total Portfolio' as ShortName,
			'Total Portfolio' as LongName,
			null as InstrumentCurrency,
			CalculationCurrencyType,
			CalculationCurrency,
			ToDate,
			CashFlowTerm,
			CashFlow,
			-- mark just one of the cashflows with IsEndValue = 1 on portfolio level.
			case
				when row_number() over (partition by PortfolioIK, CalculationCurrencyType order by ToDate) = count(PortfolioIK) over (partition by PortfolioIK, CalculationCurrencyType) then 1
				else 0
			end as IsEndValue
		from
			cashflows_instrument
		where
			CalculationCurrencyType = 'PortfolioCurrency'

	),

I am simply setting cashflows as a combination of the instrument level cashflows_instrument and the same combined to -1 as InstrumentIK. However, I cannot include InstrumentCurrency, as I might end up mixing different currency cash flows, so I am limiting these data to PortfolioCurrency. Also, I am taking care that just one of the rows for each Portfolio will have 1 as IsEndValue. This is later used in the recursive loop, and each IRR being calculated needs to have exactly one IsEndValue.

The Path to IRR(s)

IRRs are calculated exactly the same way as in Part II, but since I may have many portfolios, instruments, currencies, and totals in the one set of cash flow data, I need to keep them separate, obviously. Adding some columns to describe these is required, and in the recursive member sum window function I also need to partition by id.PortfolioIK, id.InstrumentIK, id.CalculationCurrencyType. This way the NPV - and, hence, IRR - is calculated for each Portfolio, Instrument, and CalculationCurrencyType separately. Note that the check if the calculation is ready and the loop may be terminated is also calculated for each IRR separately - some recursive loops may finish earlier than other.

For each IRR, I am also adding minimum and maximum dates of cash flow (and valuation) data for reference. There is also a column IsFinal, which just makes querying for those final results (where IterStatus not in ('Starting', 'Iterating')) a little bit easier. So querying for just the final results in all of my sample data (after creating the function):

select 
	PortfolioShortName,
	ShortName,
	CalculationCurrencyType,
	CalculationCurrency,
	MinDate,
	MaxDate,
	IterNr,
	IterStatus,
	IRR
from 
	PerfTest.uftIRR('2000-01-01','2024-10-31') 
where 
	IsFinal = 1 
order by 
	PortfolioShortName,
	InstrumentIK, 
	CalculationCurrencyType

The results are as follows:

The total portfolio took seven iterations to find its IRR = 11.95 %, while the two funds needed nine and eight iterations for both currencies. In EUR, the funds' IRRs were 6.98 % and 15.56 %.

In the chart below, you can see how the iterations got closer and closer to the final IRR, where NPV was ~0. All started at the first guess of 10 %.  

You can use the function but limit the results to a specific portfolio, fund, or both, as it is an inline table valued function. Also, you can use it to calculate e.g. annual IRRs (not necessarily recommended, but possible) in the following way. In this example I wish to report the MMBO PE Fund in portfolio Private Assets in PortfolioCurrency on an annual basis:

select 
	years.[value] as yr,
	MinDate,
	MaxDate,
	IterStatus,
	IterNr,
	IRR
from 
	generate_series(2010, 2024) as years
	-- if running SQL Server prior to 2022, comment out the line above and use values below instead
	--(values (2010),(2011),(2012),(2013),(2014),(2015),(2016),(2017),(2018),
	--        (2019),(2020),(2021),(2022),(2023),(2024)) as years([value])
	cross apply (
		select
			datefromparts(years.[value] - 1, 12, 31) as from_date,
			datefromparts(years.[value], 12, 31) as to_date
	) d
	cross apply PerfTest.uftIRR(d.from_date, d.to_date) irr
where
	irr.PortfolioShortName = 'Private Assets'
	and irr.ShortName = 'MMBO PE Fund'
	and irr.CalculationCurrencyType = 'PortfolioCurrency'
	and irr.IsFinal = 1
order by 
	yr;

And the results:

Practical Considerations

With a large number of portfolios and instruments, running the IRRs based on a cash flow view built on top of transactions and prices might easily be quite resource consuming. For an idea of the complexity, take a look at the execution plan of the last query! In practice, however, most of data are static, like in my examples the cash flows, prices, and exchange rates, most of which have been set years ago. It would probably make a lot of sense to e.g. use a procedure to store daily or monthly valuations and the cash flows, and only update those data when there are changes in the underlying data (prices, transactions). Or, alternatively, an indexed view might do the same - but since with and apply are not allowed, you would have to refactor my view vPEFundMarketValueAndCashFlowByCCY completely.

Also, it might be interesting to compare the performance of this inline function - a set-based approach - to the perhaps more intuitive (at least to those used to procedural programming) multi-statement while loop solution (see e.g. Pinal Dave's blog). However, this might depend on the underlying cash flow data a lot, so doing it in my PertTest environment might not give good answers for some other environment.

One might also consider changing the NPV criteria abs(iter_set.NPV) <= 0.01 to something like abs(iter_set.IRR - iter_set.IRR_Previous) < 0.00001, which would be more independent of the size of the investments, but still make sure IRR is accurate enough.