In Excel, use NPV to discount a series of periodic cash flows, but keep a time-zero investment outside the function and add it separately. Use PV to find the present value of a future amount or a stream of regular payments; the right choice depends on the cash-flow pattern.
What NPV and PV mean
Present value (PV) expresses money expected in the future as an equivalent amount today, using a discount rate. Net present value (NPV) compares the present value of future cash flows with the cash paid up front. Both calculations account for the idea that a dollar available today can be worth more than a dollar received later, but they answer different questions.
The terminology can be confusing because Excel’s NPV function does not automatically include a cash flow at time zero. In finance, a project’s net present value commonly includes the initial investment as well as later cash flows. Excel’s function discounts the values supplied as though the first one arrives at the end of period one. Therefore, a project calculation usually adds the initial investment separately.
For example, a project might require an initial outlay and then generate inflows over several years. NPV helps express those future inflows in today’s dollars and combine them with the outlay. PV is more directly suited to a single future payment or repeated, equal payments, such as the payment stream in a loan calculation.
How the Excel NPV function works
Excel’s NPV function applies one discount rate to a sequence of cash flows that occur at regular intervals. If the rate is annual, the values should generally represent annual periods; if the rate is monthly, each value should represent a month. The first value supplied is discounted by one period, the second by two periods, and so on.
For a project with an initial investment at time zero, put that amount in a separate cell from the later-period cash flows. With the discount rate in B1, the initial investment in B2, and the year-one onward cash flows in B3:B5, enter =NPV(B1,B3:B5)+B2. This is the common Excel pattern for calculating project NPV.
Enter the investment as a negative cash flow and receipts as positive cash flows. The function discounts B3:B5, while adding B2 brings the time-zero outflow into the total without discounting it. If the investment is instead entered inside the range passed to NPV, Excel treats it as a period-one value, which gives it the wrong timing. Adding it separately as well would also count it twice.
Worked NPV example with cash flows
Suppose a project costs $10,000 today and is expected to return $3,000 after year one, $4,000 after year two, and $5,000 after year three. For this example, put 10% in B1, -10000 in B2, and 3000, 4000, and 5000 in B3:B5. The values are illustrative; actual forecasts and discount rates depend on the situation.
With those cell entries, use =NPV(B1,B3:B5)+B2. The discounted inflows total about $9,789.63, and the result after including the $10,000 initial outflow is approximately -$210.37. The negative result means that, under these example cash flows and this chosen discount rate, the discounted inflows are less than the initial cost. A zero result means the discounted inflows and outflows balance; a positive result means the discounted inflows exceed the outflows.
That result is sensitive to the assumptions. A different discount rate, timing, or forecast can change it. NPV is a calculation, not a guarantee that expected cash flows will occur, and this illustration is not financial or investment advice. For a real decision, verify the cash-flow timing and relevant terms with appropriate financial records or a qualified professional.
How Excel PV differs
Excel’s PV function calculates the present value of regular payments and/or a future value, using a constant rate and number of periods. Its arguments let you specify the rate per period, total number of periods, payment per period, future value, and whether payments occur at the beginning or end of each period. In Excel, the default payment timing is the end of a period.
For a single $10,000 amount received in three years at an annual 10% rate, put 10% in B1, 3 in B2, 0 in B3, and -10000 in B4. With that layout, enter =PV(B1,B2,B3,B4). The result is approximately $7,513.15. The future value is negative here so that Excel returns the present value as positive; Excel’s financial functions follow a cash-flow sign convention in which money paid and money received normally have opposite signs.
When using PV for a payment stream, make sure the rate and number of periods match the payment schedule. For monthly payments, use a monthly rate and the total number of months, rather than an annual rate and a number of years. The optional timing argument distinguishes payments at the beginning from payments at the end of each period, which changes the answer.
Which function should you choose?
- Choose NPV when you have a sequence of cash flows that occur at regular intervals and want their discounted total. Keep any time-zero investment separate and add it to the function result.
- Choose PV when you want to value one future amount or a set of equal, regularly spaced payments at a given rate.
- Consider XNPV when cash flows occur on specific dates that are not evenly spaced. It discounts using the dates rather than assuming equal periods.
NPV is often convenient for project cash flows that change from period to period, because its values can be different in each period. PV is convenient for a fixed payment stream, but it is not a direct substitute for discounting a list of uneven cash amounts. If cash flows vary and their dates are irregular, XNPV is usually the more relevant Excel function to investigate. Choose based on when money actually changes hands, not just on which function name sounds closer to the calculation.
Rates, periods, and cash-flow timing
A discount rate must use the same time unit as the cash-flow periods. If annual cash flows are entered in the NPV range, use an annual rate. If cash flows are monthly, use a rate for each month and arrange the values in monthly order. Applying an annual rate to monthly cash flows as though each month were a year can substantially distort the result.
The ordinary NPV function assumes evenly spaced periods. Those periods do not have to be years: they might be months or quarters, provided the rate and values are consistent. However, entering a range of annual amounts does not tell Excel the exact dates within each year. For irregular dates, XNPV uses a rate, a set of cash-flow values, and corresponding dates. Its first date is the start date for the calculation, so a time-zero investment on that date is not discounted as a later-period flow.
Check whether each amount occurs at the beginning or end of a period. The standard NPV setup treats the first listed amount as arriving at the end of the first period. A cash flow received today belongs at time zero, not in the first position of the discounted range. For PV, the payment-timing option matters when equal payments happen at the beginning rather than the end of each period.
Common NPV and PV mistakes
- Including the initial outlay in the NPV range: Excel discounts the first supplied value by one period. Put a time-zero investment outside the range.
- Adding the initial outlay twice: If the initial amount is already included elsewhere in the total, do not add it again after the NPV result.
- Mixing annual and monthly inputs: Match the discount rate to the interval between cash flows, and use the corresponding number of periods.
- Reversing cash-flow signs: Use consistent signs for money paid and received. In
PV, sign choices affect whether the answer appears positive or negative, not the underlying value relationship. - Assuming NPV understands dates: Ordinary
NPVuses regular periods, not calendar dates. Use an appropriate date-based approach when the intervals vary.
It is also worth checking that the range contains the intended numbers in chronological order and that the rate is entered as a percentage or decimal correctly. In Excel, entering 10% or 0.10 represents a ten-percent rate; entering 10 represents a rate of 1,000% if the cell is treated as a plain number. A quick manual check of the first discounted cash flow can help catch a misplaced decimal or period.
When XNPV is a better fit
Cash flows do not always arrive once per year or at evenly spaced monthly intervals. A business may receive payments on specific dates, or a project may have expenses on different days. XNPV is designed for dated cash flows and is useful when the spacing between dates matters. Supply a discount rate, a set of values, and a matching set of Excel dates; the values and dates need to correspond position by position.
Unlike the usual project layout with NPV, the XNPV value list generally includes the initial cash flow on its start date. It uses that first date as the reference point and discounts later amounts according to their elapsed time. Make sure dates are real Excel date values rather than text that only looks like a date, and check that the earliest date is the intended starting point. This approach addresses timing differences; it does not remove the need to choose an appropriate rate or validate the cash-flow estimates.
Frequently Asked Questions
Does Excel NPV include the initial investment?
No. Excel discounts the first value supplied as a period-one cash flow. Put a time-zero investment outside the function’s range and add it to the result.
Can I use PV instead of NPV?
Sometimes, but they are built for different patterns. Use PV for a future amount or regular equal payments. For varying project cash flows, NPV is generally more direct; for irregular dates, consider XNPV.
Why is my PV result negative?
Excel’s financial functions use signs to represent money paid versus money received. If the payment and future-value inputs have the same sign, the result may appear with the opposite sign from what you expected. Review the direction of each cash flow and use opposite signs for a payment and the amount received.
Should the NPV rate be annual or monthly?
Use a rate for the same interval as the cash flows. Annual cash flows need an annual rate; monthly cash flows need a monthly rate. The period count and the rate must describe the same schedule.
When should I use XNPV rather than NPV?
Use XNPV when cash flows are tied to actual dates and the gaps between them are not equal. Use ordinary NPV when the cash flows occur at regular intervals and a consistent per-period rate is available.
Related
- Microsoft Excel Custom Number Formats: A Practical Guide
- Microsoft Excel Custom Number Format Codes Explained
- Microsoft Excel Contact List Template: Setup and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Counting Unique ValuesIn Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0) to count distinct nonblank values in A2:A100. It returns 0 if…
- How to Multiply in Excel: Formulas and ExamplesTo multiply in Excel, enter an equals sign, the first number or cell reference, an asterisk, and the…
- Excel Sentence Case Formula: One Sentence or Many?For a cell in A2, use =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))) to capitalize the first character and make the rest lowercase. This…
- Excel MID Function: Extract Text From the Middle of TextExcel’s MID function extracts a chosen number of characters from the middle of a text value. With the…
- Excel Formula Bot Free? Free Plans, Limits, and Safe UseYes—many Excel formula bots offer free access, but it is usually limited by usage, features, or account requirements.…
- Excel Formula Color: How to Use Conditional FormattingYou cannot make a standard Excel formula directly change a cell’s font or fill color. To color cells…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.