SQL and Investment Performance Measurement

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...

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 ...