In Excel, use PV to find the value today of a fixed series of equal payments, and use NPV to discount a series of cash flows that occur at regular intervals. A key detail: Excel’s NPV treats the first value you supply as a cash flow at the end of period 1, so an initial investment made today must be added separately.
What PV and NPV mean
Present value (PV) expresses future money in today’s terms using a discount rate. For example, receiving $300 at the end of each of the next four years is not equivalent to receiving $1,200 today: money available today could earn a return, and future payments carry timing and uncertainty. Discounting converts those future amounts to a comparable value today.
Net present value (NPV) applies the same idea to a project’s cash flows, then accounts for the project’s cost. In ordinary business use, the result is the present value of the expected future inflows and outflows, including the initial investment. A positive NPV means the discounted inflows exceed the costs entered; a negative NPV means they do not, given the cash flows and discount rate supplied. It is a calculation, not a guarantee that a project will perform as forecast.
Excel has separate functions because the cash-flow patterns differ. PV is built for regular, equal payments and an optional future balance. NPV discounts a list or range of cash flows at regular intervals, which may vary in amount.
How Excel’s PV function works
PV takes a rate per period, the number of periods, a recurring payment, an optional future value, and an optional payment-timing setting. The rate and period count must use the same time unit: for annual payments, use an annual rate and a number of years; for monthly payments, use a monthly rate and a number of months. The timing setting is 0 for payments at the end of each period and 1 for payments at the beginning. If omitted, Excel uses end-of-period timing.
Suppose you expect to receive $300 at the end of each year for four years and use a 10% annual discount rate. With those assumptions, the present value is about $950.96. Entering the payment as negative makes Excel return a positive present value, following Excel’s cash-flow sign convention: money paid and money received should generally have opposite signs.
With the annual rate in cell B1, the number of years in B2, and the annual payment entered as a negative amount in B3, this formula returns approximately $950.96: =PV(B1,B2,B3,0,0). The final zero specifies end-of-year payments, and the zero before it says there is no additional future balance. The result is the value today of the four payments, not their undiscounted total of $1,200.
If the same four $300 payments arrive at the beginning of each year instead, the present value is about $1,046.06. With the same cell layout and a beginning-of-period timing argument, use =PV(B1,B2,B3,0,1). Each payment arrives one period earlier than in the first example, so each is discounted for less time.
How Excel’s NPV function works
NPV discounts values that occur at regular intervals, such as yearly project cash flows. Its rate is the discount rate for each interval. The values can be different from period to period and can include negative amounts, but the first value supplied is assumed to occur one full period after the valuation date. The function does not automatically treat the first value as a cash flow happening today.
Consider a project that costs $1,000 today and is expected to return $300 at the end of each of the next four years. At a 10% annual discount rate, those four future receipts have a present value of about $950.96. Subtracting the $1,000 cost gives an NPV of about negative $49.04.
With the discount rate in B1, the initial outlay of -$1,000 in B2, and the four $300 year-end receipts in B3:B6, enter =NPV(B1,B3:B6)+B2. The result is approximately -$49.04. The separate addition is essential: the function discounts B3:B6 as years 1 through 4, while B2 is already a value at time zero and should not be discounted.
For a project with several future costs and receipts, place each period’s net cash flow in the appropriate cell, keeping the initial time-zero amount outside the range passed to NPV. A period’s net flow can be positive or negative. Keep periods in chronological order and do not leave out a period if doing so would make later cash flows appear to occur sooner.
Choosing between PV and NPV
Choose PV when the question is about a level stream of payments: the value today of loan payments, an annuity, or recurring savings withdrawals. It can also include a future value, such as a remaining balance at the end of the term. The sign of the result depends on the signs used for the payments and future value.
Choose NPV when you have a series of cash flows that may differ in size, such as a project’s setup cost, operating costs, and changing annual revenue. Excel does not require equal cash-flow amounts; it requires the values to represent consecutive, equally spaced periods. For cash flows on irregular dates, consider XNPV instead, which discounts using the dates provided.
- Equal payments at regular intervals:
PVis usually the direct fit. - Different cash flows at regular intervals: use
NPV, adding any time-zero cash flow separately. - Cash flows on actual, irregular dates: use
XNPVand a corresponding date range.
These functions answer related but different questions. The number returned by PV is not automatically a project’s NPV: it may represent only the value of a payment stream. To assess a project, compare the discounted flows with all relevant costs and make sure the initial investment is included exactly once.
Rates, periods, and cash-flow timing
Rate-period consistency is one of the most important checks. If cash flows are monthly, a quoted annual rate cannot be entered unchanged as though it were a monthly rate. For a nominal annual rate divided into monthly periods, the usual periodic rate is the annual rate divided by 12; whether that is the appropriate rate depends on how the rate is quoted and compounded. For an effective annual rate, converting to an equivalent monthly rate is different. Confirm the rate convention used in the underlying terms rather than assuming the two are interchangeable.
The number of periods must also match the cash-flow schedule. A four-year contract with monthly payments has 48 payment periods, not four. If the payment occurs at the start of each month, use beginning-of-period timing in PV; for payments at month-end, use end-of-period timing. The same timing logic matters when arranging cash flows for NPV: its first value is the first interval’s cash flow, not a payment at the starting date.
At a zero discount rate, discounting does not change the amounts: the present value of equal payments is simply their total, assuming no separate future value. This is a useful conceptual check. At positive rates, later payments should contribute less to present value than the same amount received sooner.
Common Excel PV and NPV mistakes
- Including the initial cost inside NPV’s range: Excel then treats it as a period-one amount and discounts it. Keep a time-zero investment outside the range and add it separately.
- Forgetting the initial cost: Discounting future receipts alone gives their present value, not the project’s net present value. Include the cost once, with the correct sign.
- Using inconsistent signs: If every payment and receipt has the same sign, the result may have an unexpected sign. Represent cash paid out and cash received with opposite signs where appropriate.
- Mixing annual and monthly inputs: A yearly rate paired with a monthly period count, or a monthly rate paired with yearly periods, gives a misleading answer.
- Using NPV for uneven dates: Regular periods are assumed even if actual dates have different gaps. For irregular dates, use a date-based method such as
XNPV. - Confusing a formula result with a forecast: The functions discount the values entered; they do not verify that projected revenue, costs, or rates are realistic.
When a result looks wrong, inspect the signs first, then check the payment dates, rate unit, number of periods, and whether an initial cash flow has been counted once. Formatting a cell as currency changes its display, not its underlying calculation.
Using the results responsibly
PV and NPV are only as useful as their assumptions. A small change in the discount rate can materially affect long-duration cash flows because distant amounts are discounted for more periods. Forecasts that are uncertain can also make a single NPV seem more precise than the underlying information warrants. It can be useful to compare results under several plausible rates or cash-flow scenarios, clearly labeling each scenario rather than presenting one estimate as certain.
For loans, investments, or business decisions, the spreadsheet result is not financial, tax, investment, or legal advice. Check actual loan terms and the applicable rate convention with the lender, and consult a qualified professional when a decision requires personal or regulated advice. A workbook should also document its assumptions so another reader can tell whether amounts are annual or monthly, when payments occur, and which rate was used.
Frequently Asked Questions
Why does Excel NPV give a different answer than my hand calculation?
Check whether your hand calculation includes the initial investment at time zero. Excel’s NPV discounts the first supplied value as a period-one cash flow. Add a time-zero amount separately, and check that the rate and cash-flow intervals match.
Does Excel NPV include the initial investment?
Not automatically. If the initial investment occurs today, place it outside the values passed to NPV and add it to the function result. Use a negative amount for an outflow when using the common project cash-flow convention.
What is the difference between PV and NPV in Excel?PV calculates the present value of regular equal payments and an optional future amount. NPV discounts a sequence of cash flows at regular intervals; to get a project’s net value, include any initial time-zero cash flow separately.
Should I use PV or NPV for loan payments?
For a regular loan payment stream, PV can calculate the present value of equal payments when the rate and period units match. Actual loans can include fees, variable rates, or other terms, so compare the spreadsheet assumptions with the lender’s repayment schedule.
What should I use for cash flows on different dates?
Use XNPV when cash flows occur on irregular dates, supplying the cash-flow values and their corresponding dates. This differs from NPV, which assumes evenly spaced periods. Check the date range and rate assumptions carefully.
Related
- Excel Not Updating Links From Other Workbooks? Fixes
- Excel Not Keeping Formatting? Causes and Fixes
- Excel Not Blank Conditional Formatting: How to Set It Up
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Joining Two Text Columns: Formulas and ExamplesTo join two text columns in Excel, enter a formula such as =A2&" "&B2 in a third column,…
- Excel Compare Two Sheets: Methods and FormulasTo compare matching cells on two sheets in the same workbook, enter ='Sheet1'!A1='Sheet2'!A1 in a blank cell; Excel…
- Python pandas Excel Functions: Read and Write FilesPython pandas handles Excel files mainly with read_excel() to load workbook data into a DataFrame and DataFrame.to_excel() to…
- IFS Excel Formula Examples: Syntax, Rules, and TipsExcel’s IFS function checks conditions in order and returns the value linked to the first condition that is…
- Count Unique Strings in Excel: Formulas and ExamplesTo count distinct, nonblank text strings in Excel for Microsoft 365, put your data in A2:A100 and use…
- What-If Analysis in Excel: Scenarios, Goal Seek & MoreIn Excel for Microsoft 365 on Windows, what-if analysis helps you see how changing input values affects a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.