How do you factor in inflation when calculating internal rate of return on an investment?

Question

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.

Answer 1

However, this value doesn't make any sense in the context of non-zero inflation.

I disagree. The purpose of using IRR (which is what the XIRR function calculates) it to be able to compare investments. All else being equal, a higher IRR is a "better" investment.

Inflation is an economy-wide metric. It affects all investments. Yes inflation would change the real result of projects with different timeframes, but you should be taking the timeframe into account when comparing projects regardless of inflation.

If you want to calculate the real return (meaning the return after inflation is accounted for), then you could just apply the total inflation factor to your IRR. So instead of a 56.08% return, after 100% inflation (meaning the purchasing power of your money is half of what it was), your real return would be 28.06% (R/(1+I) = 50.06/(1+1))

If you want to take inflation into account over the life of the project, you'd have to adjust all cash flows for inflation, not just the "last" one.

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

Your disbelief is correct - XIRR does NOT take inflation into account. IRR is calculated iteratively, meaning that you start with an initial "guess" at the return and adjust it until the net present value of all cash flows in zero. Depending on the cash flow schedule, it's possible to have multiple IRRs, or the iterative method can fail if you don't start with a number that's in a convergence range. Setting a different guess can give you a different result depending on the schedule. But it has absolutely no economic meaning.

Answer 2

(I initially read your question to mean the inflation average was 100% over two years, not 100% for each of the two years, so the below answer follows from that assumption.)

Your inflation-adjusted yearly return would be 10.42%.

First, one note - XIRR compounds annually assuming a 365-day year, and your date range has a leap year, so that can cause some subtle confusion. If you had picked 2001-2003, your XIRR would be 56.16%.

Anyway, aside from that, starting with your premise that inflation is 100% over two years, that means that on 2002-01-01, you have to spend $200 to buy what you could have bought for $100 on 2000-01-01.

The first step is to figure out what that annualized inflation rate actually is. To do that, you figure out the ratio of the beginning and ending values, which in this case is 2, and then raise that to the power of the reciprocal of the time duration, and subtract 1. In this case, the ratio is just over 2 years (leap year), so the formula is (2^0.499) - 1. Annualized, that is an inflation rate of 41.35%.

So now you have an unadjusted return of 56.08%, and an inflation rate of 41.35%, and the objective is to combine them.

I've seen recommendations that for inflation adjustments, you can just subtract the annualized inflation rate from the yearly return, but that is very incorrect. Instead, you have to divide them. This is because when figuring out an accurate average of rates over time, and since rates are multiplied, you have to use geometric averages.

In your case the formula would be (1+0.5608)/(1+0.4135) - 1, which is 10.42%.

An alternate way to figure this out would be to inflation-adjust each contribution as of the date of contribution, in terms of the final date. So 2000-01-01 would be in 2002's dollars, and 2001-01-01 would be in 2002's dollars. Then you could run a separate XIRR calculation on that. If you assume the inflation rate is constant between the two years, so that 2001's purchasing power is ~141.49, then an XIRR calculation of the inflation-adjusted values would yield an identical inflation-adjusted rate.

So from there it's your choice. If you only care about annualized inflation calculated from the beginning and ending dates, you would divide as described above. If you wanted to respect how inflation goes up and down within that time period, you could inflation-adjust each contribution and run XIRR. I'd recommend just using the annualized figure, though.