Financial Modeling Tutorials
- Financial Modeling Basics
- What is Financial Modeling?
- Financial Modeling in Excel
- Types of Financial Models
- Financial Modeling Interview Questions
- Alibaba IPO Financial and Valuation Model
- Box IPO Modeling Details
- Box IPO Valuation Model
- Download Alibaba IPO Financial Model
- Financial Modeling Books
- Financial Modeling Templates
- Financial Modeling Course
- Excel Modeling
- Financial Functions in Excel
- Sensitivity Analysis in Excel
- Time Value of Money
- Future Value Formula
- Present Value Factor
- Perpetuity Formula
- Annuity vs Perpetuity
- Annuity vs Lump Sum
- Internal Rate of Return (IRR)
- NPV vs XNPV
- NPV vs IRR
- NPV Formula
- PV vs NPV
- IRR vs ROI
- Break Even Point
- Payback Period & Discounted Payback Period
- Payback period Formula
- Discounted Payback Period Formula
- Profitability Index
- Cash Burn Rate
- Simple Interest
- Simple Interest vs Compound Interest
- Simple Interest Formula
- Effective Interest Rate
- Loan Amortization Schedule
- Rule of 72
- Geometric Mean Return
- Real Rate of Return Formula
- Continuous compounding Formula
- Weighted average Formula
- Holding Period Return Formula
- Cost Benefit Analysis
- Cost Volume Profit Analysis
- Opportunity Cost Formula
- Mortgage APR vs Interest Rate
Internal Rate of Return (IRR)
The internal rate of return is a metric employed in capital budgeting which is used to measure the extent of profitability of potential investments. It is also known as ERR or economic rate of return.
IRR is defined as the discount rate that sets the NPV of a project to zero is the project’s IRR.
Here is the IRR Formula
- For calculating the Internal Rate of Return with the help of this IRR formula, the NPV value is set to zero and then the discount rate is found out.
- This discount rate is then the Internal Rate of Return value that we needed to calculate.
- Due to the character of the formula, however, IRR can’t be calculated analytically, and should instead be calculated either through trial-and-error or by the use of some software system programmed to calculate the IRR.
Also, have a look at the differences between NPV and IRR
Internal Rate of Return – IRR Example
Let us assume that Nick invests $1,000 in a project A and gets a return of $1400 in 1 years time. Calculate Internal Rate of Return of project A?
Below is the Net present value calculation table of the project with different discount rates (cost of capital).
We note that for Cost of Capital @ 10%, the NPV is $298.
From the graph above, we note that Net Present value is zero at the discount rate of 40%. This discount rate of 40% is the Internal rate of return of the project.
Internal Rate of Return in Excel
IRR in Excel Step 1 – Cash inflows and outflows in a standard format
Below is the cash flow profile of the project. You should put the cash flow profile in the standardized format as given below
Internal Rate of Return in Excel Step 2 – Apply the IRR formula in excel
IRR in Excel Step 3 – Compare IRR with the Discount Rate
- From the above calculation, you can see that the NPV generated by the plant is positive and IRR is 14% which is more than the required rate of return
- This implies when the discounting rate will be 14% NPV will become zero.
- Hence, XYZ company can invest in this plant.
What is the significance of IRR?
Internal Rate of Return is much more useful when it is used to carry out a comparative analysis rather than in isolation as one single value. The higher a project’s Internal Rate of Return value, the more desirable it is to undertake that project as the best available investment option. IRR is uniform for investments of varied sorts and, as such, IRR values are often used to rank multiple prospective investment options that a firm is considering on a comparatively even basis. Assuming the amount of investment is equal among the different available options of investment, the project with the highest IRR value is considered as the best and that particular option is (theoretically) taken up first by an investor.
The IRR of any project is calculated by keeping the following three assumptions in mind:
- The investments made will be held until their maturity dates.
- The intermediate cash flows will be reinvested in IRR itself.
- All the cash flows are periodic in nature, or the time gaps between different cash flows are equal.
The IRR value provides the organization with a rate of growth which can be expected to be obtained by making an investment in the project considered. While the actual Internal Rate of Return obtained may vary from the theoretical value that we have calculated, the highest value will surely provide the best growth rate among all. The most common use of Internal Rate of Return is seen when an organization uses it to consider investing in a new project or to increase the investment in a currently ongoing project. As an example, we can take the case of an energy company which opts to start a new plant or to expand the working of a current working plant. The decision, in this case, can be taken by calculating IRR and thus finding out which of the options will provide a higher net profit.
Required rate of return (Hurdle Rate)
Hurdle rate or required rate of return is a minimum return expected by an organization on the investment they are making. Most organizations keep a hurdle rate and any project with an Internal Rate of Return exceeding the hurdle rate is considered profitable. Although this is not the only basis of considering a project for investment, Hurdle rate is an effective mechanism in screening out projects which will not be profitable or profitable enough. Usually, a project with the highest difference between Hurdle rate and IRR is considered to be the best project to invest in.
- Independent Projects: Internal Rate of Return > Cost of Capital (hurdle rate), accept the project
- Independent Projects: Internal Rate of Return < Cost of Capital (hurdle rate), reject the project
There are few thumb rules to be followed while any IRR calculations are done. They are:
- The invested amount is always taken with a negative sign. So if you invest $100 it is taken as -$100.
- The money you gain is always taken as a positive value, so if you receive an amount of $60 it is taken as $60.
- By default, all the payments are taken as yearly, either at the start or at the end of the year.
It can even be compared against the prevailing rates of return within the securities market. If a firm cannot notice any investment options with Internal Rate of Return values more than the returns which will be generated within the monetary markets, it may merely opt to invest its retained earnings into the market. Even though Internal Rate of Return is considered as a standalone metric with great importance, it should always be used in conjunction with NPV for getting a clearer picture of a project’s potential in earning the organization a better profit.
Disadvantages of Internal Rate of Return
- The need for the use of NPV in conjunction is considered to be a big drawback of IRR. Although considered an important metric, it can’t be useful when used alone. The problem arises in situations where the initial investment gives a small IRR value but a greater NPV value. This happens on projects which give profits at a slower pace, but these projects may benefit in enhancing the overall value of the organization.
- A similar problem is when a project gives a faster-paced result for a short period of time. A small project may seem like giving a large profit in a short time, giving a greater IRR value, but a lower NPV value. The project length has a greater significance in this case.
- Another problem with Internal Rate of Return, which is not strictly inherent to the metric itself, but related to a typical misuse of IRR. Individuals might assume that, once positive cash flows are generated throughout the course of a project (not at the end), the money will be reinvested at the project’s rate of return. This may seldom be the case. Rather, once positive cash flows are reinvested, it’ll be at a rate that represents the value of the total capital employed. Misreading and misusing IRR in this way could result in the conclusion that a project is a lot more profitable than it truly is.
- Another common drawback is termed as multiple IRR. Multiple IRR drawbacks occurs in cases where the cash flows in the course of the project’s lifespan are negative (i.e. the project operates at a loss or the organization needs to contribute additional capital). This is referred to as a “non-normal cash flow” situation, and such cash flows can provide multiple Internal Rate of Return.
These drawbacks of multiple Internal Rate of Return occurrences and the inability to handle multiple duration projects have brought up the need for a better procedure to find out the best project to invest in. And so, a new modified metric known as the modified internal rate of return or in short MIRR is designed.
This is a guide to what is Internal Rate of Return along with examples. Here we also discuss the significance and drawbacks of IRR. You may also have a look at the following articles on Corporate Finance –