IRR & NPV Calculation in Financial Models Explained
Introduction
when you first came across the terms "Net Present Value" and "Internal Rate of Return," you must have thought that these are the complicated things which only investment bankers use to impress their friends at parties. But NPV and IRR are just two extremely helpful methods for a single question once you take away the technical terms:
"Is this thing I'm going to spend money on, really worth it?"
That "thing" could be a new coffee shop, a solar farm, a software startup, or simply buying an apartment to rent out. It doesn't matter. NPV and IRR are the two smartest moves that we can use to prevent ourselves from making costly mistakes. So get yourself a coffee (or tea if you'd rather) and let me walk you through it like we were face to face.
First, the Big Idea: Money Today ≠ Money Tomorrow
What if I gave you a choice between two things?
Option A: $100 given to you by me immediately.
Option B: $100 to be given to you by me one year from today.
Which one would you go for?
The $100 right away is the one to take for sure. Why? Because the $100 today you could put in a bank account, invest in stock or just use it for something fun and thus it would grow (or at least not be idle). That simple idea, “money now is worth more than money later,” is basically the whole thing NPV and IRR are built on.
NPV: “How much richer will I be because of this decision?”
NPV is the matured way of putting the question: “If I consider that money today is more valuable, will this project give me money or make me lose money?”
Real-Life Example (That Actually Happened to Me)
A friend of mine was thinking about a gym business. These were the figures:
Installation money: $150,000 right now
Predicted incremental net profit yearly: $40,000 for 5 years
His bank loan + what he could get by investing his money = about 10% interest
We thus did the calculation (you are spared the full calculator torture):
The gym would be worth quite a bit more than the cost if we were to discount all cash flows to current value, namely around $11,000.
→ NPV = +$11,000
He did it. After three years he sold it for a nice gain. NPV was the real deal. Had that very same computation been -$20,000, he would have been very fortunate not to have signed the lease.
IRR and NPV are two of the most important financial tools that help in the decision making process of businesses.
In fact NPV and IRR are usually used together to analyze investment projects and decide which is better or feasible. Both are indicators of profitability and take into account the value of money over time. NPV is expressed in monetary terms and IRR is a percentage rate.
Primary Tasks of Equity Research Analysts:
NPV is very powerful due to its feature of measuring in absolute values which makes it suitable for comparing projects of different scales. However, it is based on an assumption of a constant discount rate and doesn't specify what percentage of return the investors will get. This is where IRR becomes handy.
Internal Rate of Return (IRR) reflects the discount rate at which the Net Present Value (NPV) of cash flows turns to zero. It could be regarded as the breakeven rate of return for the investment. The idea is that if the IRR is higher than the required rate, then the project is acceptable.
In short, IRR can be found as the solution for:
$ 0 = \sum_{t=1}^{n} \frac{C_t}{(1 + IRR)^t} - C_0 $
Unlike NPV, IRR doesn't have a closed-form solution and requires iterative methods or software to solve. Simply put, it is the root of the NPV function.
-
Working out IRR
- - Write down the series of cash flows. First, the initial investment is an outflow and is followed by inflows.
- - In case of trial and error, you pick the discount rate and calculate the NPV of the cash flows until the result is very close to zero. In Excel, there's a function that allows to input the series of cash flows and a guess rate to get the result quickly: =IRR(range, [guess]).
- - Your last step is to compare IRR with the hurdle rate.
The main reason why IRR is very popular is because it is a percentage and this makes it very easy for everyone to understand. Although the point that the reinvestment of cash flows is being done at the IRR rate, which might not be the case, is often overlooked by the users of this metric.
Illustration of IRR
By using the same example of the machine:
Year 0 cash flow: -$100,000
Year 1 to 5 cash flow: $30,000
One way or another, IRR is approximately 15.09%: trial and error or Excel could be used.
If the required rate of return is 10%, then the project should be undertaken as 10% is less than 15.09%. At 15% IRR equals the discount rate, therefore NPV is close to zero.
What if the cash flows were not equal? Suppose there was a -$100,000 initial investment, then $20,000 (Year 1), $30,000 (Year 2), $40,000 (Year 3), $50,000 (Year 4), $60,000 (Year 5), and IRR would be approximately 18.92%.
Both NPV and IRR are the result of DCF calculations and may differ sometimes giving different decisions.
- Scale Sensitivity: With NPV, big projects with high absolute returns are preferred whereas IRR is scale-independent and thus can point to small ones with greater percentage returns.
- Reinvestment Assumption: According to NPV reinvestment should be done at the discount rate (most realistic), while IRR assumes the reinvestment at the IRR rate (usually optimistic).
- Multiple IRRs: There might be several different IRRs if you have non-conventional cash flows (e.g. sign changes like initial outflow, inflows, and then outflows) making it hard to interpret. NPV can be calculated in any case.
As mentioned before, these conflicts are mainly due to the limitations of both NPV and IRR. Besides these limits, another important aspect to keep in mind when using these tools is the assumption underlying NPV and IRR that future cash flows must be predictable and their estimation reliable. To summarize they both should be considered as complementary indicators rather as a conflict.
Firstly, it is advisable to use both metrics in conjunction with each other and additionally considering other indicators (payback period for instance) and qualitative arguments before making the final decision. Secondly, you can use a profitability index in case you want to compare different scale projects under the IRR measure or to give you a sense of return based on NPV valuation instead of a dollar figure.
Conclusion
Both Net Present Value and Internal Rate of Return are valuable in isolation but neither can be regarded as a perfect measure. NPV is a more reliable gauge for value creation as it measures in absolute terms. However, it does not directly provide the return percentage, which is a function of IRR.
Time inconsistency and estimation uncertainty give rise to choice dilemmas in the field of investment decisions, especially when capital rationing is in place. This paper explores the interaction of two intertemporal investment opportunities with multiple payoff realizations between two time-consistent agents. It shows that the agents are faced with a Class of Abandonment Problems and analyzes their behavior which is a composite of real options and strategic options. It brings Frictions to Real Options as well as to the Model of Strategic Option. The events of the paper can be seen as an illustration of multiple real options in the presence of strategic considerations as well as the importance of the first-mover advantage in the context of intertemporal investment decisions.
In such scenarios, investment appraisal based solely on either NPV or IRR might not be sufficient and could lead to suboptimal decisions. Managers should rather consider these metrics as valuable tools that provide insights into different facets of the investment case, thereby supporting a more comprehensive decision-making process.