To calculate NPV in Excel, discount the future cash flows with NPV and add the initial cash flow separately; to calculate IRR, apply IRR to the full cash-flow series, including the initial investment. For example, with a $10,000 outflow today followed by three annual inflows of $3,000, $4,000, and $5,000, a 10% discount rate gives an NPV of about -$210.37, while the IRR is about 8.90%.
What NPV and IRR measure
Net present value (NPV) expresses a series of future cash flows in today’s dollars using a chosen discount rate. A positive NPV means the discounted inflows exceed the outflows in the model; a negative NPV means they fall short. The discount rate represents the required return or another rate appropriate to the analysis. Its choice can change the result, so it should be treated as an explicit assumption, not an automatic Excel setting.
Internal rate of return (IRR) is the rate that makes the NPV of a series of cash flows equal zero. It is reported as a percentage, which can make it easy to compare with a hurdle rate. However, NPV and IRR answer different questions: NPV estimates value in currency, while IRR estimates a break-even rate of return. Neither result is a forecast or a guarantee.
Set up the cash-flow worksheet
Use one row or column for each cash-flow period, and keep the sign convention consistent: money paid out is negative and money received is positive. Put the initial investment in period zero, then list later cash flows in chronological order. A simple annual example can be arranged like this:
- Cell A1: discount rate, 10%.
- Cell A2: initial cash flow, -10000.
- Cells A3:A5: later cash flows, 3000, 4000, and 5000.
Label each period and note whether the intervals are annual, quarterly, or monthly. Standard NPV and IRR functions assume equally spaced periods, so a column of dates is useful even when the intervals are regular. Make sure amounts use a consistent currency and perspective: for example, do not mix project cash flows with the financing cash flows used to fund the project.
Calculate NPV with Excel’s NPV function
Excel’s NPV function discounts the first value supplied as a cash flow occurring one full period from now, the next value two periods from now, and so on. It does not treat the first value in its range as a cash flow at time zero. That is why the initial investment is normally added outside the function.
With the 10% rate in A1, the initial outflow in A2, and the three year-end inflows in A3:A5, enter =NPV(A1,A3:A5)+A2 in another cell. The result is approximately -210.37. The calculation is 3000/1.1 + 4000/1.1^2 + 5000/1.1^3 – 10000, or about 9789.63 – 10000. A negative answer means these modeled cash flows do not reach the selected 10% discount rate.
A common error is to include the initial outflow inside the NPV range without adjusting for its timing. For this layout, =NPV(A1,A2:A5) discounts the initial outflow by one period as though it occurred a year from now, which does not match the stated timeline. If all cash flows truly occur at the end of regular periods and the initial one occurs today, use the separate-addition approach.
Calculate IRR from the full cash-flow series
Unlike NPV, IRR needs the entire series, including the time-zero amount. With the initial outflow in A2 and later annual cash flows in A3:A5, enter =IRR(A2:A5). Excel returns approximately 8.90% (about 8.903% before rounding). At that rate, the present value of the three inflows is approximately $10,000, offsetting the initial $10,000 outflow.
Excel uses an iterative calculation to find a rate that makes NPV zero. You can provide an optional starting estimate if the default estimate does not converge: with the same values in A2:A5 and a 10% starting estimate, enter =IRR(A2:A5,10%). For this example it returns the same IRR, approximately 8.903%. A starting estimate guides the search; it is not the rate Excel is instructed to use as the answer.
Use XNPV and XIRR for dated, uneven cash flows
When cash flows occur on specific dates that are not evenly spaced, use XNPV and XIRR rather than pretending every interval is the same length. Put the dates in A2:A5 as January 1, 2025; January 1, 2026; January 1, 2027; and January 1, 2028, and put -10000, 3000, 4000, and 5000 in B2:B5 respectively. With the 10% rate in B1, =XNPV(B1,B2:B5,A2:A5) returns approximately -210.37 because these dates are 365 days apart. The first date is the valuation date for the calculation.
Using those same dated cash flows, =XIRR(B2:B5,A2:A5) returns approximately 8.903%. The dates and values ranges must have the same number of entries, and the first date should correspond to the first cash flow. XNPV and XIRR use actual dates, making them useful for irregular schedules such as milestone payments. Check the timing convention and date inputs carefully: shifting a receipt by several months can materially change its discounted value.
Interpret the results and choose a measure
For a single project evaluated against a chosen required return, NPV directly shows the modeled value added or lost in currency at that rate. IRR can be helpful as a percentage summary, but comparing projects by IRR alone can be misleading. A small project may have a higher percentage return yet create less total value than a larger project. Projects can also differ in timing, duration, and risk, which a headline IRR does not fully communicate.
IRR is most straightforward when cash flows change sign once—for example, an initial investment followed only by receipts. If a series changes sign multiple times, it can have more than one IRR or no meaningful IRR. Even when Excel returns a number, inspect the cash-flow pattern and consider NPV at the relevant discount rate as a companion measure. In some unusual cash-flow patterns, IRR rankings may conflict with NPV rankings.
Common errors and troubleshooting
- Unexpectedly positive or negative NPV: Check the signs, discount rate, period order, and whether the initial outflow has been added separately. Confirm that the rate and cash flows use matching period units.
#NUM!from IRR: Check that the range includes at least one negative and one positive cash flow. If the cash flows change sign more than once, there may be multiple possible roots. You can try a different optional guess, but also assess whether IRR is an appropriate summary.- A rate that seems implausible: Verify that all values are actual cash flows, that the initial amount is included once, and that the spacing matches the function. An annual IRR is not directly comparable with a monthly rate without conversion.
- Numbers stored as text or missed cells: Confirm that the input cells contain numeric values and that the selected range includes every intended period. Check for omitted receipts, duplicate entries, or dates entered in the wrong order.
- Rounding differences: Keep full precision in the inputs and format the result cell for display rather than rounding each cash flow before calculation. Rounded figures may not add exactly to the displayed total.
Frequently Asked Questions
Do I include the initial investment in Excel NPV? Usually, yes, but add it separately when it occurs at time zero. Excel’s NPV discounts each value in its range as a future-period cash flow. For the example above, that means using =NPV(A1,A3:A5)+A2, not including A2 inside the discounted range.
Why does Excel IRR return a different answer from NPV? They do not return the same kind of result. NPV returns a currency amount at a rate you supply; IRR returns the rate that makes the NPV of the cash flows zero. If you calculate NPV using the unrounded IRR for the same regularly spaced cash flows, the result should be close to zero, subject to numerical precision.
Should I use IRR or XIRR? Use IRR when cash flows occur at regular, equal intervals and the period sequence is clear. Use XIRR when you have actual dates and the gaps between payments are uneven. If your dates are regular but the first period is not a standard interval, dated functions can also help represent the real timing.
Can IRR have more than one answer? Yes. When cash flows switch between negative and positive more than once, the equation can have multiple rates that make NPV zero. Excel’s result can depend on its starting estimate in such cases. Review the cash-flow pattern and do not assume one returned percentage is the only possible solution.
Is a positive NPV proof that an investment will succeed? No. It means the cash flows in the worksheet produce a positive result using the stated discount rate and assumptions. Actual outcomes can differ if costs, timing, receipts, or the required return change. For real financial decisions, verify the underlying terms and assumptions with a qualified professional where appropriate; this explanation is not financial, tax, investment, or legal advice.
Related
- Auto-Update Calendar Excel Template: Setup and Fixes
- Autofit Button in Excel: Where It Is and How to Use It
- Excel Audit Trail: What It Records and How to View It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMPRODUCT Excel: Calculate a Weighted AverageIn Excel, calculate a weighted average with =SUMPRODUCT(A2:A5,B2:B5)/SUM(B2:B5), assuming A2:A5 contains the values and B2:B5 contains their weights.…
- How to Count Unique Values in an Excel PivotTableTo count distinct values in an Excel PivotTable, create the PivotTable with Add this data to the Data…
- Excel SUMIFS Not Equal: Criteria and FixesTo sum values that do not match a criterion, use the not-equal operator <> in SUMIFS. For example,…
- Excel Formula to Fill Blank Cells in a List AboveTo fill blank cells with the value from the row above in Excel for Microsoft 365, select the…
- Sort an Excel Column Alphabetically Without Mixing RowsTo sort a column alphabetically in Excel for Microsoft 365 on Windows, click a cell in the column…
- Excel MAX IF: Find the Highest Number by ConditionTo find the largest number in Excel that meets a condition, use MAXIFS, such as =MAXIFS(C2:C7,B2:B7,"East"). If you…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.