Time zero is discounted by accident
NPV begins with the first future period. Keep B2 outside NPV; include B2 inside IRR and include the complete date/value pairs inside XNPV and XIRR.
Evaluate a periodic project without discounting time zero
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter separate formulas in E6 and E7. B2 is the signed cost at time zero; B3:B9 are seven equally spaced end-period flows, including numeric zero periods. E2 is the discount per period. E6 is the project NPV at time zero. E7 is the unrounded IRR per period of the complete B2:B9 schedule. Retain signs and numeric zero periods; do not discount the initial cost or annualize an unspecified period.
Edit a cash flow or an assumption
Inputs stay numeric. Keep signed cents, real zero periods and full-precision results. Rates are decimals such as 0.08. Dates use YYYY-MM-DD and are stored as numeric dates. Applying one input validates the complete schedule first.
Applied value:
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: E6, E7. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Separate an initial cost, unequal end-period receipts and the yield that makes the complete schedule balance.
THE QUICK ANSWER01
Enter separate formulas in E6 and E7. B2 is the signed cost at time zero; B3:B9 are seven equally spaced end-period flows, including numeric zero periods. E2 is the discount per period. E6 is the project NPV at time zero. E7 is the unrounded IRR per period of the complete B2:B9 schedule. Retain signs and numeric zero periods; do not discount the initial cost or annualize an unspecified period.
E6
=B2+NPV($E$2,B3:B9)E7
=IRR(B2:B9)For the example data
E6 383.74609249798124E7 0.11913357215646292
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
Discounting asks a different question from solving a yield
NPV evaluates a supplied discount; IRR solves the rate at which the initial cost and discounted receipts balance. Changing E2 changes NPV, while the cash-flow IRR remains the same.
Zero periods have economic timing
A cost of -1,600 followed by six zero receipts and 2,600 at period seven is not a one-period schedule. Neither filtering out zeroes nor counting only nonzero receipts preserves this model.
Read the full step-by-step walkthrough
- Locate time zero
B2 occurs immediately. The first value inside NPV is B3, one period later. Add the signed initial cost outside NPV.
- Keep the complete timeline
A numeric zero is a real period with no net receipt; removing its row changes the timing. Keep B3:B9 in their period order.
- Solve for a break-even rate
E7 uses all eight signed flows, including time zero. These conventional sources have one unique root within the declared range. The result is per period, not automatically per year.
- Interpret zero and negative yields
In the zero-yield source the signed receipts exactly repay the initial cost. In the loss source, a negative IRR is a valid result. Do not replace a negative number with zero or treat it as a calculation error.
- Change the discount without changing the receipts
Change E2 and compare E6 with E7. The supplied discount changes NPV; the IRR is determined by the signed cash-flow schedule. A positive or negative NPV is conditional on that supplied discount, not a risk assessment.
Things to watch
A percentage is entered as a whole number
Rates are decimal fractions: 0.08 means eight percent per declared period, or per 365 days in the dated task. Keep full precision; number formatting does not change the calculation.
A rate is treated as a recommendation
These are original cash-flow models with supplied assumptions. A computed yield is not a ranking of projects with different timing, scale or risk. Taxes, fees and changing discount curves are outside the model.
Input domain needs review
Use eight numeric cent flows from -1,000,000 through 1,000,000. The first flow is a cost of at least 100. Periodic and dated IRR tasks require nonnegative later flows and at least one positive receipt, with their unique yield between -0.8 and 2. Discount is -0.25 through 0.5. MIRR admits later costs and uses finance/reinvestment rates from zero through 0.5. Dates are whole numeric serials from 2000 through 2099, the first is earliest, every later date is at least 30 days later and the schedule spans 365 through 3650 days. Arbitrary sign-changing IRR roots and invalid source types require separate rules.
About this example
Original retained cash-flow sources checked with real browser calculations and independent arithmetic. The dated task has an explicit mathematical fallback for the pinned browser XNPV negative-rate boundary. Native Excel/Sheets recalculation, editing and saving remain separate and unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.