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 dated cash flows using actual day intervals
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 using all aligned pairs A2:B9, including the initial negative flow at the earliest date A2. E2 is an annual effective discount expressed per 365 days. E6 is the dated present value at A2; E7 is the unrounded XIRR per 365 days. Discount each flow by its actual whole-day difference from A2 divided by 365. Use XNPV for nonnegative discount rates; for negative discount rates the pinned browser engine requires explicit per-flow discounting with SUMPRODUCT. Keep duplicate-date receipts and reorder later rows only as complete date-value pairs.
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
Keep date-value pairs aligned and use 365-day discounting for an irregular cash-flow schedule.
THE QUICK ANSWER01
Enter separate formulas in E6 and E7 using all aligned pairs A2:B9, including the initial negative flow at the earliest date A2. E2 is an annual effective discount expressed per 365 days. E6 is the dated present value at A2; E7 is the unrounded XIRR per 365 days. Discount each flow by its actual whole-day difference from A2 divided by 365. Use XNPV for nonnegative discount rates; for negative discount rates the pinned browser engine requires explicit per-flow discounting with SUMPRODUCT. Keep duplicate-date receipts and reorder later rows only as complete date-value pairs.
E6
=IF($E$2<0,SUMPRODUCT(B2:B9/(1+$E$2)^((A2:A9-$A$2)/365)),XNPV($E$2,B2:B9,A2:A9))E7
=XIRR(B2:B9,A2:A9)For the example data
E6 869.0826526767546E7 0.24658479758692275
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
A date format does not turn a date into text
Each date remains a whole Excel serial. This course excludes time fractions, text dates and the 1900 leap-year compatibility boundary. A leap day changes elapsed days, not the fixed 365-day exponent denominator.
Order-independent pairs are different from periodic rows
Reordering later date-value pairs preserves XNPV and XIRR. Sorting only dates or substituting NPV/IRR changes the model. Keep the first, earliest date as the valuation anchor.
An implementation boundary is separate from the model
The nonnegative branch uses the real XNPV function. The negative branch computes the declared mathematical discount equation explicitly because the pinned browser XNPV returns #NUM!. The task still includes the original negative-discount source. Native Excel/Sheets behavior for this boundary has not been measured.
Read the full step-by-step walkthrough
- Keep the date and its value together
Dates in A are actual numeric serials formatted yyyy-mm-dd. B2 is the earliest cost; later pairs may be out of order, and same-day receipts remain distinct.
- Use actual elapsed days
XNPV includes all flows, including B2 at day zero. Leap-day intervals still divide by 365; equal row positions do not imply equal time.
- Solve the same dated equation
XIRR uses the same pairs and returns a rate per 365 days. The discount E2 is not a cash flow or a required solution guess.
- Keep a negative discount explicit
The pinned browser XNPV rejects negative rates. The solution branches to the stated sum of signed flows divided by (1 + rate) raised to elapsed days / 365. This is a transparent mathematical fallback, not a claim that native Excel rejects the same input.
- Reorder complete date-value pairs
The retained unsorted source rearranges later pairs while keeping the earliest first pair fixed. Both dated outputs stay the same. Sorting only A2:A9 would associate different dates with the amounts and change the model.
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.