PV vs NPV in Excel: Which Function Should You Use?

Formulas & functions · Updated November 20, 2026

Use Excel’s PV function to find the present value of regular payments or a future balance; use NPV to discount a series of cash flows that occur at regular intervals. A key distinction: Excel’s NPV function treats its first cash flow as arriving at the end of period one, so a project’s initial investment at time zero is normally added separately.

The difference between PV and NPV

Both functions express future money in today’s terms by discounting it at a chosen rate. The difference is the kind of cash-flow pattern each is designed to handle. PV is built around a payment amount that stays constant from period to period, with an optional final balance. NPV takes a sequence of cash flows, which can vary in amount, and discounts each one according to its position in the sequence.

For example, a loan with the same payment every month is a natural PV problem: the payments are regular and level. A project expected to produce $300 in its first year, $500 in its second, and $700 in its third is a natural NPV problem because its cash flows vary.

In finance, the phrase net present value often means the present value of all project cash flows, including the initial investment. Excel’s NPV function does not automatically include a time-zero cash flow. To calculate a project’s net present value in that common sense, discount the later cash flows with NPV and add the initial outlay separately.

What Excel’s PV function calculates

PV calculates the value today of a set of equal payments made at regular intervals, together with an optional future value at the end. Its inputs cover the rate per period, number of periods, payment per period, optional future value, and optional payment timing. The last two inputs can be left out when the future balance is zero and payments occur at the end of each period.

Here is a simple example. Assume a rate of 10% per year, three annual payments of $400, and no additional amount at the end. With the rate in B1, the number of periods in B2, and the payment entered as a negative amount in B3, enter =PV(B1,B2,B3) in another cell. The result is approximately $994.74. This is the value today of three $400 payments discounted at 10%.

Excel uses a cash-flow sign convention: money paid out and money received generally have opposite signs. In that example, the payments are entered as -$400, so the present value returned is positive. If you enter the payment as positive instead, PV returns a negative result. That does not mean the calculation failed; it reflects the direction of the cash flow from the chosen point of view.

How Excel’s NPV function handles timing

NPV discounts a list of future cash flows at a constant rate per period. It assumes the first listed amount arrives one full period from now, the second arrives two periods from now, and so on. It is suitable when the cash flows are evenly spaced, such as annual estimates or monthly receipts.

Suppose a project costs $1,000 today and is expected to return $400 at the end of each of the next three years. Put the annual discount rate of 10% in B1, the initial investment of -$1,000 in B2, and the three future receipts of $400 in B3:B5. Enter =NPV(B1,B3:B5)+B2 in another cell. The result is about -$5.26.

The arithmetic is: $400 divided by 1.10, plus $400 divided by 1.10 squared, plus $400 divided by 1.10 cubed, then minus the $1,000 paid today. The three receipts have a combined present value of about $994.74, which is $5.26 less than the initial cost. A small negative result means the modeled receipts do not quite equal the outlay at the selected discount rate; it is not a forecast or a recommendation about whether to proceed.

If you put the initial investment inside the NPV range, Excel discounts it as though it arrives at the end of the first period. That shifts its timing and gives a different answer. This is the most important source of confusion when people compare Excel’s NPV result with a finance textbook or another calculator.

PV and NPV compared with the same cash flows

The three equal $400 receipts in the example can also be valued with PV, because they form a level-payment series. Using the same rate, period count, and negative payment, PV returns about $994.74. NPV applied to the three positive receipts also returns about $994.74. Add the time-zero $1,000 outlay to that amount and the project’s net present value is approximately -$5.26.

This comparison shows that the functions are not competing methods for exactly the same input pattern. PV is convenient when the payments are equal and you want the value of that payment stream. NPV is more flexible when the amounts change from period to period. For uneven amounts that are still spaced at regular intervals, NPV can discount the whole series directly.

Neither function decides which discount rate is appropriate. The rate is an assumption supplied to the worksheet, and changing it changes the result. In a practical model, keep the rate visible in a clearly labeled input cell rather than burying it inside a long formula. That makes the assumptions easier to review and change.

How to choose the right function

Choose based on the pattern and timing of the cash flows, not just on whether the question uses the words present value or net present value. Ask what is happening in each period and where the time-zero amount belongs.

  • Use PV for a fixed loan payment, a regular annuity, or a future balance supported by equal periodic payments.
  • Use NPV for cash flows that vary in amount but arrive at consistent intervals, such as one cash flow at each year-end.
  • Use NPV plus a separately entered initial amount when you are calculating a project’s net present value and the initial amount occurs today.
  • Use XNPV rather than NPV when cash-flow dates are irregular, such as receipts occurring on specific calendar dates rather than at consistent yearly or monthly intervals.
  • Check whether payments occur at the start or end of each period. That timing affects PV, and ordinary NPV assumes end-of-period cash flows.

A useful rule is to draw a short timeline before entering a formula. Mark today as period zero, then place each payment or receipt where it is expected. This catches timing mistakes that a syntactically valid formula cannot detect.

Rates, payment frequency, and payment timing

The rate and number of periods must use matching units. For annual payments, enter an annual rate and a count of years. For monthly payments, use a monthly rate and a count of months. For instance, a nominal annual rate of 6% divided by 12 gives a monthly rate of 0.5%; a 3-year monthly schedule has 36 periods. Whether a nominal rate should be divided by 12 or converted from an effective annual rate depends on the terms and assumptions of the calculation.

PV also has a payment-timing option. The default is payment at the end of each period, often called an ordinary annuity. Selecting beginning-of-period timing represents an annuity due, such as rent paid at the start of each month. Because each payment arrives earlier, its present value is generally higher than the present value of the same payments made at period-end.

NPV does not offer that same payment-timing switch. If a stream of regular cash flows occurs at the beginning rather than the end of each period, account for the shifted dates explicitly—for example, discount the first payment as occurring today and later payments at their actual period numbers. Do not assume that merely changing an input in an NPV formula changes its timing convention.

Common mistakes and how to avoid them

  • Including today’s investment in the NPV range: Excel discounts the first value as a period-one cash flow. Keep the initial amount outside the NPV range and add it afterward.
  • Mixing annual and monthly units: A monthly payment schedule needs a monthly rate and a period count in months. Using an annual rate with 36 monthly periods discounts the cash flows too heavily.
  • Entering cash flows with inconsistent signs: Decide whose perspective the worksheet represents. In a project model, an investment is often negative and later receipts positive. In a loan-payment calculation, the payment and amount received may have opposite signs.
  • Using NPV for irregular dates: NPV assumes equally spaced periods. When dates are irregular, use XNPV with the actual dates instead.
  • Expecting PV to value any list of changing payments directly: PV is for level payments plus an optional future balance. Use NPV for a changing sequence of regularly spaced flows.
  • Rounding intermediate results: Keep the underlying rate and cash-flow values at their appropriate precision, and apply display formatting for currency. Rounding each discounted amount before summing can create small differences.

Excel for Microsoft 365 on Windows uses the same formulas described here. The PV, NPV, and XNPV calculations are also available in Excel for Mac and Excel for the web; the formula logic is the same. The examples use commas between arguments, as in a typical English-language installation. Excel installations with different regional settings may use another list separator.

When XNPV is a better alternative

When cash flows occur on actual dates that are not evenly spaced, XNPV discounts them using the supplied dates rather than assuming one equal-length period between each entry. This is useful for schedules with irregular payment dates. The values and dates must correspond one-for-one, and the first date anchors the timing of the calculation. XNPV uses a 365-day year for its date-based discounting convention.

For an illustration, assume the rate is in B1, cash flows—including the initial investment—are in B2:B5, and their corresponding Excel dates are in C2:C5. Enter =XNPV(B1,B2:B5,C2:C5). Unlike the basic NPV example, the initial investment belongs in the values range because XNPV uses the actual dates to determine when each amount occurs. Check that the dates are stored as real Excel dates, not text that only looks like a date.

XNPV is not simply a more precise replacement for NPV in every situation. If your cash flows occur at regular monthly or annual intervals and your model is organized by those periods, NPV may be more straightforward. Choose the function whose timing assumptions match the real schedule.

Frequently Asked Questions

Does Excel NPV include the initial investment?
No. Excel NPV treats each value in its list as a future cash flow, beginning one period from now. To include an investment made today, add that amount separately to the NPV result.

Can PV and NPV return the same answer?
Yes. If the future cash flows are equal payments at regular intervals, PV and NPV can calculate the same present value when given the same rate and timing. PV is tailored to equal payments; NPV handles a sequence that can vary.

Why is my PV result negative?
Excel applies a cash-flow sign convention. If the payment or future value has the opposite sign from the cash amount you expect to receive, the returned present value can be negative. Review the signs and decide which cash flows are receipts and which are payments from your chosen perspective.

Should I use NPV or XNPV?
Use NPV when the cash flows are separated by consistent periods, such as one amount per year. Use XNPV when the cash flows occur on irregular dates and you want the date spacing reflected in the calculation.

Does Excel NPV calculate a project’s profitability?
It calculates a present-value result from the cash flows and rate you provide; it does not verify those assumptions or account for every business consideration. For actual borrowing, investment, tax, or other financial decisions, check the real terms with the lender or a qualified professional.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.