Calculating IRR, DPI, RVPI, and TVPI, Part I
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 time series of the valuations, cash flows, and ratios. The second part will calculate the IRR.
As usual, all code can be found in my GitHub repository.
Setting up
Let's first set up some test data. I will use the same tables as for the Brinson-Fachler performance attribution, so please see Part I of that series, or download PerfTest tables and data.sql directly from GitHub, if you know what you're doing. Set up the schema, tables and indexes.
First, I am going to add a dedicated portfolio for the private assets, even though there is nothing preventing us having these assets in the same portfolios as any liquid assets. I just don't want to mess up the performance attribution results calculated previously. Also, I will add two Private Equity funds, one investing in North American mid market buyout, in USD, and the other investing in European Venture Capital, in Euros.
-- set up a new portfolio (not required, but not to mix up with other data) insert into PerfTest.Portfolio values (3, 'Private Assets','Private Assets Portfolio', 'EUR') -- set up two Private Equity Fund instruments insert into PerfTest.Instrument values (58, 'MMBO PE Fund', 'Middle Market Buyout Private Equity Fund', 'USD', 'US', 'North America', 'Private Equity', 'Private Equity'), (59, 'VC PE Fund', 'Venture Capital Private Equity Fund', 'EUR', 'DE', 'Europe', 'Private Equity', 'Private Equity')
In addition, as I know I will have very few private equity funds compared to the total number of instruments in table PerfTest.Instrument, I will add a filtered index I can then use to quickly find these all. A filtered index is useful, as it only stores data according to its where clause, limiting the disk space and updates needed, but still provides quick access to the data:
-- add a filtered index to support Private Equity asset class queries create index Instrument_IX#InstrumentIK$Private_Equity on PerfTest.Instrument (InstrumentIK asc)
include (ShortName, LongName, Currency) where AssetClass = 'Private Equity';
Next, we'll need the fund transactions in table PerfTest.Transact. Private Equity funds are rarely bought or sold; instead, everything starts with a Commitment to the fund. Then, capital is called and later returned; in the meantime, profits can be paid out or costs incurred. There are quite a number of different transactions that are used in the real world; to keep things simple, I have only used Commitment, Capital Call, Capital Return, Profit Distribution, and Fund Closed. Only the first and last will have non-zero Units in table PerfTest.Transact, so that if Commitment is e.g. USD 5 mln, Fund Closed should be USD -5 mln to bring cumulative units back to zero - Units represents Commitment. All the other transactions only have a CurrencyValue, which creates a cash flow, but does not affect cumulative units. Also note that I have ignored fees, so you will not see the familiar J-curve in the example, either.
The funds also need valuations or NAVs (Net Asset Values). NAVs may sometimes be investor specific, unlike in mutual funds, and are thus often simply expressed in total currency value, not per any units. However, to work with the current tables, I have added NAVs as price/unit in table PerfTest.Price. The price (NAV) at the time of Commitment will be zero, as no capital has been called and the asset owner has not paid anything to the fund yet. Then with capital calls, the NAV will rise towards 1.00, and when capital is returned and profits paid, return back to zero. Of course, if the fund does well, NAV may go above 1.00 as well at some point. For the two funds we set up, there will be semiannual NAVs for their 7 and 9 year lives.
For valuations (both NAVs and cash flows) in instrument currency and in portfolio currency, FX rates are required on the NAV and cash flow dates.
The additions to tables PerfTest.Transact, PerfTest.Price, and PerfTest.FXRate are about 150 rows in total, so I have not added them here. Please download and execute PerfTest IRR test data.sql (in GitHub).
Getting Cash Flows and Prices
We are now ready to use the data to get the time series of each fund with all cash flows and valuations. I am building a view, defined in the database, for easier use in later applications. The code for the entire view (about 200 rows) can be found in vPEFundMarketValueAndCashFlowByCCY.sql, and, as the name implies, it will also have the data in two currencies, the instrument currency and the portfolio currency.
To start with, let's get a list of all the AssetClass Private Equity instruments and portfolios which have any transactions. I have defined a CTE (Common Table Expression) pf_instr:
with pf_instr as ( -- get the portfolios and instruments, limit to 'Private Equity' AssetClass only. select distinct t.PortfolioIK, i.InstrumentIK, i.ShortName, i.LongName, i.Currency as InstrumentCurrency from PerfTest.Transact t inner join PerfTest.Instrument i on i.InstrumentIK = t.InstrumentIK where i.AssetClass = 'Private Equity' ),
While the distinct operator guarantees just one row per portfolio and instrument, it is quite expensive to use, requiring data to be sorted. However, the filtered index we set up earlier will help somewhat here, especially if Private Equity instruments are only a small fraction of all instruments. Besides, if we can limit the queries using this view to a small number of portfolios by using e.g. where PortfolioIK = 3 in the final query, that will help as well. Note that to actually use the filtered index, we need to have the exact same where clause AssetClass = 'Private Equity' in both the index and the query.
With our test data, pf_instr will produce just two rows of data:
Anyway, we need to know in which portfolios each instrument is used to combine the valuations to our data - as the valuations do not have any portfolio information - in the following CTE, which uses the CTE pf_instr just defined above:
-- combine transactions with valuations to get a full time series of calculation dates. trans_valuations as ( -- get all transactions, add Price prevailing on TradeDate. select pin.PortfolioIK, pin.InstrumentIK, pin.ShortName, pin.LongName, pin.InstrumentCurrency, t.Currency as TransCurrency, t.TradeDate as ToDate, t.Units, t.CurrencyValue, pr.PriceDate, pr.PriceCurrency, pr.Price from pf_instr pin inner join PerfTest.Transact t on t.PortfolioIK = pin.PortfolioIK and t.InstrumentIK = pin.InstrumentIK cross apply ( -- get the latest Price. select top 1 p.PriceDate, p.PriceCurrency, p.Price from PerfTest.Price p where p.InstrumentIK = pin.InstrumentIK and p.PriceDate <= t.TradeDate order by p.PriceDate desc ) pr
Above is the first part of trans_valuations CTE, getting all the transactions for the portfolios and instruments in pf_instr. In addition, it uses cross apply with a select top 1 to get the latest price available on the date of the transaction, so that we have the prevailing NAV with each transaction as well.
Below, the second part of the CTE adds all valuation dates and prices using a union all operator. To avoid getting the same data twice, the dates when a transaction has already provided the date and price, are filtered out. Pure price data will not affect Units or CurrencyValue (cash flow).
union all -- add all valuation date Prices. select pin.PortfolioIK, pin.InstrumentIK, pin.ShortName, pin.LongName, pin.InstrumentCurrency, null as TransCurrency, p.PriceDate as ToDate, 0 as Units, 0 as CurrencyValue, p.PriceDate, p.PriceCurrency, p.Price from pf_instr pin inner join PerfTest.Price p on p.InstrumentIK = pin.InstrumentIK where -- transaction TradeDates already accounted for in the first select, so exclude them here. not exists (select 1 from PerfTest.Transact t where t.PortfolioIK = pin.PortfolioIK and t.InstrumentIK = pin.InstrumentIK and t.TradeDate = p.PriceDate) ),
trans_valuations now provides all transactions and valuations for all portfolios and instruments (where AssetClass = 'Private Equity').
Looking at the first five rows of InstrumentIK 58 (MMBO PE Fund) ordered by ToDate, we have the following:
In the first row, we have the commitment to the fund, USD 5 mln. The first valuation, on row 2, is still zero, as the fund has not called in any capital yet. The first capital calls are on rows 3 and 4, with the latest valuation (price) still zero. Note that the CurrencyValue being positive is, in a a way, positive from the fund instrument perspective; from the cash account perspective, it is negative, i.e. cash is being invested in the instrument. The first real valuation then appears on row 5, valuing the fund at 0.26 or 26 % of committed capital.
Multi-Currency
Note that I have nested two CTEs using with so that the second, trans_valuations, refers to the first, pf_instr (twice). Let's add a third CTE, dates, referring to trans_valuations (not fully shown below - refer to the vPEFundMarketValueAndCashFlowByCCY.sql file). What this third CTE does is it adds FX rates and some portfolio identifiers, and sums up the data to one row per ToDate and CalculationCurrency. I am simply joining PerfTest.FXRate to trans_valuations, for each of the four currencies defined (Portfolio, Instrument, Transaction, and Price) and calculating the FX rates I want to output data in. That is done in a cross apply:
cross apply ( -- using cross apply to generate two rows for each data row from trans_valuations, one for Instrument Currency and one for Portfolio Currency, with respective FXRates. -- other currencies like ReportingCurrency or SystemBaseCurrency could be added, if required. select ccy_data.CalculationCurrencyType, ccy_data.CalculationCurrency, ccy_data.FXRateTrans, ccy_data.FXRatePrice from ( values ('InstrumentCurrency', tv.InstrumentCurrency, fx.FXRateTrans / fx.FXRateInstrument, fx.FXRatePrice / fx.FXRateInstrument), ('PortfolioCurrency', por.Currency, fx.FXRateTrans / fx.FXRatePortfolio, fx.FXRatePrice / fx.FXRatePortfolio ) ) ccy_data(CalculationCurrencyType, CalculationCurrency, FXRateTrans, FXRatePrice) ) ccy
The cross apply takes all the rows, with four FX rate columns each, and creates two rows from every single row: One row for InstrumentCurrency and one row for PortfolioCurrency. Each row will have a CalculationCurrency, and the FX rates required to transform transaction CurrencyValue and valuation Price into that currency. Our data are rather simple here, with only one portfolio with PortfolioCurrency EUR, and two funds with InstrumentCurrency USD and EUR. So for the first fund, we will get two sets of cash flows and valuations, one in USD and one in EUR, and for the second fund, again two sets of cash flows, but in this case both in EUR. However, the code can handle any currencies, so e.g. valuations could be given in different currencies every day, and also transactions may have any currency denominated CurrecyValues. Just remember that the applicable FX rates must then also be available in table PerfTest.FXRates.
What we now have, for the same first five dates for MMBO PE Fund, is ten rows:
You'll note that for every date we now, indeed, have two rows: one for InstrumentCurrency and one row for PortfolioCurrency, and the CalculationCurrency. We no longer need TransCurrency or PriceCurrency information. While units do not depend on currency, both Price and CashFlow are now expressed in CalculationCurrency.
Also, it would be easy to add more currencies, e.g. ReportingCurrency as a third currency, just adding a third row to the cross apply's values clause and another join to get the respective FX rate.
In addition to the currency, I am also adding two columns for the ratio calculations:
sum(convert(float, case when tv.CurrencyValue > 0 then tv.CurrencyValue else 0 end) / ccy.FXRateTrans) as CapitalCall, sum(convert(float, case when tv.CurrencyValue < 0 then -tv.CurrencyValue else 0 end) / ccy.FXRateTrans) as Distributions
In effect, I am calculating CapitalCall as the sum of positive CurrencyValues, as these are cash flows from the investor to the fund (Capital Paid-In), and Distributions as the sum of negative CurrencyValues, as these are cash flows from the fund to the investor. In practice, the cash flows are often more complicated, and this may need to be done based on some transaction type identifier to give correct results.
Cumulative Time Series
To calculate the ratios DPI, RVPI, and TVPI, we need to know the cumulative Distributions and Paid-In Capital, and also the Residual Value of the fund. Residual Value is simply the current market value, Units x Price adjusted by a cash flow adjustment. The cash flow adjustment is needed, as we only have semiannual valuations. Let's say the NAV at the end of June was 0.4, and our commitment = Units = USD 5 mln. Then market value would be USD 2 mln at the end of June. Let's then say there was a capital call of USD 1 mln at the end of August. Surely the best estimate of fund value must now be USD 3 mln, even if Units and Price are the same as before, as we'll only have a new NAV at the end of December. The same applies, of course, if the fund returns capital, but now with a negative adjustment.
To calculate the units, market value, cash flow adjustment, and cumulative capital calls and distributions on all dates, I am adding yet another CTE called cum_data, with the CTE dates as its only data source. Here, I am using window functions to calculate the cumulative sums from the beginning to the current date. Note that the partition by clause is different for CashFlowAdjustment, as it includes PriceDate. This way, CashFlowAdjustment starts from zero with each new valuation. In addition, the cross apply makes sure we do not apply a cash flow adjustment on a valuation date, as the valuation should already incorporate the effect of the cash flow.
select -- [all columns not listed here] sum(d.Units) over (partition by d.PortfolioIK, d.InstrumentIK, d.CalculationCurrencyType order by d.ToDate rows between unbounded preceding and current row) as CumUnits, convert(numeric(19,2), sum(d.Units) over (partition by d.PortfolioIK, d.InstrumentIK, d.CalculationCurrencyType order by d.ToDate rows between unbounded preceding and current row) * d.Price) as MarketValue, convert(numeric(19,2), sum(cf.CashFlowAdjustment) over (partition by d.PortfolioIK, d.InstrumentIK, d.PriceDate, d.CalculationCurrencyType order by d.ToDate rows between unbounded preceding and current row)) as CashFlowAdjustment, convert(numeric(19,2), sum(d.CapitalCall) over (partition by d.PortfolioIK, d.InstrumentIK, d.CalculationCurrencyType order by d.ToDate rows between unbounded preceding and current row)) as Cum_CapitalCall, convert(numeric(19,2), sum(d.Distributions) over (partition by d.PortfolioIK, d.InstrumentIK, d.CalculationCurrencyType order by d.ToDate rows between unbounded preceding and current row)) as Cum_Distributions from dates d cross apply ( -- use cash flow adjustment only after price date, as on price date it is already affecting the price. select case when d.PriceDate < d.ToDate then d.CashFlow else 0.00 end as CashFlowAdjustment ) cf
The first ten rows (with selected columns only) now look like this:
select *, try_convert(numeric(19,4), convert(float, Cum_Distributions) / nullif(Cum_CapitalCall, 0)) as DPI, try_convert(numeric(19,4), convert(float, MarketValue + CashFlowAdjustment) / nullif(Cum_CapitalCall, 0)) as RVPI, try_convert(numeric(19,4), convert(float, MarketValue + CashFlowAdjustment + Cum_Distributions) / nullif(Cum_CapitalCall, 0)) as TVPI from cum_data where CumUnits > 0 or Units < 0; -- include possible final "sell" when CumUnits drops to zero.
The formulas for the ratios are now very simple, given that we have all the data nicely in place. The view is now ready, and we can start using it. Execute the script vPEFundMarketValueAndCashFlowByCCY.sql to create the view. Let's then execute the following query to get the data for InstrumentIK = 58 (MMBO PE Fund) in InstrumentCurrency (USD):
select ToDate, CumUnits, MarketValue, CashFlowAdjustment, MarketValue + CashFlowAdjustment as AdjustedValue, Cum_CapitalCall, Cum_Distributions, DPI, RVPI, TVPI from PerfTest.vPEFundMarketValueAndCashFlowByCCY where PortfolioIK = 3 and InstrumentIK = 58 and CalculationCurrencyType = 'InstrumentCurrency' order by ToDate;
The results have 44 rows, of which the first ten and the last five are shown below:
You can see how USD 4.938 mln (out the commitment of USD 5 mln) was called, and USD 6.78 mln was returned to the investor in the form of capital returns and profits. In the beginning, there was little in the form of distributions, so RVPI made the bulk of TVPI, while at the end, DPI was all that was left. In the end, the fund returned 1.373 times the capital it called from the investor.
Finally, let's run another query. Perhaps we do not need the time series, but only the data for all closed funds. Closed funds will have CumUnits = 0, so let's adjust the query, add instrument and currency data and drop uninteresting metrics:
select ToDate, ShortName, CalculationCurrencyType, CalculationCurrency, Cum_CapitalCall, Cum_Distributions, TVPI from PerfTest.vPEFundMarketValueAndCashFlowByCCY where PortfolioIK = 3 and CumUnits = 0;
And the results, for both funds and the two currencies each, with closing date shown:
Now, by the TVPI metric, the VC PE Fund was the better of these two investments, returning 1.6 times investment.
Next, I will add a table valued function, based on the the cash flow and valuation data in this view, to calculate the internal rate of return (IRR) for the funds. Which fund will win by that metric? Read more in Part II.
Comments
Post a Comment