Net Present Value (NPV): How to Discount Cash Flows and Read the Answer

1. Why a future dollar is worth less, in arithmetic rather than words

You are offered $100 today or $100 in a year. Beyond simple impatience, name one concrete reason today is worth more.

Because $100 today can be doing something for the next year. Put it somewhere that returns 10% and by this time next year it is $110. So $100 today and $100 next year are not the same offer, and the size of the difference is whatever return you could have got.

Run that backwards and you have the whole method. If $100 today grows to $110 in a year, then $110 arriving in a year is worth exactly $100 today. Growing forwards multiplies by 1.10. Coming backwards divides by 1.10. The dividing is called discounting, and the 10% is called the discount rate.

Do it twice for two years. $100 today grows to $110, then to $121. So $121 in two years is worth $100 today, and the division is by 1.10 twice, which is 1.21. Three years is 1.331. The number you divide by grows fast, which is why money far in the future gets cut down hard.

Two numbers to hold on to, both from this page's project. At a 10% discount rate, a dollar arriving in five years is worth 62 cents today. Raise the rate to 15% and the same dollar is worth 50 cents. Nothing about the dollar changed. The rate did.

2. The discount factor, and the year-0 rule

What should the discount factor be for money you spend today?

One. Money spent today is already in today's dollars, so it does not get discounted at all, and dividing by 1 leaves it alone. That is not a special case bolted onto the formula. It falls out of it, because any number raised to the power of 0 is 1.

The discount factor for year t at rate r is (1 + r) raised to the power t. Present value is the cash flow divided by that factor.

Net present value is the sum of every year's present value, with the upfront cost included as a negative number at year 0. The word "net" is that subtraction. A present value that ignores the cost is not an NPV, and section five shows what that mistake looks like when it reaches a slide.

The decision rule is one line. NPV above zero means the project earns more than the rate you required of it, so it adds value. NPV below zero means it does not. NPV of exactly zero means it earns precisely the required return, no more.

3. The worked project, row by row

A project costs $500,000 today and pays back $850,000 over five years. Guess whether it clears a 10% required return, and by roughly how much.

Here is the project. A production line expansion: $500,000 to build, then five years of extra cash from the capacity it adds. The required return is 10%.

Check one row by hand to trust the rest. Year 3: 170,000 divided by 1.331 is 127,723.5, which rounds to 127,724. Add the present-value column and you get 137,236.

Look at the two totals on the bottom row, because they are the whole lesson. Undiscounted, the project nets $350,000. Discounted at 10%, it nets $137,236. The $212,764 difference is not a cost anyone pays and not a number in any ledger. It is the return you would have earned on that money elsewhere over five years, and NPV charges the project for it.

The verdict, in a sentence someone can act on: the line expansion is worth $137,236 more than the 10% return we could get elsewhere on the same $500,000. Notice how much work "at 10%" is doing in that sentence. Section six is about how much.

Say why the discounted total is smaller than the undiscounted one, in your own words, before reading on. If you can say it, the rest of this page is careful arithmetic.

4. The query, and the rate written as 10/100

In SQL, what does 10/100 return when both are whole numbers?

Zero. And that turns the whole NPV calculation into something else without changing a single row of data. Follow it through: 10/100 is 0, so 1 + 10/100 is 1, so POWER(1, year) is 1 for every year, so every cash flow is divided by 1 and no discounting happens at all. The query runs, returns $350,000, and $350,000 is just the raw sum of the cash-flow column.

That is the worst kind of wrong. It is not an error, it is not a null, and $350,000 is a believable answer for this project. The only way to catch it is to know it can happen.

The working query, with the rate written as a decimal.

SELECT ROUND(SUM(cash_flow / POWER(1 + 0.10, year)), 0) AS npv
FROM project_cash_flows
WHERE project = 'Line Expansion';        -- 137236

And the same idea one row at a time, which is what you actually want on screen, because a single NPV number is impossible to check.

SELECT year,
       cash_flow,
       ROUND(POWER(1 + 0.10, year), 4)             AS discount_factor,
       ROUND(cash_flow / POWER(1 + 0.10, year), 0) AS present_value
FROM project_cash_flows
WHERE project = 'Line Expansion'
ORDER BY year;

Three notes on the arithmetic, all worth checking once on your own database.

POWER itself is safe, because it returns a decimal even when handed whole numbers, so cash_flow / POWER(...) stays decimal. The danger is entirely in how the rate reaches it. Write rates as 0.10. If the rate has to come from a column, make that column a decimal type when the table is created, and check one value before you trust the output. The same trap in its percentage form is worked in gross vs operating vs net margin.

One portability note. POWER exists in SQLite, PostgreSQL, SQL Server, MySQL, Oracle, Snowflake and BigQuery, though older SQLite builds may not have it compiled in. Where it is missing, EXP(year * LN(1 + 0.10)) is the same calculation and works anywhere that has logs.

5. Two off-by-one errors, and which one flips the decision

Your cash-flow table was built with a row number instead of a year number, so the first inflow is labelled 0 instead of 1. Does NPV come out too high or too low?

Too high, and by a lot. Every inflow is being discounted one year less than it should be, so each one is worth more today than it really is, while the cost at year 0 is untouched. On this project the correct answer is $137,236 and the mislabelled one is $200,960, an overstatement of $63,724, which is 46%.

It matters because it can change the answer. Take the same five inflows against a $660,000 upfront cost instead of $500,000.

A row number and a year number look identical in a table. Nothing in the query can tell them apart. The check that catches it takes five seconds: the year-0 row is the money you spend today, and it should be the only negative one and the only one whose present value equals its cash flow. If your year-0 row is an inflow, the labels are shifted.

The second off-by-one is worth knowing because it behaves completely differently. If the whole stream shifts, cost and all, so the cost sits at year 1 and the inflows at 2 to 6, then every present value gets divided by one extra 1.10 and NPV goes from $137,236 to $124,760. That is exactly $137,236 divided by 1.10. Because everything moved together, the sign cannot change, so the accept-or-reject decision is always safe even though the value is 9.1% off. Only the partial shift is dangerous, and the partial shift is the one that happens.

The third mistake in this family has nothing to do with years. The upfront cost simply is not in the table, because it lives in a different system, or because someone filtered on year > 0 to drop what looked like an empty row.

SELECT ROUND(SUM(cash_flow / POWER(1 + 0.10, year)), 0)
FROM project_cash_flows
WHERE project = 'Line Expansion' AND year > 0;      -- 637236

$637,236, against a real NPV of $137,236. Overstated by exactly the $500,000 that was left out, and there is nothing in the number itself to say so. That is a present value, not a net present value, and the missing word is the entire cost of the project.

Picture your own company's project table. Where does the upfront cost live, and is it a row in the same table as the returns? If it lives in a capital budget somewhere else, every NPV anyone runs on that table is a gross present value wearing the wrong name.

6. The rate is the assumption, and IRR is the other way round

Same five cash flows, same $500,000 cost. Guess what rate would make this project not worth doing.

Just under 20%. Here is the same project at seven different required returns, with nothing else changed.

One project, one set of cash flows, and the verdict flips between 15% and 20%. Every row is arithmetically perfect. The rate is not something the data tells you; it is something you choose and then have to defend. An NPV presented without the rate beside it is not a result, it is half of one.

Where the rate comes from, in practice. It is the return the company requires on money it puts at risk, usually its weighted average cost of capital, sometimes a hurdle rate set by the finance team, sometimes the return available on a comparable investment. Riskier projects get a higher rate, which is how risk enters the arithmetic: a higher rate punishes distant cash flows hardest, so speculative projects with far-off payoffs have to be much bigger to clear.

The rate that makes NPV exactly zero has a name: the internal rate of return, or IRR. On this project it is 19.80%. Read it as the project's own return, the break-even required rate. Above it the project is worth doing; below it, not.

IRR is not a SQL calculation. There is no closed-form solution, so it is found by trying rates until NPV lands on zero, which needs iteration that plain SQL does not do. That is why the practical division of labour is: compute NPV in SQL at the rate your company uses, and hand IRR to a spreadsheet or a Python script. If you want an approximate IRR without leaving SQL, run the NPV query at a handful of rates as above and read off where the sign changes. Between 15% and 20% here, and 19.80% when solved properly.

Two more numbers worth having, both from the same project. Payback period, the point where the undiscounted cash adds back up to the cost, is 3.11 years. Discounted payback, the same question in today's dollars, is 3.84 years. Payback is easy to explain and it ignores everything that happens after it lands, which is why it is a useful second number and a poor first one.

7. The full before and after

Same project, two ways of putting it in front of a decision maker.

Before

"The line expansion costs $500,000 and returns $850,000 over five years, a 70% return. Recommend approval." Nothing there is false. The reader has no way to know that the $850,000 arrives slowly, that the return is 70% spread over five years rather than 70% a year, or that the same project loses money if the company's required return is 20%. And the single number carries no rate, so nobody in the room can disagree with it usefully.

After

Same project, four extra rows, and the whole conversation changes. The rate is on the page, so it can be argued with. Two other rates are on the page, so the reader can see how much room there is before the answer changes. And the recommendation now has a shape: fund it if our required return is below about 19.8%, and the margin gets thin above 15%. That is a sentence a finance director can either agree with or correct, which is the most useful thing an analysis can be.

8. Edge cases NPV cannot see

NPV comes out at exactly zero. Accept or reject?

Zero means it earns precisely the required return. It is not a failure and it is not a win. The project would return exactly what the money would have earned elsewhere, so on the numbers alone it is a coin toss, and the decision moves to everything the numbers left out: strategy, risk, what else the money could do, what the team learns by doing it.

Cash flow is not profit. NPV wants cash moving in and out, not accounting profit. Depreciation is a real expense on the income statement and no cash leaves the building, so it does not belong in a cash-flow row. If someone hands you a project's projected profit and asks for an NPV, the first job is converting it back to cash.

The end of the model is not the end of the project. A production line that still works in year 6 has value that a five-year model gives away for nothing. Either extend the model or add a terminal value in the final year. Whichever you do, say so, because a five-year NPV on a fifteen-year asset understates it badly.

The cash flows are forecasts, and NPV reports to the nearest dollar. $137,236 has six digits of apparent precision built on estimates that could be 20% out either way. Presenting NPV to the dollar invites a confidence nobody has earned. Round to the nearest thousand in the summary, keep the full number in the working, and show at least one alternative rate so the range is visible.

Years are not always the right period. If cash arrives monthly, discount monthly, and the rate has to be converted first. A 10% annual rate is not 10 divided by 12 per month; it is 1.10 raised to the power of one twelfth, minus 1, which is about 0.797% a month. Mixing an annual rate with monthly periods is a large error dressed as a small one.

Sign conventions have to be consistent. Every model needs one rule: costs negative, inflows positive. A cost stored as a positive number in a column the query then adds up will not fail, it will just tell you the project is wonderful.