CAPITAL BUDGETING · FORMULA AND EXCEL
1. Start with a decision model and risk drivers
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 original manufacturing case: initial investment 120, five years, annual base OCF 35, terminal flow 26, discount rate 12%. Test demand, price, variable cost, fixed cost, investment timing, tax capacity, working capital and terminal proceeds. Describe which assumptions are uncertain and how they interact.
Reproduce it in Excel
Create a sheet named Risk. Tax losses are assumed usable immediately, as in the base case.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B2 | 20000 | Annual units Q |
| B3 | 1000 | ₹ selling price p |
| B4 | 600 | ₹ variable cost v |
| B5 | 40 | Fixed costs ₹ lakh |
| B6 | 20 | Depreciation ₹ lakh |
| B7 | 0.25 | Tax rate |
| B8 | 0.12 | Required return |
| B9 | 5 | Years |
| B10 | 120 | Initial outlay |
| B11 | 26 | Terminal flow |
| B13 | =(B2*(B3-B4)/100000-B5)*(1-B7)+B6*B7 | Annual OCF |
| B14 | =(1-(1+B8)^(-B9))/B8 | Annuity factor |
| B15 | =-B10+B13*B14+B11/(1+B8)^B9 | NPV |
CAPITAL BUDGETING · FORMULA AND EXCEL
2. Sensitivity analysis: vary one input at a time
Reduce volume from 20,000 to 18,000 units, keeping price and costs unchanged. OCF falls from 35 to 29. NPV falls from +20.92 to −0.71 lakh. This identifies volume as a critical assumption; it does not tell us the probability of low volume.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| D2:D4 | 18000, 20000, 22000 | Volume grid |
| E2 | =-$B$10+((D2*($B$3-$B$4)/100000-$B$5)*(1-$B$7)+$B$6*$B$7)*$B$14+$B$11/(1+$B$8)^$B$9 | Copy down for transparent formula sensitivity |
Excel Data Table alternative
Place volumes down D3:D7 and link E2 to B15. Select D2:E7, choose Data → What-If Analysis → Data Table and set the column input cell to B2. A two-variable table can combine volume and price. Keep input units and calculation settings explicit.
CAPITAL BUDGETING · FORMULA AND EXCEL
3. Scenario analysis: change coherent sets of inputs
| Scenario | Q | Price ₹ | Variable ₹ | Fixed lakh | OCF lakh | NPV lakh |
|---|---|---|---|---|---|---|
| Downside | 18000 | 950 | 620 | 42 | 18.05 | -40.18 |
| Base | 20000 | 1000 | 600 | 40 | 35 | 20.92 |
| Upside | 22000 | 1050 | 580 | 38 | 54.05 | 89.59 |
These are internally specified illustrative bundles. Depreciation, tax, project life, initial outlay and terminal flow remain unchanged. “Worst case” means the specified stress, not the worst outcome imaginable.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| D10:G12 | 18000,950,620,42 / 20000,1000,600,40 / 22000,1050,580,38 | Three rows: volume, price, variable cost, fixed cost |
| H10 | =(D10*(E10-F10)/100000-G10)*(1-$B$7)+$B$6*$B$7 | Scenario OCF; copy down |
| I10 | =-$B$10+H10*$B$14+$B$11/(1+$B$8)^$B$9 | Scenario NPV; copy down |
Expected NPV and dispersion
Assign probabilities 25%, 50%, 25% only as an explicit judgement. Expected NPV = ΣpᵢNPVᵢ; variance = Σpᵢ(NPVᵢ − mean)². These measures describe the assumed scenario distribution and are not a guarantee.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| J10:J12 | 0.25, 0.50, 0.25 | Probabilities; must sum to 1 |
| B18 | =SUMPRODUCT(I10:I12,J10:J12) | Expected NPV |
| K10 | =(I10-$B$18)^2*J10 | Weighted squared deviation; copy down |
| B19 | =SQRT(SUM(K10:K12)) | Population scenario standard deviation |
A coefficient of variation (standard deviation / mean) becomes unstable near a zero mean and is hard to interpret for negative means. Standard deviation measures dispersion, not the required market return.
CAPITAL BUDGETING · FORMULA AND EXCEL
4. Accounting, cash and financial break-even
Cash QBE = fixed cash costs / unit contribution
Financial QBE: solve NPV(Q) = 0
Unit contribution = ₹400 = ₹0.004 lakh. Accounting break-even = (40 + 20)/0.004 = 15,000 units. Pre-tax cash operating break-even = 40/0.004 = 10,000 units. Financial break-even includes investment recovery and the 12% required return.
OCF = 0.003Q − 25. Required OCF = [120 − 26/1.12⁵]/3.604776 = 29.1965. Hence financial Q = (29.1965 + 25)/0.003 = 18,065.5 units a year.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B22 | =(B5+B6)/((B3-B4)/100000) | Accounting break-even |
| B23 | =B5/((B3-B4)/100000) | Pre-tax cash operating break-even |
| B24 | =(B10-B11/(1+B8)^B9)/B14 | OCF required for NPV zero |
| B25 | =(B24+B5*(1-B7)-B6*B7)/(((B3-B4)/100000)*(1-B7)) | Financial break-even units |
Excel Goal Seek provides a second route: set B15 to zero by changing B2. The closed-form result assumes level annual volume, constant costs, fixed NWC and terminal flow. If NWC depends on sales, link it to volume before using Goal Seek.
CAPITAL BUDGETING · FORMULA AND EXCEL
5. Monte Carlo simulation: model an input distribution
A simulation draws uncertain inputs repeatedly, recalculates project NPV and summarises the distribution. Select distributions using evidence, represent dependencies and timing, and test the model before drawing thousands of samples.
Reproducible classroom setup in Excel
As a deliberately simple exercise, let annual volume be a single project-wide draw uniformly distributed between 16,000 and 24,000 units. Hold all other assumptions fixed. This models persistent demand uncertainty; it does not generate independent demand shocks in each year.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| A31:A1030 | 1 to 1000 | Trial IDs |
| B31 | =16000+8000*RAND() | Uniform volume draw; copy down |
| C31 | =(B31*($B$3-$B$4)/100000-$B$5)*(1-$B$7)+$B$6*$B$7 | OCF from volume; copy down |
| D31 | =-$B$10+C31*$B$14+$B$11/(1+$B$8)^$B$9 | Trial NPV; copy down |
| B28 | =AVERAGE(D31:D1030) | Simulated mean |
| B29 | =COUNTIF(D31:D1030,"<0")/COUNT(D31:D1030) | Simulated probability of NPV below zero |
RAND is volatile: samples change when Excel recalculates. To keep an audit record, copy the draw/result range and paste values into a dated snapshot. Build a histogram and calculate percentiles, but do not report one random run as a stable finding. Correlated prices, volume and costs need joint modelling. Sampling independence by default can understate or distort risk.
CAPITAL BUDGETING · FORMULA AND EXCEL
6. Decision trees and staged investment
Work backwards through the tree. At each decision node choose the best available action. At a chance node combine outcomes using justified probabilities, valuations and discounting assumptions.
A pilot costs 10 now. In one year there is a 60% chance of a strong market, in which immediate launch costs 100 and the launch opportunity is worth 150 at that date. In the weak branch the opportunity is worth 70, so decline the 100 launch. With a stated 10% risk-adjusted rate for this illustrative expected payoff, project value = −10 + [0.6 × max(150−100,0) + 0.4 × max(70−100,0)]/1.1 = 17.27.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B2 | 10 | Pilot today |
| B3 | 0.6 | Strong-market probability |
| B4 | 150 | Strong opportunity value at t1 |
| B5 | 70 | Weak opportunity value at t1 |
| B6 | 100 | Optional launch cost at t1 |
| B7 | 0.1 | Illustrative risk-adjusted rate |
| B9 | =-B2+(B3*MAX(B4-B6,0)+(1-B3)*MAX(B5-B6,0))/(1+B7) | Pilot-plus-option value |
This is an expected-payoff decision example, not a calibrated option-pricing model. The probability and risk adjustment must be consistent. Committed development costs and the option to abandon should not be confused.
CAPITAL BUDGETING · FORMULA AND EXCEL
7. Risk-adjusted rates and certainty-equivalent cash flows
Certainty-equivalent route: NPV = CF₀ + ΣCEₜ/(1+rf)t
For a separate one-year project costing 90 with expected receipt 110, k = 10% gives NPV = 10. At risk-free 5%, a certainty-equivalent receipt of 105 also gives NPV = 10. The CE factor is 105/110 = 0.9545.
Reproduce it in Excel
Use a separate worksheet for this illustration.
| Cell / range | Enter / formula | Meaning |
|---|---|---|
| B2 | -90 | Initial flow |
| B3 | 110 | Expected receipt |
| B4 | 0.1 | Risk-adjusted rate |
| B5 | 0.05 | Risk-free rate |
| B7 | =B2+B3/(1+B4) | Risk-adjusted NPV |
| B8 | =B3*(1+B5)/(1+B4) | Consistent CE receipt |
| B9 | =B2+B8/(1+B5) | Same NPV from CE |
Do not reduce expected cash flows for the same priced risk and also increase the discount rate for it. CAPM estimates a cost of equity, not automatically the right rate for every project cash-flow definition. Expected losses and systematic-risk premia have different roles.
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.