CAPITAL BUDGETING · FORMULA AND EXCEL
1. Choose the metric for the question
All case amounts are ₹ lakh unless a per-unit ₹ price is stated. Inputs are original teaching assumptions, not a company forecast. Cash flows occur at year-end unless otherwise stated.
Use the six-year timeline from Topic 1. NPV is the main value criterion. Payback describes recovery, ARR describes accounting performance, and IRR / MIRR describe percentage returns. None makes weak cash-flow assumptions reliable.
Reproduce it in Excel
Create a sheet named Evaluation. B2:B4 are percentage inputs; cash flows are ₹ lakh.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B2 | 0.12 | Required return |
| B3 | 0.12 | MIRR finance rate |
| B4 | 0.12 | MIRR reinvestment rate |
| B7:G7 | 0, 1, 2, 3, 4, 5 | Years |
| B8:G8 | -120, 35, 35, 35, 35, 61 | Cash flows |
| B9 | =B8/(1+$B$2)^B7 | PV; copy across through G9 |
| B10 | =B8 | Initial cumulative cash |
| C10 | =B10+C8 | Copy across through G10 |
| B11 | =B9 | Initial discounted cumulative cash |
| C11 | =B11+C9 | Copy across through G11 |
CAPITAL BUDGETING · FORMULA AND EXCEL
2. Payback period: when is the initial cash recovered?
After year 3, ₹105 lakh has been recovered. ₹15 lakh remains. With ₹35 lakh OCF in year 4: PBP = 3 + 15/35 = 3.43 years, assuming OCF accrues uniformly during that year. With strictly year-end receipts, recovery occurs at the end of year 4.
Reproduce it in Excel
On Evaluation, E10 is cumulative cash at year 3 and F8 is year-4 cash flow.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B14 | =3+ABS(E10)/F8 | Interpolated PBP = 3.43 years |
Compare with a management-set recovery limit. Payback ignores the time value of money and all cash flows after recovery; a quick payback can still accompany a poor investment.
CAPITAL BUDGETING · FORMULA AND EXCEL
3. Accounting rate of return: state the convention
Annual operating PAT is ₹15 lakh. We use average fixed-asset book value of (100 + 0)/2 = ₹50 lakh plus constant NWC ₹20 lakh. ARR = 15/70 = 21.43%. This operating ARR excludes the terminal disposal gain. Including it, or using initial investment as denominator, gives a different figure.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B16 | 15 | Average annual operating PAT |
| B17 | =(100+0)/2+20 | Average investment including NWC |
| B18 | =B16/B17 | Operating ARR = 21.43% |
ARR is based on accounting earnings and does not discount timing. Always specify depreciation, terminal-gain treatment and the denominator before comparing projects.
CAPITAL BUDGETING · FORMULA AND EXCEL
4. Net present value and value additivity
NPV = −120 + 35/1.12 + 35/1.12² + 35/1.12³ + 35/1.12⁴ + 61/1.12⁵ = ₹20.92 lakh. The project adds this present value above the required 12% return under the assumptions.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B20 | =SUM(B9:G9) | NPV from the PV row |
| B21 | =NPV(B2,C8:G8)+B8 | NPV using Excel; exclude time zero from NPV() |
Decision and additivity
Accept a positive-NPV independent project when resources and constraints allow. NPV = 0 meets the required return without extra value. If separate project values are measured consistently, NPV(A + B) = NPV(A) + NPV(B). Include synergies and cannibalisation once before applying this principle.
CAPITAL BUDGETING · FORMULA AND EXCEL
5. Internal rate of return and its limitations
Our conventional investment has IRR = 18.28%, above the 12% hurdle. Verify by discounting at that IRR: NPV is approximately zero.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B23 | =IRR(B8:G8) | Periodic IRR = 18.28% |
| B24 | =NPV(B23,C8:G8)+B8 | Residual NPV; approximately zero |
Multiple IRRs
The stream −100, +230, −132 has IRRs of 10% and 20%. At 12%, its NPV is +0.13. Different Excel guesses can locate different roots. Sign changes indicate possible multiple roots, not a guarantee that every sign change yields an economically meaningful IRR.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B28:D28 | -100, 230, -132 | Separate non-conventional case |
| B29 | =IRR(B28:D28,0.05) | One root: 10% |
| B30 | =IRR(B28:D28,0.25) | Another root: 20% |
| B31 | =NPV(12%,C28:D28)+B28 | Use NPV at the appropriate hurdle |
IRR can be absent, ambiguous or misleading for mutually exclusive projects, financing-type streams and changes in scale. The standard accept-if-IRR-exceeds-k rule here applies to a conventional investment outlay followed by positive receipts.
CAPITAL BUDGETING · FORMULA AND EXCEL
6. Modified IRR: make financing and reinvestment rates explicit
With finance and reinvestment rates both at 12%, MIRR = 15.66%. The horizon n is five periods from time zero to year 5.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B33 | =MIRR(B8:G8,B3,B4) | MIRR = 15.66% |
| B34 | =C8*(1+B4)^4+D8*(1+B4)^3+E8*(1+B4)^2+F8*(1+B4)+G8 | Terminal value of positive flows |
| B35 | =(B34/ABS(B8))^(1/5)-1 | Independent route; initial outflow only |
IRR is a mathematical zero-NPV rate; calculating it does not physically reinvest cash. Interpreting that rate as a compound terminal-wealth return requires an additional assumption about intermediate receipts. MIRR makes such rates explicit and avoids multiple-IRR ambiguity for a fixed aggregation convention. It still does not replace NPV when project scale differs.
CAPITAL BUDGETING · FORMULA AND EXCEL
6A. MIRR illustration: trace every compounding period
Use the supplied three-year dollar case: A = −10,000, 7,500, 4,000, 1,000; B = −10,000, 1,000, 5,000, 9,000. Finance and reinvestment rates are both 10%. Both initial outlays are 10,000.
| Flow received | A flow | Periods to year 3 | A terminal value | B flow | B terminal value |
|---|---|---|---|---|---|
| Year 1 | 7500 | 2 | 9075 | 1000 | 1210 |
| Year 2 | 4000 | 1 | 4400 | 5000 | 5500 |
| Year 3 | 1000 | 0 | 1000 | 9000 | 9000 |
| Total | 14475 | 15710 |
TVB = 1,000(1.1)² + 5,000(1.1) + 9,000 = 15,710
MIRRA = (14,475/10,000)1/3 − 1 = 13.12%
MIRRB = (15,710/10,000)1/3 − 1 = 16.25%
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B2 | 0.1 | Finance rate |
| B3 | 0.1 | Reinvestment rate |
| B5:E5 | -10000,7500,4000,1000 | Project A, dollars |
| B6:E6 | -10000,1000,5000,9000 | Project B |
| G5 | =C5*(1+$B$3)^2+D5*(1+$B$3)+E5 | A terminal value; copy down for B |
| H5 | =(G5/ABS(B5))^(1/3)-1 | Manual-route MIRR; copy down |
| I5 | =MIRR(B5:E5,$B$2,$B$3) | Excel MIRR; copy down |
The correct results are MIRR(A) 13.12% and MIRR(B) 16.25%. Retain the exact exponent 1/3 instead of rounding it to 0.3333 before calculation.
What does agreement with NPV establish?
With equal initial outlays, equal horizons and a common reinvestment rate equal to the NPV discount rate, MIRR and NPV rank these projects consistently. That conclusion is not universal for different scales, lives or rate conventions. Continue to use NPV for the comparable investment decision.
For these exact cash flows IRR also favours B: A = 16.64%, B = 17.60%. Therefore this case illustrates MIRR calculation rather than the resolution of an actual NPV–IRR ranking conflict. A genuine conflict variant appears in the project-comparison topic.
CAPITAL BUDGETING · FORMULA AND EXCEL
7. Profitability index: value per unit of capital
For one initial outlay: PI = 1 + NPV / initial investment
PV of future receipts = ₹140.92 lakh. PI = 140.92/120 = 1.1743. Each ₹1 invested produces ₹1.1743 of discounted receipts, including recovery of the investment.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B37 | =NPV(B2,C8:G8)/ABS(B8) | PI = 1.1743 |
| B38 | =1+B21/ABS(B8) | Cross-check for this conventional stream |
PI above 1 agrees with positive NPV for this stream. With later outlays, define PV benefits and costs carefully. PI ranking alone may not maximise total NPV for indivisible projects or multiple budget constraints.
CAPITAL BUDGETING · FORMULA AND EXCEL
8. Discounted payback and cash-flow timing
At end-year 4, cumulative discounted cash flow is −₹13.69 lakh. Year-5 OCF has PV ₹19.86 lakh. If OCF accrues uniformly and terminal cash arrives only at year-end, discounted recovery is approximately 4 + 13.69/19.86 = 4.69 years. With all receipts at year-end, recovery occurs at year 5.
Interpolating against the full year-5 ₹61 lakh receipt would incorrectly spread the terminal ₹26 lakh throughout the year. Fractional discounted payback is an approximation whose timing convention must be stated.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B40 | =4+ABS(F11)/(35/(1+B2)^5)) | Approximate DPP using OCF only; 4.69 years |
Discounting improves the recovery calculation, but discounted payback still ignores value after the recovery date.
CAPITAL BUDGETING · FORMULA AND EXCEL
9. Excel dates, periods and common errors
| Situation | Excel approach | Check |
|---|---|---|
| Annual equal intervals | NPV(k,year1:yearN)+CF0; IRR(CF0:CFN) | k and the IRR are per period |
| Irregular dated flows | XNPV(annual_rate,values,dates); XIRR(values,dates) | Include dated CF0 inside both ranges |
| Monthly equal intervals | Monthly k = (1+annual k)^(1/12)−1 | Annualise a monthly IRR as (1+IRR_month)^12−1 |
| MIRR | MIRR(values,finance_rate,reinvest_rate) | All rates match the period |
| Data checks | Use actual numeric zero for zero-flow periods | Blank/text entries can shift or omit periods |
Use Excel’s Goal Seek as a second IRR route: set the NPV cell to zero by changing a candidate rate. For non-conventional flows, inspect an NPV profile and root sensitivity rather than trusting one Solver or Goal Seek result.
CAPITAL BUDGETING · FORMULA AND EXCEL
10. Try the evaluation laboratory
Change the conventional cash-flow assumptions below. All are ₹ lakh, paid at year-end. This lab reports NPV, IRR, MIRR and PI; it deliberately avoids fractional payback assumptions.
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
- OpenStax: NPV
- OpenStax: IRR
- OpenStax: PI, MIRR and discounted payback
- Microsoft: NPV timing convention
- Microsoft: MIRR
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.