Inflation Can Flip a Spreadsheet's Investment Verdict When Cash Flows and Discount Rates Do Not Match
Match nominal cash flows with nominal discount rates, or real flows with real rates. Follow an original one-year example and audit the assumptions in a model.
A spreadsheet discounts next year's inflated receipts using a rate expressed after removing inflation. The formula runs, the chart looks plausible, and the project appears attractive. The error sits in the meaning of the inputs rather than the spreadsheet syntax.
A present-value calculation needs a consistent price basis. Nominal cash flows include the assumed change in the general price level. Real cash flows express purchasing power relative to a stated base period. A rate appropriate to one basis cannot be quietly applied to the other.
The UK's 2026 Green Book, updated on 5 February 2026, distinguishes inflation adjustment from discounting in public appraisal. Its supplementary discounting guidance provides the social-appraisal context. This article uses that conceptual distinction in an original arithmetic example; it does not prescribe the government's social discount rate for a private investment.
State the base period before entering a rate
“Today's money” needs a date and a unit. A model should identify the currency, price base, timing convention and whether each input is nominal or real.
A figure can increase in nominal terms while remaining unchanged in real terms. If the general price level rises by an assumed 10%, 110 units next year have the same purchasing power as 100 units at the starting date under this simplified assumption.
That conversion does not mean a business's actual selling price or input cost will rise by exactly 10%. Relative prices can change differently. The general inflation adjustment and the forecast of a specific price are separate modeling decisions.
Separate inflation from time value
Removing inflation changes the price basis. Discounting changes the weight given to an amount at a future date relative to the valuation date. Neither operation can silently substitute for the other.
The Green Book's social-appraisal rules belong to their stated government context. A business selecting a financing or investment rate must consider its own appropriate risk and cash-flow conventions. The consistency principle is useful across contexts; a particular public-sector rate is not automatically transferable.
| Model element | Question to label explicitly |
|---|---|
| Cash flow | Does it include general inflation? |
| Discount rate | Is it nominal or real? |
| Valuation date | When is present value measured? |
| Timing | Beginning, middle or end of the period? |
| Risk treatment | Which assumptions or adjustments represent uncertainty? |
This original checklist is a way to inspect a model, not a complete valuation standard.
Work a one-year example
Assume a fictional project costs 100 units now and produces one receipt at the end of the next year. The receipt is 110 nominal units. Assume general inflation is exactly 10% for this teaching scenario, so the receipt is 100 units in starting-period purchasing power.
Assume a 5% real discount rate. With these deterministic conventions, the matching nominal rate satisfies one plus nominal rate equals one plus real rate multiplied by one plus inflation. It is 1.05 × 1.10 − 1 = 15.5%.
Simply adding five and ten gives 15%, which omits the cross-product. The difference is small in some scenarios but the exact relationship is straightforward enough to state without the shortcut.
Calculate both consistent routes
Discount the nominal receipt using the matching nominal rate: 110 divided by 1.155 gives approximately 95.2381. Alternatively, remove inflation and discount the real receipt: 100 divided by 1.05 gives the same value.
Subtract the initial cost of 100 in either route. The illustrative net present value is about −4.7619.
| Route through the original example | Calculation | Present value | NPV after initial 100 |
|---|---|---|---|
| Nominal with nominal | 110 / 1.155 | 95.2381 | −4.7619 |
| Real with real | 100 / 1.05 | 95.2381 | −4.7619 |
| Incorrect mixed basis | 110 / 1.05 | 104.7619 | +4.7619 |
The mixed calculation reverses the sign of the answer without changing the underlying fictional project. That is why input labels matter as much as the visible formula.
Check the algebra rather than guessing a correction
In this one-period model, dividing a nominal flow by one plus inflation and then by one plus the real rate is equivalent to dividing by their product. The matching nominal rate represents that product minus one.
Do not generalize this simple identity into a claim that uncertain inflation, risk premiums, tax rules and financing terms can always be collapsed into one known rate. The example holds those complications outside its boundary.
If a model's real and nominal routes do not agree under identical deterministic assumptions, inspect the price base, conversion factors, timing and rounding. The discrepancy is a diagnostic signal; it does not identify the error without further checking.
Keep relative price changes in the right place
Suppose a particular input rises faster than general inflation. Removing the general inflation component does not remove that relative cost increase. A real-price forecast can therefore change over time.
For a fictional service contract, the analyst might model labor costs and equipment costs separately because their assumed price paths differ. Document the source and reason for each path. Do not make every real cost flat merely because the model uses a real discount rate.
Equally, avoid adding an inflation allowance twice: once in a supplier's already-escalated quote and again in a blanket escalation row. Trace whether the quoted value is expressed at the base date or a future payment date.
Distinguish valuation from payment capacity
A present value is not the amount of cash required on a future invoice date. A project can have a favorable appraisal while needing financing before its benefits arrive. It can also have adequate short-term cash while failing the chosen valuation test.
Keep nominal funding requirements and payment dates visible in the financial plan. A cost converted to base-year purchasing power cannot be paid to a supplier as though it were the future invoice amount.
The Green Book itself distinguishes economic appraisal from the public-sector financial dimension. That distinction reinforces the broader reading habit: identify which question a model answers before transferring its output to another decision.
Audit a spreadsheet with one controlled change
Start with a copy of the model. Record the current price basis and rate convention, then translate a simple deterministic scenario into the other basis. Keep the economic assumptions unchanged while checking whether present values reconcile.
If they do, proceed to inspect uncertainty and sensitivity. If they do not, correct the inconsistency before interpreting a sensitivity chart. A sophisticated scenario dashboard cannot compensate for mismatched units of purchasing power.
For related distinctions, read profit versus cash flow and bond cash flows and market pricing. In each case, the calculation becomes more useful when the quantities retain their meanings.
Sources and limits
- HM Treasury: The Green Book 2026, updated 5 February 2026; checked 26 October 2026.
- Green Book supplementary guidance on discounting, updated 5 February 2026; checked 26 October 2026.
All rates, receipts and project values above are original assumptions. The example demonstrates consistency, not a forecast or personalized investment recommendation.