IRR vs XIRR vs MIRR

Hey, this is an additional lecture on discussing the difference between several of the IRR functions. So it's actually several different IRR functions in Excel, and there are some nuanced differences. I use the X IRR instead of the normal IRR, but there's also another one called the Modify IRR, which is more accurate and some ways and we're going to discuss it in this lecture, but I'll also discuss why it still might be a good idea to use the IRR instead. So stay tuned if you're curious about it. In this short discussion, we're going to look at the differences in IRR stands for internal rate of return and the best way to think about what an internal rate of return is. If you have an investment that requires some initial cash investment and then you're going to get some returns back over it a period of time, if you were to look at it on an annualized basis, what would that return rate be? And that's your internal rate of return. So however, the cash flows are laid out for the different investment options. If it were laid out as though you were getting a consistent annual return, what would that return equivalent be? That's your internal rate of return. Okay. So let's see an example here. Let's look at the first eight investments here, all ten investment, actually, they all require an investment of, say, $2,000 right now, investment. One, you get these cash flows over the course of five years. So the total cash flow in during those five years is equal to 4000. So investment went through eight have the exact same cash flow over the first five years, except the amounts and the timing within those five years are different, as you can see here. So the IRR. Is simply i. RR. And we select the values from the time of investment to the last year of the cash flows. And then we hit oak. Now we can copy and paste this. Formula Control Option V. Formula. Okay. And calculate that down. So we see that even though the cash flow over the five years, the total cash flow is identical and even though the initial investment is identical for both of these, the IRR is quite different. We see that as the cash flow return gets progressively earlier. In terms of when you get that investment back, your IRR is higher. So what that means is the earlier you get your returns a faster you get your investment, cash flow or profits, the better the investment. Now we can copy the formula as well for these investments here. These are a little bit different. I'm going to talk about it a little bit later, but let's just put the formulas here as well. And we're going to paste that there and notice that the IRR are identical for these investments. Okay. So one of the big. The issues that folks have with IRR is that the IRR itself assumes that any of the cash flows that is received during the period of the investment is reinvested at the rate of the return. For most people, that's not going to be true. Right. You have the investment returns and they may put it in a bank or they may invest it in something else. And that's why many folks, instead of using the IRR, they use what's called a modified IRR function that accounts for this shortcoming of the IRR itself. It doesn't assume all the returns are reinvested at the same return rate. So to use the IRR or the modify em IRR, we use em IRR. Again, we take the period from the investment to the last year of cashflow. Then we enter what's called the finance rate. That is, if you borrow the money for the investment. What was your rate of the financing? The cost of financing? So you think of it as an interest rate. If you have to borrow money, what would that be? Let's say you borrowed it at 4%. And then the amount that you get back during the investment, what do you think your reinvestment rate could be now? If you put in a bank, that's probably going to be somewhere between one or 2%. Maybe some folks want to put it in a mix of low risk treasury bonds and municipal bonds, and maybe they're able to get aggressively 5% return. We're going to assume that for their example. Now we see that the modified RR compared to the RR is a little bit lower. Right. So if we select these and copy the formula, just the formula. We see that the IRR for these investments. Aren't that much different. Yes, this last one is still better than the first investment because you're getting it way earlier, but it's not 100% versus 17% better. It's 19 versus 15% better. So there is a pretty significant difference here, and that is the reason why some folks prefer to use IRR for their investment calculations. Okay. Let's now look at x eir. See the problem. Another problem with the EIR is that it assumes all of the payments come at the end of the year. So if I put some dates here for when the investments go out and when the payments come in, let's see what happens if we factor the dates in rather than just assuming everything's happening at the end of the year. So there's a function called x IRR. That will take the values. But it will also take the dates. Now we're going to anchor the dates. Close that. See, now the values are higher than the original IRR. And if we copy. And Control option v pastes the formulas. We see that the Zaire is very different. And because of the dates here, because this is happening towards the end of the year, this is happening very early. The czar is extremely high for this last case here. The gap between these two dates is much smaller. These assume a year almost of difference. These do assume a year versus this one is factoring the dates here. So there's quite a bit of difference between these. The disadvantages of the IRR is partially addressed with the Modify IRR. However, the modified IRR also falls prey to not being able to factor in the dates. So in terms of preference, many folks will use either X IRR or modify IRR depending on the project to calculate the IRR. But. If we look at the example for investment number nine and ten. It will show that you cannot use any of these formulas for either by itself. Here's why. Let's look at these two investments. They are both five 2000 investments. But one of them, you get a $25,000 windfall in the last year and the other one, you get a very good return through the five years. If we just look at the IRR, they're identical investments. If we look at the Modify IRR, then the investment at nine is better as a higher modifier than the investment ten. However, if we look at the ex IRR that factors in the dates we see that the ex IRR says the investment ten is better than investment nine. So what should we do here? If we use IRR, it says they're the same. If we use IRR, it says investment nine is better. If we use ex IRR, it says investment ten is better. So it's very confusing to look at it in this way. Right. So in order to evaluate this better, we need different measures and we're going to look at that in the next lecture.