Money-Weighted Returns in Excel
Beyond “Don’t Use” – How (Not) to Calculate Money-Weighted Returns in Excel
If cost was of no matter, then asset managers would all own (fill in the blank with your favorite accounting system).
For emerging asset management firms, cost does matter. When managing a start-up commingled fund, an investment firm only needs to calculate a single return stream to comply with best practices in the GIPS® standards. It’s hard to justify a six-figure expense when Microsoft Excel is free, never mind the pitfalls.
In literature, we read “Don’t use Excel” a lot. In practice, we see start-up firms using Excel for performance and other required GIPS statistics. Geometrically-linked, time-weighted returns, asset-weighted composite returns, dispersion and volatility statistics, PIC ratios… Excel can do it all. Footing calculations and audit trails can be built in… as a starting place, until the firm reaches (fill in the blank with your target AUM before investing in your favorite accounting system).
The 2020 GIPS exposure draft proposes more flexibility for firms to use money-weighted returns (MWRs) as an alternative to time-weighted returns. When using Excel for money-weighted returns, though, beware the XIRR and IRR Excel formulas—the built-in assumptions can lead to inaccurate and prohibited results.
Excel XIRR – it’s great for periods of one year or more, because it’s an annualized return, which is required in the proposed 2020 GIPS provisions1. The XIRR formula in Excel also allows daily dates to be entered in the calculation, so that cash flows are weighted daily during the period, another proposed GIPS requirement for MWRs2. However, the GIPS standards prohibit firms from annualizing performance for less than a year, because annualizing returns for periods less than one year inflates the results by the assumption that assets could be reinvested and earn the same return over and over again for a full year. The four XIRR results below demonstrate how unadjusted XIRR will calculate very different returns for the same cash flow streams (same starting value, interim cash flow 2/3 of the way through the period and ending flows/value). The differences in the XIRR results are due to the dates entered in the Excel XIRR formula.
Jumping to the last row in the example above: What’s going on with the Excel IRR calculation of a 6.08% return? The Excel IRR formula assumes equal cash flows at regular intervals by factoring in a midpoint assumption. If cash flows are not equal at regular intervals, as in the examples above, the return stream calculation is inaccurate. Because of the requirement to use daily-weighted cash flows, the Excel IRR formula is not permitted either, unless there are no cash flows. Modified Dietz and the IRR formula in Excel will generate the same return in any of the examples above if the two cash flows are removed, but only the Excel XIRR formula will correctly annualize periods greater than one year.
How should investment firms calculate MWRs in Excel during the first three quarters of a funds life?
Using a De-Annualized XIRR is the most straight-forward approach, and it will calculate performance within a few basis points of the Modified Dietz formula published in the GIPS standards and the earlier AIMR-PPS white book since 1993 as a good approximation of IRR. The Modified Dietz formula is a more manual view into how performance is derived and a useful reasonableness check for periods up to one year.
XIRR De-Annualized: (1+[XIRR result])^(Days/365)-1
- To calculate the # of Days in the numerator: Type “Days([End of period date, Beginning of period date]), and Excel will count the days for you.
Modified Dietz numerator: Ending value minus beginning value minus cash flows, and you don’t even have to worry about weighting those cash flows.
Modified Dietz denominator: Beginning value plus cash flows weighted for the amount of time in the period the firm had the funds to put to work. At the end of every new performance period during the first year, the weight of the flows from prior periods in the denominator will need to be adjusted when calculating a since-inception money-weighted return, since the amount of time the firm has to put the funds to work will increase over time.
For periods of 12 months or more, Excel XIRR can be used for annualized since-inception money-weighted returns, in compliance with the GIPS standards.
1 2020 GIPS provision 2.A.31.a.
2 2020 GIPS provision 2.A.31.b.
Questions?
Contact us: Exchange@cascadecompliance.com
Thank you for the great article. I agree with everything said. My only question is about IRRs for periods of less than one year. How about using the XIRR formula, and then de-annualize the return?
Thank you.
Yes, you can de-annualize. Your comment prompted further conversations with a younger, more Excel-savvy co-worker who showed me an excellent “days” functionality for de-annualizing. Results are within 2 or 3 basis points of the more manual Modified Dietz approach. We’re updating the article to incorporate. Thank you, Petya.