CAPITAL BUDGETING · FORMULA AND EXCEL
1. Independent, mutually exclusive and dependent projects
| Relationship | Decision logic | Example |
|---|---|---|
| Independent | Accept each positive NPV if constraints permit | Different outlets with unrelated demand |
| Mutually exclusive | Choose the feasible alternative with greatest NPV on a comparable basis | Two machines for the same task |
| Dependent | Evaluate interactions or linked packages | Warehouse automation requires enabling software |
A portfolio NPV is additive only after interactions are represented. Do not count the same synergy in two projects.
CAPITAL BUDGETING · FORMULA AND EXCEL
2. NPV–IRR conflict from scale
For one year at 10%, A costs 100 and pays 140: IRR 40%, NPV 27.27. B costs 500 and pays 650: IRR 30%, NPV 90.91. If mutually exclusive and funding is available, choose B despite its lower IRR. Incremental B−A is −400, +510; its NPV is +63.64.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B2 | 0.1 | Required return |
| B5:C5 | -100, 140 | Project A |
| B6:C6 | -500, 650 | Project B |
| D5 | =NPV($B$2,C5)+B5 | A NPV = 27.27; copy down |
| E5 | =IRR(B5:C5) | A IRR = 40%; copy down |
| B8 | =B6-B5 | Incremental initial flow |
| C8 | =C6-C5 | Incremental receipt |
| D8 | =NPV(B2,C8)+B8 | Incremental NPV = 63.64 |
IRR is a percentage; NPV measures currency value. A high percentage on a small amount need not create the most value.
CAPITAL BUDGETING · FORMULA AND EXCEL
3. Timing conflicts and the crossover rate
A is −100, +130, 0. B is −100, 0, +160. A’s IRR is 30%; B’s is 26.49%. At 10%, NPV(A) = 18.18 and NPV(B) = 32.23. At 25%, NPV(A) = 4.00 and NPV(B) = 2.40. Rankings cross because the later receipt is more sensitive to discounting.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B2 | 0.1 | Trial discount rate |
| B5:D5 | -100, 130, 0 | A cash flows |
| B6:D6 | -100, 0, 160 | B cash flows |
| E5 | =NPV($B$2,C5:D5)+B5 | Copy to E6 |
| B8 | =160/130-1 | Crossover = 23.08% |
The crossover can also be found from the NPV of incremental flows, but incremental streams need not have a unique IRR. Reinvestment interpretations should not obscure the underlying scale and timing arithmetic.
CAPITAL BUDGETING · FORMULA AND EXCEL
3A. Verify the supplied two-project case before diagnosing a conflict
The following original cash flows are in US dollars, with year-end receipts and a common 10% required return.
| Year | Project A | Project B | B minus A |
|---|---|---|---|
| 0 | -10000 | -10000 | 0 |
| 1 | 7500 | 1000 | -6500 |
| 2 | 4000 | 5000 | 1000 |
| 3 | 1000 | 9000 | 8000 |
NPVB = −10,000 + 1,000/1.1 + 5,000/1.1² + 9,000/1.1³ = 1,803.16
| Metric | A | B | Ranking |
|---|---|---|---|
| NPV at 10% | $875.28 | $1,803.16 | B |
| IRR | 16.64% | 17.60% | B |
| MIRR at 10% | 13.12% | 16.25% | B |
These exact numbers have no ranking conflict. NPV(B) − NPV(A) is $927.87. The previously supplied 17.51%, 16.32% and 13.04% rates do not solve the stated equations.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B2 | 0.1 | Required return |
| B5:E5 | -10000,7500,4000,1000 | Project A |
| B6:E6 | -10000,1000,5000,9000 | Project B |
| F5 | =NPV($B$2,C5:E5)+B5 | A NPV; copy to F6 |
| G5 | =IRR(B5:E5) | A IRR; copy to G6 |
| H5 | =MIRR(B5:E5,$B$2,$B$2) | A MIRR; copy to H6 |
| B9 | =B6-B5 | Incremental cash flow; copy across E9 |
| F9 | =NPV($B$2,C9:E9)+B9 | Incremental NPV = 927.87 |
| G9 | =IRR(B9:E9) | Crossover = 18.90% |
Multiply by (1+r)³: 6,500(1+r)² − 1,000(1+r) − 8,000 = 0
r = 18.8987%
At this crossover, both project NPVs are approximately −$267.70. A crossing does not itself mean that either investment is worth undertaking. If doing neither is feasible, compare the best project NPV with zero.
CAPITAL BUDGETING · FORMULA AND EXCEL
3B. A genuine conflict variant and incremental IRR
Change just one stated assumption: reduce Project B’s year-3 receipt from $9,000 to $8,500. All other flows remain the same. This is a separate, explicitly modified teaching variant.
| Metric at 10% | A: unchanged | B: final receipt 8,500 | Preferred |
|---|---|---|---|
| NPV | $875.28 | $1,427.50 | B |
| IRR | 16.64% | 16.11% | A |
| Year-3 terminal value at 10% | $14,475 | $15,210 | B |
| MIRR at 10% | 13.12% | 15.00% | B |
Incremental NPV at 10% = 1,427.50 − 875.28 = 552.22
Crossover r = IRR([0,−6,500,1,000,7,500]) = 15.3846%
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B2 | 0.1 | Common hurdle rate |
| B5:E5 | -10000,7500,4000,1000 | Project A |
| B6:E6 | -10000,1000,5000,8500 | Modified Project B |
| F5 | =NPV($B$2,C5:E5)+B5 | NPV; copy down |
| G5 | =IRR(B5:E5) | IRR; copy down |
| H5 | =MIRR(B5:E5,$B$2,$B$2) | MIRR; copy down |
| B9 | =B6-B5 | Copy across through E9 |
| F9 | =NPV($B$2,C9:E9)+B9 | Incremental NPV = 552.22 |
| G9 | =IRR(B9:E9) | Crossover = 15.3846% |
Choosing B sacrifices $6,500 of early cash at year 1 in exchange for $1,000 extra in year 2 and $7,500 extra in year 3. This incremental stream has one economically relevant positive IRR. Since the hurdle of 10% is below 15.38%, B adds more NPV.
How to read the profile
For economically relevant non-negative discount rates below 15.38%, B has greater NPV; above it, A has greater NPV. At any rate also check whether the preferred project has positive NPV. The general incremental-IRR rule is unreliable for incremental streams with multiple roots or a different investment/financing pattern; incremental NPV remains the direct test.
Why do rankings differ?
Timing and scale determine the shapes of the NPV profiles. The formula for NPV does not require physically reinvesting receipts at WACC, and solving IRR does not require physically reinvesting them at IRR. Those rates enter a common terminal-wealth interpretation; MIRR makes an assumed reinvestment rate explicit. Early cash recovery alone does not guarantee a higher IRR for arbitrary project streams.
Decision rule with the right scope
For comparable, feasible, mutually exclusive alternatives, choose the higher NPV at risk-appropriate discount rates. Under funding constraints maximise the NPV of the feasible portfolio. When lives or services differ, first establish a comparable decision horizon. The simple scale illustration’s undiscounted gain is not the same as NPV at a positive hurdle rate.
CAPITAL BUDGETING · FORMULA AND EXCEL
4. Unequal lives: equivalent annual cost
EAA = NPV / annuity factor(k,n)
Two machines deliver the same required service. A costs 100, lasts 3 years and costs 10 a year to run. B costs 140, lasts 5 years and costs 8 a year. With zero salvage, no taxes and k = 10%, EAC(A) = 50.21 and EAC(B) = 44.93 per year. Choose B if repeatability and equal service assumptions hold.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B2 | 0.1 | Required return |
| B5:D5 | 100, 3, 10 | A purchase, years, annual cost |
| B6:D6 | 140, 5, 8 | B purchase, years, annual cost |
| E5 | =(1-(1+$B$2)^(-C5))/$B$2 | Annuity factor; copy down |
| F5 | =B5+D5*E5 | PV of costs; copy down |
| G5 | =F5/E5 | Positive EAC; copy down |
| H5 | =-PMT($B$2,C5,F5) | Excel cross-check; copy down |
For benefit projects, compare EAA on consistent assumptions. For costs, compare lower positive EAC. EAA does not justify comparing different services or assuming an unrepeatable project will recur.
CAPITAL BUDGETING · FORMULA AND EXCEL
5. Replacement chains and a common service horizon
For lives of 3 and 5 years, a 15-year chain can compare five A cycles with three B cycles. Repeat purchase costs at years 0,3,6,9,12 for A and 0,5,10 for B; include operating payments for years 1–15. Do not buy another machine at year 15 when the analysis ends then.
Reproduce it in Excel
Continue the EAC case above. Replacement prices and operating costs are assumed repeatable.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| J5 | =F5*(1+(1+$B$2)^(-3)+(1+$B$2)^(-6)+(1+$B$2)^(-9)+(1+$B$2)^(-12)) | 15-year PV cost of A |
| J6 | =F6*(1+(1+$B$2)^(-5)+(1+$B$2)^(-10)) | 15-year PV cost of B |
If the service is needed for only four years, explicitly value the alternatives over that four-year horizon, including disposal or continuation values. Blindly repeating to a common multiple can answer the wrong business question.
CAPITAL BUDGETING · FORMULA AND EXCEL
6. Capital rationing and why PI ranking can fail
Budget = 100. Projects are indivisible: A costs 60, NPV 42, PI 1.70; B costs 50, NPV 34, PI 1.68; C costs 50, NPV 34, PI 1.68. Greedy PI ranking selects A, leaves 40 unused and achieves NPV 42. Selecting B + C uses 100 and produces NPV 68.
| Constraint | Meaning |
|---|---|
| Hard rationing | External inability to obtain suitable finance |
| Soft rationing | Management-imposed internal cap |
| Divisible projects | A fraction can be undertaken with proportional cash flows |
| Indivisible projects | All-or-nothing investments requiring integer/binary choice |
PI can be useful for independent divisible projects with one initial capital constraint. Indivisibility, dependencies, different future-year budgets and interactions require explicit portfolio optimisation.
CAPITAL BUDGETING · FORMULA AND EXCEL
7. Excel Solver: formulate the actual allocation problem
Subject to 60xA + 50xB + 50xC ≤ 100; x ∈ {0,1}
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B2:D2 | 60, 50, 50 | Capital costs A, B, C |
| B3:D3 | 42, 34, 34 | NPVs A, B, C |
| B4:D4 | 0, 0, 0 | Decision cells; Solver changes these |
| B6 | =SUMPRODUCT(B2:D2,B4:D4) | Total spend |
| B7 | =SUMPRODUCT(B3:D3,B4:D4) | Total NPV |
| B8 | 100 | Available budget |
In Excel Solver, maximise B7 by changing B4:D4. Constrain B6 ≤ B8 and B4:D4 to binary; use the linear model with integer restrictions. The solution is 0,1,1. For divisible investments, use 0 ≤ x ≤ 1 with continuous variables. Add one constraint per period or resource. A dependency such as “C requires B” becomes xC ≤ xB; mutually exclusive projects require xA + xB ≤ 1.
Sources and further reading
Selected relevant sections of the supplied materials informed this series. Prasanna Chandra’s planning framework also informs the emphasis on estimation and strategic fit. These are original explanations and teaching cases, not reproduced textbook examples.
- Prasanna Chandra (2019), Projects: Planning, Analysis, Selection, Financing, Implementation, and Review, 9th edition, McGraw Hill. The uploaded 150-page scan was visually checked in selected opening sections: importance and difficulties of capital investment, project categories, planning–analysis–selection–financing–implementation–review, strategy and demand forecasting. It ends during Chapter 4; later detailed appraisal chapters appear in its contents but are not present in this extract.
- Dayananda, Irons, Harrison, Herbohn and Rowland (2002), Capital Budgeting: Financial Appraisal of Investment Projects, Cambridge University Press. Reviewed selected passages on relevant cash flows, appraisal methods, sensitivity, simulation and capital constraints. The chapter structure also guides further reading.
- Dr. Vijay Shankar Pandey, Capital Budgeting Techniques, uploaded as “Basics of Capital Budgetings”: process, payback, ARR, NPV, IRR and comparison. Rounded examples and conventions are checked independently rather than copied.
- Professor James P. Dow Jr., FIN 303, Part 8: Topics in Capital Budgeting: uncertainty, flexibility and unequal project lives.
- Capital Budgeting – Risk Analysis, uploaded five-page extract: probability, certainty equivalents and risk-adjusted approaches.
For continued study: Dayananda Chapters 1–2 for foundations; 5–6 for appraisal; 7–9 for risk; 11–12 for constrained selection; 16 for international appraisal. Review here is targeted, not a claim that the entire 343-page book was read.
Prasanna Chandra: further-reading map
The 9th-edition contents identifies Chapter 8 (Investment Criteria), Chapter 9 (Project Cash Flows), Chapter 10 (Cost of Capital), Chapter 11 (Project Risk Analysis), Chapter 12 (Project Rate of Return) and Chapter 13 (Special Decision Situations). These are reading directions, not claims that their absent pages were consulted.
Public reference links
All rates, taxes, exchange rates and business figures in the examples are teaching assumptions. Results use unrounded calculations and are rounded for display. Excel formulas use English function names and commas.