If you need to calculate the internal rate of return for a series of mixed cash flows, you can use the MIRR function.
- [Instructor] Investing money in projects can be quite complicated. In some cases, it's not as easy as putting money into a project and then taking the profits out. In some cases, you might want to take the money that comes out of a project and reinvest it elsewhere. In this movie, I will show you how to evaluate a series of cash flows, assuming that you borrowed money for the original investment and can reinvest the proceeds from the project in another investment. My sample file is the Mixed IRR workbook, and you can find it in the Chapter 2 folder of the Exercise Files collection.
To use the Mixed IRR function, which is MIRR, you need to know several bits of information. The first is a series of cash flows that occur at regular intervals. So in cells B4 through B11, you'll see that we have an initial cash flow, which is $150,000. That's a negative number, because it's money flowing out of our account. And then a series of incomes, which are positive numbers. To use MIRR, you have to have a negative number in the first position and positive numbers in the remainder.
Then you also need to know the finance rate and the reinvestment rate. The finance rate is the percentage that you pay in interest on the amount that you borrow. In this case, we're assuming it's a year, so your finance rate per year is 7%. The reinvestment rate, which is in cell E4, is 9%, and that is the amount of money that you make by reinvesting the proceeds that you receive from project number one into a separate project.
So what we're trying to do is to mix money that you put into one project with the profits from another. So we're mixing two cash flows. To perform this calculation, I'll go to cell B13, type in the equals sign, and the functional use is MIRR. Then type a left parentheses. And we need to know the values, the finance rate, and the reinvestment rate. The values are in cells B4 through B11. Then a comma. The finance rate is in cell E3, and a comma.
And the reinvestment rate, that is, what we received by reinvesting the proceeds from project number one, is in cell E4. Everything is good, so I'll type a right parentheses and enter. And I see that this project, overall, has an internal rate of return of 8.51%. We interpret this result as meaning that this project would have a net present value of zero, that is, be exactly break even. If we could find a risk-free investment that returned 8.51%, and given that government bonds, which are usually considered to be risk free, pay back at most about 3.5%, when we consider that most government bonds that are considered risk free pay 3 to 3.5%, an 8.51% return looks very good.
- Calculating the effect of interest rates and inflation
- Finding the arithmetic and geometric means of growth rates
- Calculating the future and present value of an investment
- Calculating loan payments for a fully amortized loan
- Calculating the effect of paying extra principal with each payment
- Finding the number of periods required to meet an investment goal
- Calculating net present value and internal rate of return
- Building a cash tracking worksheet
- Visualizing cash flows using a waterfall chart