NPV vs PV in Excel: Which Present Value Function

The short answer to NPV vs PV in Excel: use PV when every payment is the same amount, like a loan, a lease or a savings plan, and use NPV when the cash flows change from period to period, like a project's yearly returns. Both turn future money into today's money using a discount rate. This comparison is for finance students, analysts and anyone facing a finance Excel test who needs to pick the right function quickly.

NPV vs PV at a glance#

PV is built for regular, equal payments; NPV is built for a list of uneven cash flows.

Two terms are worth defining. The discount rate is the return you could get elsewhere, used to shrink future money to today's value. Present value is what a future amount is worth today at that rate.

How PV works#

PV returns today's value of a series of equal payments, or of a single amount in the future.

=PV(rate, nper, pmt, [fv], [type])

• rate is the interest rate per period, such as 10% a year or 5%/12 a month.
• nper is the number of periods.
• pmt is the payment each period. It must be the same every time.
• fv (optional) is a lump sum at the end. type (optional) is 0 for payments at the end of each period, 1 for the start.

Three examples:

=PV(10%,3,-1000)
=PV(5%/12,60,-500)
=PV(6%,10,0,-10000)

The first returns 2,486.85: three yearly payments of 1,000 are worth that today at 10%. The second returns 26,495.35: if you can pay 500 a month for five years at 5% a year, that is how much you can borrow. The third returns 5,583.95: 10,000 received in ten years is worth that today at 6%.

The payments are typed as negative numbers. Excel treats money paid and money received as opposite signs, so a negative payment gives a positive PV.

How NPV works#

NPV returns today's value of a list of cash flows, one per period, which can all be different.

=NPV(rate, value1, [value2], ...)

The values are usually a range, such as B3:B5. NPV assumes each value arrives at the end of its period, so the first value is discounted by one full period, the second by two, and so on.

That assumption causes the most common NPV mistake. An investment paid today should not be discounted at all, so it goes outside the function:

=NPV(B1,B3:B5)+B2

Here B2 holds the cost today as a negative number. The NPV function guide covers the function on its own in more detail.

The same cash flows with NPV and PV#

When the payments are equal, the two functions agree. Three yearly receipts of 1,000 at 10%:

=PV(10%,3,-1000)
=NPV(10%,1000,1000,1000)

Both return 2,486.85. PV is shorter because it only needs the payment once.

Now a project with uneven returns:

PV can't value this, because the payments differ. NPV can:

=NPV($B$1,B3:B5)+B2

The result is 88.15. The returns are worth 5,088.15 today, which is slightly more than the 5,000 cost, so the project adds value at 8%. Change B1 to 10% and the result becomes -105.18: at a higher required return, the same project no longer pays. This is why the rate sits in its own cell rather than inside the formula.

Why the rate reference is absolute#

The dollar signs in $B$1 make it an absolute reference: it stays pointing at B1 when you copy the formula. If you value several projects side by side and copy the formula across, the cash-flow range should move but the rate should not. Without the dollar signs, the copied formula would look for the rate in C1, D1 and so on, and return wrong results. The guides to relative vs absolute references and the dollar sign in Excel explain this, and you can press F4 on Windows or ⌘T on a Mac to add the dollar signs while editing.

When to use PV#

Use PV when the payment is the same every period. It fits:

• Loans: how much you can borrow for a given monthly payment.
• Annuities and pensions: today's value of a fixed income stream.
• Leases: the value of fixed lease payments.
• A single future amount: leave pmt as 0 and use fv, as in the 10,000 example.
• Bond pricing: equal coupons plus a face value at the end, using both pmt and fv.

Remember to match the rate to the period. For monthly payments, divide the annual rate by 12 and multiply the years by 12.

When to use NPV#

Use NPV when the cash flows differ or change sign. It fits:

• Project appraisal: an up-front cost followed by uneven returns.
• Comparing investments: the one with the higher NPV at the same rate adds more value.
• Business cases: yearly savings that grow or shrink over time.
• Any cash-flow table with one value per period, laid out in a row or column.

If a rate is reused across many formulas, a named cell such as DiscountRate can make formulas easier to read; see named ranges vs cell references.

Common mistakes with NPV and PV#

1. Putting the year-0 investment inside NPV. =NPV(B1,B2:B5) discounts the cost as if it were paid a year from now. With the data above, that returns 81.62 instead of 88.15.
2. Mixing yearly rates with monthly periods. =PV(5%,60,-500) treats 5% as a monthly rate. Use 5%/12.
3. Being surprised by a negative PV. It is the sign convention, not an error. Enter payments as negative, or wrap the result: =-PV(...).
4. Using PV for uneven payments. PV takes one payment amount. If the amounts differ, use NPV.
5. Forgetting to lock the rate cell. Copying =NPV(B1,B3:B5)+B2 across columns moves the rate reference. Use $B$1, or see how to copy a formula without changing references.
6. Using NPV for irregular dates. NPV assumes equal periods. For specific dates, use XNPV.

Our recommendation#

Look at the cash flows. If they are equal and regular, use PV: it is shorter and has options for a final lump sum and start-of-period payments. If they vary, or mix an up-front cost with later returns, use NPV, keep any year-0 amount outside the function, and put the rate in its own locked cell so you can test different rates. For a finance Excel test, be ready to explain both, including why PV comes back negative.

In short: PV for equal payments, NPV for uneven cash flows, and the year-0 cost outside NPV.

Practise it#

Try the exercise on this page: build an NPV formula with a locked rate and the year-0 cost added outside. Then practise absolute references in a live spreadsheet, so formulas like this copy correctly every time. When you're ready, the finance Excel mock test puts these skills under time pressure.