NPV Formula Excel

What this does

NPV discounts a series of future cash flows to today's value and, done correctly, tells you whether a project creates value. The trap is in the name: unlike the textbook definition, Excel's NPV discounts every argument by at least one period, so including the initial investment inside the range discounts money you are spending today. The correct pattern is =NPV(rate, flows_from_period_1) + initial_outlay, with the outlay negative. The rate must match the period spacing, and the flows must be evenly spaced — if they are not, XNPV is the function. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. For “npv formula excel”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected formula change, and only then apply the same setup to the full sheet. When a formula is involved, keep the inputs labelled beside it, reference cells instead of typing values, and apply number formatting only after the result checks out. That is what makes a calculation you can defend to a CFO or an auditor useful in real work: repeatable, auditable, and not dependent on memory or luck.

A worked example

An investment of -100,000 today returning 30,000, 40,000, 45,000 and 35,000 over four years at 10 %: =NPV(10%, B3:B6) + B2 returns about 17,700. Putting the outlay inside the range, =NPV(10%, B2:B6), returns roughly 16,090 — the same project undervalued by discounting today's spend. NPV is the standard investment test, and the initial-outlay placement is the detail that separates a correct model from a plausible one. If there is any chance you will reuse this, drop it into a small template tab right now: a labelled input area on the left and the formula beside it, checked once against the tool above. Next time the same question comes up, the answer is a single paste away instead of a rebuild from memory.

In Google Sheets

If you are in Google Sheets rather than Excel, the good news is that the formula shown here is identical and the workflow barely changes — menus sit across the top instead of in a ribbon, and a few function names differ slightly, but anything you build here moves across with little or no rework. Keep this page bookmarked for the next time the same question comes up. Better still, rebuild the example once in your own sheet — doing it yourself, with the tool above to check against, is what turns a copied formula into a technique you own. Treat “npv formula excel” as a small building block rather than a chore. Once the inputs sit in their own cells and the formula reads from them, the same setup answers a dozen related questions with a tweak, and Excel keeps every dependent figure current as the data changes. The tool above is there so you can rehearse and verify before committing anything to a real workbook; the steps and worked example are there so the logic sticks. Get it right once and it stops costing you time — it starts saving it, every time the question comes back around.

Common mistakes

• Including the initial investment inside the NPV range, which discounts a payment made today and understates the project.
• Using a periodic rate with annual flows or vice versa.
• Applying NPV to irregularly dated flows, which XNPV handles properly.

Frequently asked questions