NPV vs. IRR and the Crossover Rate
Compare Projects S and L at a 7.5% WACC, then find the 10.16% crossover rate and follow a worked Excel example.
Chapter 9 — Capital Budgeting (MBA)
NPV • IRR • MIRR • PI • Payback • Discounted Payback — with syntax cards, mini calculators, worked examples, and homework preloads.
Intro
Capital budgeting evaluates long-term projects by turning future cash flows into today’s value or by finding required returns. For this course, focus on:
- NPV (gold standard for value creation)
- IRR & MIRR (rates of return; MIRR fixes IRR’s reinvestment assumption)
- PI (useful when capital is rationed)
- Payback / Discounted Payback (speed of recovery; screening tools)
Tip: For Excel, cash flows usually go in rows/columns. Make sure signs are right (CF0 < 0, later CFs generally > 0), and that periods are equally spaced.
Slides (PPT)
If the embed is blocked by your LMS or browser, use the open-in-new-tab link.
Featured Video — What is Capital Budgeting? (Importance, Methods & Limitations)
6 Capital Budgeting Methods Explained | NPV, IRR, MIRR, Payback & PI
Short primer covering definitions, why it matters, common methods, and limitations.
Excel Syntax (Quick Cards)
Capital Budgeting in Excel | NPV, IRR, MIRR, Payback & Profitability Index
NPV
=NPV(rate, value1, value2, ...)
- Values occur at period ends
- Add CF0 separately: NPV(...) + CF0
IRR
=IRR(values, [guess])
- Array must include CF0 and all cash flows
- Guess optional; helps convergence & chooses root when multiple exist
MIRR
=MIRR(values, finance_rate, reinvest_rate)
- Needs at least one negative and one positive CF
- Uses explicit reinvest & finance rates
Profitability Index (PI)
PI = 1 + NPV / |CF0|
Payback
Time to recover initial outlay (undiscounted). Discounted payback uses present values.
Calculators
Capital Budgeting Calculators
Use these calculators first for homework, examples, and exam practice.
Use the mini calculator below only for quick checks.
Mini Calculator on This Page
Quick check tool: enter CF0 (negative) and later cash flows (comma-separated). Example: -800, 350, 350, 350
Inputs
Outputs
NPV: —
IRR (by guess): —
MIRR (fin=reinv=rate): —
PI: —
Payback (yrs): —
Disc. Payback (yrs): —
IRR roots (if multiple): —
Manual formulas used
- NPV = Σ CF_t / (1+r)^t
- IRR: if multiple sign changes, there can be multiple positive roots; we pick the root nearest your Guess
- MIRR: grow positives at reinvest rate, discount negatives at finance rate, then annualize
- PI: 1 + NPV / |CF0|
- Payback = years until cumulative CF ≥ 0 + fraction of final year
- Discounted payback uses PVs
Multi-Project Choice — IRR vs NPV
WACC = 7.5%. Mutually exclusive projects S and L:
| Year | 0 | 1 | 2 | 3 | 4 |
|---|---|---|---|---|---|
| S | -1,100 | 550 | 600 | 100 | 100 |
| L | -2,700 | 650 | 725 | 800 | 1,400 |
Reminder: “True value” is measured by NPV. IRR can rank differently when cash flow scale/timing differs.
NPV Profile — Project S vs L (Interactive)
Cash Flows (editable)
Project S
| Year | CF |
|---|
Project L
| Year | CF |
|---|
Controls
Tip: Edit any CF cell and the chart refreshes automatically.
S — NPV@WACC: | IRR:
L — NPV@WACC: | IRR:
Crossover r (L–S):
Crossover Rate — What it is and how to get it
Definition (plain English): The crossover rate is the discount rate where two projects’ NPVs are exactly equal—i.e., the point where their NPV profiles cross. Below that rate one project has the higher NPV; above it, the other wins.
How to compute (L − S, then IRR)
- Align years for both projects (pad missing years with zeros).
- Compute incremental flows = L − S for each year (including CF0).
- Compute IRR of that incremental series. That IRR is the crossover rate.
Excel: =IRR(Incremental_Flows_Range)
Current crossover r (L–S): —
Incremental CFs (L − S)
| Year | Incremental CF |
|---|
At the crossover rate, NPV(L) = NPV(S). Ranking flips on either side of this rate.
Excel vs Math
Setup: Put 11% in B1. Cash flows in B3:B6: −800, 350, 350, 350.
A) Easiest — Named Ranges
- Select B1 → type Rate in the Name Box → Enter
- Select B3 → name CF0
- Select B4:B6 → name CFs
- Select B3:B6 → name Flow
Formulas (paste anywhere)
=NPV(Rate, CFs)+CF0 =IRR(Flow) =MIRR(Flow, Rate, Rate) =1 + NPV(Rate, CFs)/ABS(CF0)
B) Plain Cells (no $)
Tip: Don’t copy these around—just paste once where you want the answers.
Formulas (paste once)
=NPV(B1,B4:B6)+B3 =IRR(B3:B6) =MIRR(B3:B6,B1,B1) =1 + NPV(B1,B4:B6)/ABS(B3)
Math (same numbers)
NPV = −800 + 350/1.11 + 350/1.11^2 + 350/1.11^3 ≈ 55.30
IRR ≈ 14.93% (solve NPV(IRR)=0)
MIRR (r=11%) ≈ 13.50%
PI = 1 + 55.30/800 ≈ 1.069
Payback ≈ 2.286 yrs; Discounted Payback ≈ 2.784 yrs
Teacher mode (optional): show $-anchored version
$-Anchored (for copy/fill safety)
=NPV($B$1,$B$4:$B$6)+$B$3 =IRR($B$3:$B$6) =MIRR($B$3:$B$6,$B$1,$B$1) =1 + (NPV($B$1,$B$4:$B$6)+$B$3)/ABS($B$3)
Non-Conventional Cash Flows (Multiple IRRs)
One Project, Two IRRs | Non-Conventional Cash Flows, NPV & MIRR in Excel
Example: CF0 = −90,000; CF1 = 132,000; CF2 = 100,000; CF3 = −150,000. Hurdle (required return) = 15%.
What’s going on (plain English)
- IRR solves: \( \mathrm{NPV}(r)=\sum_{t=0}^{n}\frac{CF_t}{(1+r)^t}=0 \).
- With sign pattern − + + −, the NPV polynomial can have multiple positive roots ⇒ multiple IRRs (Descartes’ Rule of Signs).
- On the NPV profile (NPV vs r), you’ll see the curve cross zero more than once.
Short math derivation
\[
\mathrm{NPV}(r)= -90000 + \frac{132000}{1+r} + \frac{100000}{(1+r)^2} - \frac{150000}{(1+r)^3}=0
\]
Multiply by \((1+r)^3\):
\[
90000\,r^3 + 138000\,r^2 - 94000\,r + 8000 = 0
\]
Two positive roots here (≈ 10.11% and 42.56%) → two IRRs.
What to do in practice
✅ If NPV at hurdle > 0 → Accept.
⚠️ If IRR conflicts with NPV or you get multiple/no IRRs → trust NPV at the hurdle.
😵 No real/positive IRR? Use MIRR and NPV.
😵 No real/positive IRR? Use MIRR and NPV.
IRR roots: — and —
MIRR (15%,15%): — • NPV@15%: —
MIRR (15%,15%): — • NPV@15%: —
Excel IRR “guess” (which root shows up?)
📏 Rule: 🎯 Excel returns the IRR root nearest your guess.
Use a big guess to land on the high root (e.g., 40% → ~42.56%). Use a small guess (e.g., 10%) to land on the low root (~10.11%).
—
👉 Default Excel guess is 10% (
😵 If you see #NUM!, try a different guess, check for at least one sign change, or switch to MIRR and NPV.
=IRR(B3:B6)) → tends to return the low root.😵 If you see #NUM!, try a different guess, check for at least one sign change, or switch to MIRR and NPV.
NPV Profile — see the two zero-crossings
🔵 NPV(r) curve
➖ NPV = 0 axis
🔴 Zero-crossings (IRR candidates)
🟢 Hurdle line (your r)
r=15.0% • NPV(r): —
Method Summary
| Approach | Description | Pros | Cons | Suitable Cases | Industry Popularity |
|---|---|---|---|---|---|
| Net Present Value (NPV) | Calculates the present value of cash flows, subtracting initial investment | Accounts for time value of money, provides direct measure of profitability | Requires accurate cash flow estimation, sensitive to discount rate assumptions | Long-term projects, capital budgeting | Popular in most industries, especially capital-intensive sectors like manufacturing, energy, and real estate |
| Internal Rate of Return (IRR) | Finds the discount rate that makes NPV zero | Easy to understand, considers time value of money | Can be misleading with non-conventional cash flows or mutually exclusive projects, assumes reinvestment at IRR | Projects with conventional cash flows, early cash inflows | Used across industries, especially finance, healthcare, and tech |
| Modified Internal Rate of Return (MIRR) | Similar to IRR, but assumes reinvestment at a specified rate | Corrects IRR’s reinvestment assumption, suitable for comparing different projects | More complex calculation than IRR, still less informative than NPV for absolute profitability | Projects where reinvestment rate is known | Less common, used in finance and advanced corporate finance projects |
| Payback Period | Time required to recoup initial investment | Simple, emphasizes liquidity, useful for quick screening | Ignores time value of money, cash flows beyond payback period, and profitability | Short-term projects, high liquidity needs | Common in small businesses and start-ups, used in risk-averse sectors like retail |
| Discounted Payback Period | Time to recoup investment considering time value of money | Considers time value of money, provides a more accurate payback measure than traditional Payback Period | Ignores cash flows after payback, profitability, and project lifetime | High-liquidity projects with cash flow risks | Used in industries with uncertain cash flows like tech and energy |
| Profitability Index (PI) | Ratio of PV of future cash flows to initial investment | Useful for ranking projects, particularly with capital constraints | Similar drawbacks as NPV for cash flow estimation, not suitable for mutually exclusive projects | Capital rationing scenarios, comparing projects with limited capital | Used in finance, utilities, and large capital-intensive industries |
Quick Quiz
Click to check answers.
Homework (Due with Final)
Question 1
Project with an initial cash outlay of $20,000 with following free cash flows for 5 years. Year Cash flows 1 $8,000 2 4,000 3 3,000 4 5,000 5 10,000 1) How much is the payback period (approach one)? ---- 4 years 2) If the firm has a 10% required rate of return. How much is NPV (approach 2)? -- $2456.74 3) If the firm has a 10% required rate of return. How much is IRR (approach 3)? ---- 14.55% 4) If the firm has a 10% required rate of return. How much is PI (approach 4)? ---- 1.12
Question 2
Project with an initial cash outlay of $60,000 with following free cash flows for 5 years.
Year FCF
Initial outlay –60,000
1 25,000
2 24,000
3 13,000
4 12,000
5 11,000
The firm has a 15% required rate of return.
Calculate payback period, NPV, IRR and PI. Analyze your results.
(2.85, $764.27, 15.64%, 1.013, accept the project)
Question 3: Mutually Exclusive Projects
1) Consider the following cash flows for one-year Project A and B, with required rates of return of 10%. You have limited capital and can invest in one but one project. Which one? (A’s NPV = 72.73, B’s NPV=227.27, so choose B) § Initial Outlay: A = -$200; B = -$1,500 § Inflow: A = $300; B = $1,900 2) Example: Consider two projects, A and B, with initial outlay of $1,000, cost of capital of 10%, and following cash flows in years 1, 2, and 3: A: $100 $200 $2,000 B: $650 $650 $650 Which project should you choose if they are mutually exclusive? Independent? Crossover rate? (mutually exclusive: A’s NPV=758.83 > B’s NPV = 616.45, so choose A; Independent, choose all positive NPV, so choose both; Crossover rate = 21.01%. The calculator does not work. Use IRR in Excel)
Homework Help Videos (Chapter 9)
Capital Budgeting Homework | Payback, NPV, IRR, PI & Crossover Rate in Excel
Q1 Walkthrough
Q2–Q3 Walkthrough
Excel Template Help
Resources
Blog — IRR vs MIRR (Why MIRR is often better)
IRR vs. MIRR in Excel | How to Calculate Both and When to Use Each
The issue in plain English
- IRR assumes interim cash flows are reinvested at the IRR itself (often unrealistic), can produce multiple answers (non-conventional cash flows), or no real answer.
- MIRR fixes this by using explicit rates: a finance (borrowing) rate for negatives and a reinvestment rate for positives. It produces a single, comparable return.
Definitions (math)
IRR: the rate \( r \) that makes NPV zero.
\( \mathrm{NPV}(r)=\displaystyle\sum_{t=0}^{n}\frac{CF_t}{(1+r)^t}=0 \)
MIRR (with finance rate \( r_f \) and reinvest rate \( r_r \)):
\[ \mathrm{MIRR}=\left(\frac{\mathrm{FV}_{\text{positives at } r_r}}{-\,\mathrm{PV}_{\text{negatives at } r_f}}\right)^{\!1/n}-1 \]
- \(\mathrm{PV}_{\text{negatives}}=\displaystyle\sum_{t:CF_t<0}\frac{CF_t}{(1+r_f)^t}\)
- \(\mathrm{FV}_{\text{positives}}=\displaystyle\sum_{t:CF_t>0} CF_t\,(1+r_r)^{\,n-t}\)
Excel formulas (no $ anchoring)
=IRR(B3:B6)
=MIRR(B3:B6, finance_rate, reinvest_rate)
Tip: For MIRR, a good default is to use your hurdle/WACC for both the finance and reinvest rates (e.g., 10%). Ensure your range has at least one negative and one positive CF.
Worked example (numbers & steps)
Cash flows: CF0 = −1000; CF1 = 400; CF2 = 400; CF3 = 400. Use finance = reinvest = 10%.
IRR
Solve \( \mathrm{NPV}(r)=0 \) for r.
Result: IRR ≈ 9.70%
Solve \( \mathrm{NPV}(r)=0 \) for r.
Result: IRR ≈ 9.70%
MIRR
\(\mathrm{PV}_{\text{neg}} = 1000\) (only CF0 at t=0)
\(\mathrm{FV}_{\text{pos}} = 400(1.1)^2 + 400(1.1)^1 + 400 = 400(1.21+1.10+1)=400\cdot3.31=1324\)
\(\mathrm{MIRR}=\left(\dfrac{1324}{1000}\right)^{1/3}-1 \approx \mathbf{9.81\%}\)
\(\mathrm{PV}_{\text{neg}} = 1000\) (only CF0 at t=0)
\(\mathrm{FV}_{\text{pos}} = 400(1.1)^2 + 400(1.1)^1 + 400 = 400(1.21+1.10+1)=400\cdot3.31=1324\)
\(\mathrm{MIRR}=\left(\dfrac{1324}{1000}\right)^{1/3}-1 \approx \mathbf{9.81\%}\)
Why MIRR can be higher (or lower) than IRR
- If the IRR is below your chosen reinvestment rate, MIRR typically comes out a bit higher (you assumed better reinvestment than IRR’s assumption).
- If the IRR is above your reinvestment rate, MIRR will come out lower (more conservative and realistic).
When to use what
- NPV remains the gold standard for accept/reject and ranking (value creation at the hurdle/WACC).
- MIRR is the most sensible single “rate of return” to report: it uses realistic reinvestment and returns a unique number.
- IRR is fine for quick intuition with conventional cash flows—but watch for multiple IRRs or conflicts with NPV.
Bottom line: Use NPV to decide, and use MIRR (with your hurdle as both rates) when you want a single comparable return figure.
Note: Calculators here are for instruction; for official work, verify in Excel/approved tools.