Say you invest $1000 into a venture on day 1. Then you invest another $1000 on day 365. Then, on day 730, its market value is worth $4000.
If I were to put these numbers into an Excel spreadsheet like:
Date | Value |
---|---|
2000-1-1 | $1000 |
2001-1-1 | $1000 |
2002-1-1 | -$4000 |
and then use XIRR
to calculate the internal rate of return with the formula =XIRR(B2:B4, A2:A4)
, it would say I have a return of 56.08%.
However, this value doesn't make any sense in the context of non-zero inflation.
Hypothetically, say in the course of those two years, the mean rate of inflation is 100%. Meaning that even though I added $2000 to my investment, the value of the the dollar dropped by half, so though I now have $4000, in practical terms of buying power I actually have the same amount of money, and thus had an effective interest rate of 0%.
That's just an extreme example meant to make a point, and obviously we don't usually have an inflation rate of 100%, but inflation is still far from zero. So how do you include that when calculating IRR?
Is the simplest method to apply the rate of inflation to the final value before it's used by XIRR
, meaning I should write -$2000 instead of -$4000? The only problem I see with that method is that it requires a common temporal frame of reference when comparing investments, in this case the year 2000. So if I wanted to compare rate of returns of competing investments, I'd have to retroactively estimate all their rate of returns using a rate of inflation since the year 2000 as well.
I've seen some articles claim the guess
field in Excel's IRR
and XIRR
functions are meant for entering the inflation rate, but I disbelieve this, as regardless of what value I enter for the guess, the calculated rate is the same, which is nonsensical if that actually represents the rate of inflation.