Financial
Discount irregular dated cash flows on a 365-day basis
Discount cash flows arriving on exact irregular calendar dates using SUM(CashFlows / ((1 + Rate) ^ ((Dates - StartDate) / 365))).
Use elapsed days for irregular cash-flow timing
NPV uses equally spaced periods and a discount rate per period; those periods are not necessarily years. Its first supplied cash flow is discounted by one period. This example instead discounts from the date in A2.
For valid whole-day dates and an annual rate greater than -100%, SUM(B2:B5/((1+D2)^((A2:A5-A2)/365))) applies the 365-day convention used by XNPV. The initial outlay is included at day zero. Round the total, rather than each intermediate present value.
Use matching numeric cash-flow and date ranges, with A2 as the earliest date. This algebraic expression does not reproduce all XNPV input-validation behavior. It is a valuation example, not an investment recommendation; the appropriate discount rate depends on the application.
| Cash Flow Date (A) | Elapsed Days | Cash Flow (B) | PV @ 10% Discount Rate |
|---|---|---|---|
| 2026-01-01 (Close) | 0 days | -$10,000 | -$10,000.00 |
| 2026-04-01 | 90 days | +$3,000 | +$2,930.15 |
| 2026-10-01 | 273 days | +$4,500 | +$4,190.54 |
| 2027-01-01 | 365 days | +$4,000 | +$3,636.36 (XNPV = +$757.06) |
Dated present-value sum, rounded to cents
Array subtraction (A2:A5 - A2) / 365 discounts every row back to the initial closing date A2.
=IF(D2<=-1,"Rate must exceed -100%",ROUND(SUM(B2:B5 / ((1 + D2) ^ ((A2:A5 - A2) / 365))), 2))Undiscounted net cash flow
At D2 = 0%, daily-discounted NPV collapses cleanly to SUM(B2:B5).
=SUM(B2:B5)Try the example
Use the original values or change them to test your understanding.