IRR vs XIRR vs MIRR

Symon He
A free video tutorial from Symon He
Author | Investor | MBA
4.5 instructor rating • 12 courses • 236,718 students

Lecture description

A discussion and comparison of the IRR, XIRR, and MIRR functions in Excel.  What are their differences and which should you use?

Learn more from the full course

Fundamentals of Analyzing Real Estate Investments

Learn professional investment analysis techniques for real estate investing in residential and commercial properties

18:34:53 of on-demand video • Updated December 2020

  • Confidently Evaluate the Return Potential of Any Real Estate Investment Opportunity
  • Know a "Good" Investment from a "Bad" Investment
  • Use Professional Grade Investment Models to Evaluate Your Deals
  • Evaluate Rental Income Properties
  • Evaluate Fix & Flips
  • Evaluate Commercial Properties
  • Evaluate Airbnb Properties
  • Use Smart Investment Deal Structures With Business Partners
  • Understand and Use Professional Real Estate Investment Strategies & Techniques
English [Auto] Hey, this is an additional lecture on discussing the difference between several of the IRR functions, so it's actually several different IRR functions and Excel and there are some nuance differences. OK, I use the IRR instead of the normal IRR, but there's also another one called the modified IRR, which is more accurate in some ways, and we'll begin to discuss it in this lecture. But I'll also discuss why it still might be a good idea to use the Zaer 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 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. OK, so let's see an example here. Let's look at the first eight investments here, all 10 investment. Actually, they all require an investment of, say, two thousand dollars. All 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 four thousand. 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 are. Is simply I are. And we select the values from the time of investment to the last year of the cash flows. And then we hit, OK? Now we can copy and paste this. Formula Patrol Option B. Formula. OK, and calculate that down. So we see that even though the cash flow over the five years, the total cash flow is identical and they 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 piece that there and notice that the eye are identical for these investments. OK, so one of the big. 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 invested 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 IRR, we use em IRR. Again, we take the period from the investment to the last year, the cash flow. 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 I can think of it as an interest rate. If you have to borrow money, what would that be? Let's say you borrowed it at four percent. 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 two percent, 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, you know, aggressively five percent return. OK, we're going to assume that, for example. Now we see that the modified mice are compared to there is a little bit lower, right? So if we select these and copy the formula, just the formula. We see that the images are 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 percent versus 17 percent better. It's 19 versus 15 percent better. So there is a pretty significant difference here, and that is the reason why some folks prefer to use M i r for their investment calculations. OK. Let's now look at EXI or see the problem, another problem with the IRA 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 paste formulas. We see that the zahraa 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 extra hour is extremely high for this last case here. OK, 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 modifier. However, the modifier are also falls prey to not being able to factor in the dates. So in terms of preference, many folks will use either Zaara or Modify are depending on the project to calculate the IRR, but. If we look at the example for investment number nine and 10. It will show that you cannot use any of these formulas for Iraq by itself. Here's why. Let's look at these two investments. They're both five, 2000 dollar investments, but one of them, you get a 25000 dollar 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 IRA, are there identical investments? If we look at the modified IRR, then the investment nine is better, has a higher modifier than the investment 10. However, we look at the X IRR that factors in the dates, we see that the X IRR says the investment 10 is better than investment nine. So what should we do here if we use IRR? It says they're the same if we use them IRR. It says investment nine is better if we use X. IRR says investment 10 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.