Make Sure Your Business Decisions Are Profitable: Using NPV, IRR, and Breakeven Period with Excel

Jeff

Jun 16, 2024 • 7 min read

In the realm of business and finance, assessing the viability of projects is crucial. Whether you're a small business owner considering a new venture or a manager evaluating potential investments, understanding how to assess projects using key financial metrics is essential. In this guide, we will delve into three fundamental metrics: Net Present Value (NPV), Internal Rate of Return (IRR), and Breakeven Period. We will explore how to calculate each, the significance of the discount rate in NPV calculations, and the pros and cons of each method. Additionally, we'll provide step-by-step instructions on how to use these metrics in Excel, complete with concrete examples.

Part 1: Understanding the Metrics

1. Net Present Value (NPV)

Definition: NPV is the difference between the present value of cash inflows and the present value of cash outflows over a project's lifespan. It helps in assessing whether a project will generate more value than its cost.

Time Value of Money: The concept of the time value of money is central to NPV. Simply put, a dollar today is worth more than a dollar in the future due to its potential earning capacity. This principle is crucial for assessing the value of a project because it allows us to compare cash flows received at different times on a like-for-like basis.

Calculation: To put this into an equation, NPV is calculated using the formula:

%% NPV = ∑(\frac{C_{t}}{(1+r)^t)})−C_{0} %%

Where:

%%C_{t}%% = Cash inflow at time t
%%r%% = Discount rate
%%t%% = Time period
%%C_{0}%% = Initial investment

Discount Rate: The discount rate is the rate of return used to discount future cash flows back to their present value. It reflects the project's risk and the opportunity cost of capital. I'll cover how to come up with a discount rate later below. For the example below, let's assume it's 10%.

Example Calculation in Excel:

Let’s consider a project with the following cash flows:

Initial investment (Year 0): $10,000
Year 1: $3,000
Year 2: $4,000
Year 3: $5,000
Discount rate: 10%

In Excel:

Enter the cash flows in cells A1 to A4: -10000, 3000, 4000, 5000.
Note the importance of signage. The 10000 is negative because it's a cash outflow, while the other numbers are positive because they indicate cash inflow.
Enter the discount rate in cell B1: 10%.
Use the NPV function: =NPV(B1, A2:A4) + A1.
NPV is evaluated as -$210.37. A negative number means going ahead with this project will be value-destructive.

2. Internal Rate of Return (IRR)

Definition: IRR is the discount rate that makes the NPV of a project zero. It represents the project's expected rate of return.

Calculation: IRR is found by solving the NPV equation for %%r%%:

%% 0 = ∑(\frac{C_{t}}{(1+IRR)^t)})−C_{0} %%

In Excel, continuing on from the previous example:

Use the NPV function: =IRR(A1:A4).
It should be evaluated as 8.9%. This is what you expect to return by completing this project. Notice in your previous example the discount rate was 10%. As this project is returning 8.9%, lower than the 10% discount rate, this explains why the NPV calculation returned a negative number.

3. Breakeven Period

Definition: The breakeven period is the time it takes for a project to generate enough cash inflows to recover its initial investment.

Calculation: The breakeven period is calculated by summing the cash inflows until they equal the initial investment.

In Excel, continuing on from the previous example:

Use the SUM function: =SUM($A$1:A1) in cell D1. Then copy and paste this formula down to cell D4. Note you can use the keyboard shortcut Ctrl + D for Windows users. Note that there are "$" signs between the cell reference A and 1. This is to ensure the field is static.
The output is in the screenshot below. You can see that the number goes from negative to positive sometime between the 2nd and 3rd year of the project.

Part 2: Pros & Cons of each Metrics

1. Using NPV

Pros:

Considers the time value of money.
Provides a clear measure of the expected profitability.
Useful for comparing projects of different sizes.

Cons:

Requires an accurate discount rate.
Can be difficult to understand for those unfamiliar with finance.

Everyday Analogy: Think of NPV as evaluating whether a savings account will generate enough interest to justify the initial deposit, considering the interest rate and time period.

2. Using IRR

Pros:

Easy to understand as a percentage.
Useful for comparing the profitability of different projects.
Does not require a predetermined discount rate.

Cons:

Assumes reinvestment at the IRR rate, which may not be realistic. Going back to the example above, this means the $3,000 that you receive at the end of Year 1 of the project, needs to be reinvested at 8.9% for the rest of the project to get an IRR of 8.9%.
Can be misleading for projects with non-conventional cash flows.

Everyday Analogy: Consider IRR as the "interest rate" of a project. Just like you might compare interest rates on different savings accounts, you can compare IRRs to see which project offers a better return.

3. Using Breakeven Period

Pros:

Simple and easy to understand.
Provides a quick assessment of how long your capital is at risk.
Useful for liquidity analysis.

Cons:

Ignores the time value of money.
Does not measure overall profitability.
Can be misleading for long-term projects.

Everyday Analogy: Think of the breakeven period as paying off a loan. It’s the point where the total repayments equal the loan amount, but it doesn’t consider interest or the value of time.

Part 3: Allocating Capital Between Projects

So far, we've seen the NPV and IRR calculation are 2 sides of the same coin. However, an important distinction occurs when allocating capital between mutually exclusive projects, or in other words, when you need to decide between 2 projects. It’s crucial to consider which metric gives the most accurate picture of profitability and risk. NPV is generally more reliable than IRR in this context. Let's use an example below to illustrate why.

Example: Comparing Two Projects

Let's say we only have $100,000 to invest, and there are 2 projects we could undertake. Here are the forecasted returns of the 2 projects "A" and "B" where Project A costs $100,000 and Project B costs $70,000. As we have $100,000 to invest, we can choose to complete only 1, not both projects.

Project A:

Initial investment: $100,000
Year 1: $60,000
Year 2: $50,000
Year 3: $40,000
Discount rate: 10%

Project B:

Initial investment: $70,000
Year 1: $40,000
Year 2: $40,000
Year 3: $30,000
Discount rate: 10%

NPV Calculation:

Project A: =NPV(0.10, 60000, 50000, 40000) - 100000 = $25,920
Project B: =NPV(0.10, 40000, 40000, 30000) - 70000 = $21,961

IRR Calculation:

Project A: =IRR(-100000, 60000, 50000, 40000) = 25.3%
Project B: =IRR(-70000, 40000, 40000, 30000) = 28.0%

Decision: Although Project A has a higher NPV, Project B has a higher IRR. This means Project A will generate more value in absolute terms, even though its percentage return is lower. Therefore, Project A is the better choice when considering mutually exclusive projects. The important distinction here is that although Project B has a better IRR (ie 28.0%), this return is only on the $70k invested.

Part 4: How to Work Out a Discount Rate for NPV

1. Weighted Average Cost of Capital ("WACC") Approach: This is the approach they teach you at business school. The discount rate should reflect the cost of capital, which includes the cost of debt and the cost of equity. There are a few other considerations such as tax, but let's keep this simple for now. For example, if your business has a 5% cost of debt (what the bank charges you) and a 10% cost of equity (for example assessed via the Capital Asset Pricing Model "CAPM" - something which we will cover another day), and you use a 60/40 debt/equity ratio, your discount rate would be:

Discount Rate = (60% × 5%) + (40% × 10%) = 7%

2. Risk-Adjusted Rate: Consider the specific risks associated with the project. Higher-risk projects should have a higher discount rate to account for the increased uncertainty. Note that saving your capital at the bank and earning essentially the risk-free rate is the lower bound of your discount rate. As a project gets riskier than saving your capital in the bank, you should adjust your discount rate up.

Part 5: Summary

Key Takeaways:

NPV provides a clear measure of profitability, factoring in the time value of money. It’s best for projects where future cash flows are predictable, but it requires an accurate discount rate.
IRR is easy to understand and can be useful for comparing projects. However, it can be misleading if the cash flow pattern is irregular or reinvestment rates differ. Use NPV over IRR when comparing mutually exclusive projects.
Breakeven Period is simple and provides a quick risk assessment. It's ideal for short-term projects but doesn’t account for long-term profitability or the time value of money.

By understanding and applying these metrics, you can make informed decisions that enhance your business’s profitability and growth. Use Excel as your tool to simplify the calculations and compare different projects with ease. Remember, financial metrics are not just numbers; they are the language of business strategy.