Using the IRR function in Excel to calculate the project’s internal rate of return. The internal rate of return (IRR) is a rate of return used in capital budgeting to measure and compare investment returns. It is also called discounted cash flow rate of return (DCFROR) or rate of return (ROR) In the context of savings and loans IRR is also known as effective interest rate.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
How to use the IRR function in Excel
For example, project A requires an initial investment of $100 (cell B5).
1. We expect $0 profit at the end of the first period, $0 profit at the end of the second period and $152.09 profit at the end of the third period.
Note: the discount rate is 10%. This is the best rate of return on the investment. For example, you can also deposit money into a savings account with 10% interest.
2. Use the NPV function to calculate the present value of a series of future cash flows and subtract the initial investment.
Explanation: a positive net present value indicates that the project’s rate of return exceeds the discount rate. In other words, you are better off investing your money in project A than depositing it in a savings account with 10% interest.
3. The IRR function below calculates the internal rate of return for project A.
4. Internal rate of return is the discount rate that makes net present value zero. To see this clearly, change the 10% discount rate in cell B2 to 15%.
Explanation: a net present value of zero indicates that the project generates a rate of return equal to the discount rate. In other words, both options, investing your money in project A or depositing your money in a savings account with 15% interest, yield the same return.
5. Suppose you deposit $100 in the bank. Your investment after 3 years with an annual interest rate of 15% will be $52.09.
Conclusion: you can compare the performance of a project with a savings account using IRR.
Present value
For example, project B requires an initial investment of $100 (cell B5). We expect a profit of $25 at the end of the first period, a profit of $50 at the end of the second period, and a profit of $152.09 at the end of the third period.
1. The IRR function below calculates the internal rate of return for project B.
2. Again, the internal rate of return is the discount rate that makes the net present value zero. To see this clearly, change the 15% discount rate in cell B2 to 39%.
Explanation: a net present value of zero indicates that the project generates a rate of return equal to the discount rate. In other words, both options, investing your money in project B or depositing your money in a savings account with an interest rate of 39%, yield the same return.
3. I will check this out. First, we calculate the present value (pv) of each cash flow. Next, we sum these values.
Explanation: instead of investing $100 in project B, you can also deposit $17.95 in a savings account for 1 year, $25.77 in a savings account for 2 years, and 56, $28 into a three-year savings account, with an annual interest rate equal to IRR (39%).
Rules of IRR
The IRR rule states that if the IRR is greater than the required rate of return, you should invest in the project. The IRR value is often used to compare investments.
1. The IRR function below calculates the internal rate of return of project X.
Conclusion: if required rate of return is 15%, you should invest in this project because the IRR of this project is 29%.
2. The IRR function below calculates the internal rate of return of project Y.
Conclusion: in general, a higher IRR indicates a better investment. Therefore, project Y is a better investment than project X.
3. The IRR function below calculates the internal rate of return of project Z.
Conclusion: Higher IRR is not always better. Project Z has a higher IRR than project Y but a much lower cash flow.
In addition, you can also view many other excel articles here.
