CAPITAL BUDGETING · FORMULA AND EXCEL
1. Cash-flow estimation comes before the evaluation formula
A precise NPV formula cannot rescue an incomplete cash-flow forecast. Establish the decision date, realistic alternative, economic life, operating drivers, investment timing, tax treatment, working capital and terminal obligations before calculating any return.
New project versus replacement
A new project generally adds a new stream of business activity. A replacement decision compares the firm with the new asset against the firm continuing with the old asset. The old asset’s remaining earnings and tax deductions belong in the counterfactual.
| Flow | New project | Replacement project |
|---|---|---|
| Time zero | New installed capex and new NWC | New capex less after-tax old-asset sale proceeds, plus incremental NWC |
| Operating years | New project cash revenue less incremental cash costs and taxes | Difference in revenue, costs and taxes between replace and keep |
| Terminal | After-tax asset proceeds and recoverable NWC, less closure costs | Difference in after-tax terminal proceeds plus incremental NWC released |
For replacement, always write two columns first: Replace and Keep. A third column, Replace minus Keep, is the cash-flow stream to discount.
CAPITAL BUDGETING · FORMULA AND EXCEL
2. Why depreciation matters when it is not a cash payment
OCF = EBITDA × (1−T) + tax depreciation × T
Purchase and installation consume cash when paid. Depreciation allocates asset cost across periods for accounting or tax purposes. Its direct expense is non-cash, but an allowable deduction can reduce cash tax. Add depreciation back after deriving after-tax profit because no second cash payment occurred.
Accounting versus tax depreciation
Use tax depreciation to estimate cash tax. Accounting depreciation can differ. When calculating accounting ARR use the defined accounting profit and asset values. A deferred tax accounting entry is not automatically a current cash tax flow.
Tax capacity and timing
The shortcut shield D × T assumes deductions reduce cash tax in that period. Loss carry-forwards, deduction restrictions, minimum taxes and payment lags can defer or limit the shield. Model the realised cash-tax schedule when that shortcut does not hold.
CAPITAL BUDGETING · FORMULA AND EXCEL
3. Depreciation methods and the timing of tax relief
Declining-balance Dₜ = rate × opening tax written-down value
Tax depreciation may follow rules different from these illustrative schedules. The eligible basis, start date, rates, asset grouping and disposal rules depend on jurisdiction. Excel’s SLN or DDB is a calculation function; it does not determine which schedule the tax authority permits.
| Year | Straight-line D | Illustrative accelerated D | SL shield at 25% | Accelerated shield |
|---|---|---|---|---|
| 1 | 20 | 40 | 5 | 10 |
| 2 | 20 | 24 | 5 | 6 |
| 3 | 20 | 14.4 | 5 | 3.6 |
| 4 | 20 | 8.64 | 5 | 2.16 |
| 5 | 20 | 12.96 | 5 | 3.24 |
Both schedules write a 100 basis down to zero over five years. The accelerated example uses 40% of opening book value in years 1–4 and writes off the remaining 12.96 in year 5. Assuming both are permitted, shields are immediately usable and tax rate is 25%, total undiscounted shields equal 25. At 12%, PV shields are 18.02 for straight-line and 19.49 for accelerated. Acceleration adds 1.46 of present value through timing.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B2 | 100 | Tax depreciable basis |
| B3 | 0 | Residual tax book value |
| B4 | 5 | Years |
| B5 | 0.25 | Tax rate |
| B6 | 0.12 | Discount rate for this teaching comparison |
| B7 | 0.4 | Accelerated rate |
| B10:F10 | 1,2,3,4,5 | Years |
| B11 | =SLN($B$2,$B$3,$B$4) | Copy across through F11 |
| B12 | =B2 | Opening tax value, year 1 |
| B13 | =B12*$B$7 | Year-1 accelerated depreciation |
| C12 | =B12-B13 | Next opening tax value; copy through F12 |
| C13 | =C12*$B$7 | Copy C13 through E13 |
| F13 | =F12-$B$3 | Final-year write-off to assumed residual |
| B15 | =B11*$B$5 | SL shield; copy across |
| B16 | =B13*$B$5 | Accelerated shield; copy across |
| B18 | =NPV(B6,B15:F15) | PV straight-line shields = 18.02 |
| B19 | =NPV(B6,B16:F16) | PV accelerated shields = 19.49 |
Accelerated depreciation does not by itself improve the asset’s productivity. If tax capacity is absent or disposal recapture differs, the timing benefit can change. The discount rate for tax shields should reflect their risk in a full valuation.
CAPITAL BUDGETING · FORMULA AND EXCEL
4. New-project cash flows: formula and Excel together
Return to the common case: installed cost 100, NWC 20, five-year life, EBITDA 40 a year, tax depreciation 20 a year, tax 25%, gross terminal salvage 8 and zero terminal tax book value.
OCF = 40(1−0.25) + 20(0.25) = 35
After-tax salvage = 8 − (8−0)(0.25) = 6
TCF = 6 + 20 = 26
Reproduce it in Excel
Use a new sheet named NewProject. Amounts are ₹ lakh; receipts are year-end.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B2 | 100 | Installed cost |
| B3 | 20 | Initial NWC |
| B4 | 40 | Annual EBITDA |
| B5 | 20 | Annual tax depreciation |
| B6 | 0.25 | Tax rate |
| B7 | 5 | Life |
| B8 | 8 | Gross terminal sale |
| B9 | 0 | Terminal tax book value |
| B10 | 0.12 | Required return |
| B12 | =-B2-B3 | Initial flow = −120 |
| B13 | =B4*(1-B6)+B5*B6 | OCF = 35 |
| B14 | =B8-(B8-B9)*B6+B3 | TCF = 26 |
| B17:G17 | 0,1,2,3,4,5 | Years |
| B18 | =B12 | CF0 |
| C18:F18 | =$B$13 | Enter in each year 1–4 |
| G18 | =$B$13+$B$14 | Year 5 = 61 |
| B20 | =NPV(B10,C18:G18)+B18 | NPV = 20.92 |
Forecast the operating drivers, not just the cash-flow total
Support volume with demand and capacity evidence; forecast price, variable cost and fixed cash costs separately; allow a commissioning ramp-up and downtime where relevant. Link NWC to receivable, inventory and payable requirements. A flat OCF is appropriate only when those simplified assumptions are intended.
CAPITAL BUDGETING · FORMULA AND EXCEL
5. Replacement decision: specify the keep-old alternative
A new machine costs 120 installed. The old machine can be sold today for 50 and has tax book value 40. Tax is 25%, so immediate after-tax proceeds are 50 − (50−40)×25% = 47.50.
Keeping the old machine permits tax depreciation of 8 a year for five years. Replacing it gives new tax depreciation of (120−20)/5 = 20 a year. The new machine has terminal tax book value and gross sale value both 20; the old machine would have zero book value and sale value at that date.
The new machine saves 30 of annual operating cost but adds maintenance of 3. It requires 5 more NWC than keeping the old machine. Assume equal output quality and capacity, no downtime, usable tax relief and a 12% required return.
| Input | Replace | Keep | Incremental |
|---|---|---|---|
| Installed investment today | 120 | 0 | 120 |
| Old asset proceeds today | 47.50 net | 0 | 47.50 inflow |
| Extra operating NWC | 5 | 0 | 5 |
| Annual cost savings relative to old | 30 less 3 maintenance | 0 | 27 |
| Annual tax depreciation | 20 | 8 | 12 |
| Terminal after-tax asset value | 20 | 0 | 20 |
| NWC released at terminal | Extra 5 recovered | 0 | 5 |
The old purchase price is sunk. Its current tax book value is still relevant because it determines the assumed tax on sale and remaining deductions. Current sale proceeds are an opportunity cost of keeping it.
CAPITAL BUDGETING · FORMULA AND EXCEL
6. Replacement cash-flow calculation: initial, annual and terminal
Incremental OCF = ΔEBITDA(1−T) + ΔD × T
= 27 × 75% + (20−8) × 25% = 23.25
Incremental TCF = (20−0) + 5 = 25
Cash-flow stream: −77.50, 23.25, 23.25, 23.25, 23.25, 48.25. NPV at 12% is ₹20.50 lakh. The last cash flow is 23.25 OCF plus 25 terminal flow.
The old depreciation shield is not available once the asset is sold. Using new depreciation of 20 as though it were entirely incremental would overstate annual OCF by 8 × 25% = 2.
Reproduce it in Excel
Use a new sheet named Replacement. Values are ₹ lakh. The disposal-tax formula assumes separate asset gain/loss treatment for teaching.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B2 | 120 | New installed machine cost |
| B3 | 50 | Old machine sale value today |
| B4 | 40 | Old tax book value today |
| B5 | 0.25 | Tax rate |
| B6 | 5 | Additional NWC |
| B7 | 30 | Annual gross savings |
| B8 | 3 | Additional annual maintenance |
| B9 | 20 | New annual tax depreciation |
| B10 | 8 | Old annual tax depreciation if kept |
| B11 | 20 | New terminal gross sale |
| B12 | 20 | New terminal tax book value |
| B13 | 0 | Old terminal gross sale if kept |
| B14 | 0 | Old terminal tax book value if kept |
| B15 | 0.12 | Required return |
| B17 | =B3-(B3-B4)*B5 | After-tax proceeds today = 47.50 |
| B18 | =-B2-B6+B17 | CF0 = −77.50 |
| B19 | =B7-B8 | Incremental EBITDA = 27 |
| B20 | =B9-B10 | Incremental depreciation = 12 |
| B21 | =B19*(1-B5)+B20*B5 | Annual incremental OCF = 23.25 |
| B22 | =(B11-(B11-B12)*B5)-(B13-(B13-B14)*B5)+B6 | Incremental TCF = 25 |
| B25:G25 | 0,1,2,3,4,5 | Years |
| B26 | =B18 | Initial cash flow |
| C26:F26 | =$B$21 | Enter in each year 1–4 |
| G26 | =$B$21+$B$22 | Year 5 = 48.25 |
| B28 | =NPV(B15,C26:G26)+B26 | Replacement NPV = 20.50 |
Actual Indian tax depreciation can involve asset blocks and rules that differ from this separate-asset disposal formula. Use the applicable cash-tax calculation when modelling a real Indian company.
CAPITAL BUDGETING · FORMULA AND EXCEL
7. Reconcile replace versus keep without double counting
Construct EBIT and tax separately for each alternative, then compare OCF. In the simplified savings case, incremental EBIT is 27−12 = 15; incremental tax is 3.75; incremental PAT is 11.25; adding the 12 incremental depreciation gives OCF 23.25.
Reproduce it in Excel
Continue on Replacement. This linear incremental-tax route works only under the stated identical tax-rate and immediate-tax-capacity assumptions.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B30 | =B19-B20 | Incremental EBIT = 15 |
| B31 | =B30*B5 | Incremental cash tax = 3.75 |
| B32 | =B30-B31+B20 | Independent OCF route = 23.25 |
| B33 | =B32-B21 | Reconciliation difference = 0 |
Items often missed
- Downtime and start-up training reduce near-term benefits.
- Installation, removal and site-restoration costs have their own timing and tax treatment.
- Old maintenance costs may rise if the machine is retained.
- Changes in capacity may require forecasts of incremental sales and NWC.
- Using different lives requires a comparable service horizon and appropriate terminal values.
CAPITAL BUDGETING · FORMULA AND EXCEL
8. A cash-flow estimation checklist and teaching questions
- Define the without-project or keep-old alternative explicitly.
- Separate past payments from future incremental consequences.
- Build volumes, prices, costs, taxes, depreciation and NWC from stated drivers.
- Include initial disposal consequences and terminal differences.
- Reconcile OCF using both EBIT and EBITDA routes.
- Match the discount rate, currency, timing and financing basis.
- Stress the cash-flow assumptions that could change the decision.
Why can an old machine’s book value matter if its cost is sunk?
Book value can determine future tax deductions and the tax consequences of disposal. Those future cash-tax differences remain relevant.
Should we add the full new depreciation tax shield in a replacement case?
Only the difference from the shield available if the old asset is kept is incremental, subject to actual tax rules and capacity.
Does recovery of working capital include every rupee originally invested?
Only amounts expected to be collected or realised. Bad debts, obsolete inventory and settlement costs can reduce terminal recovery.
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.