Skip to main content
SheetTutor.
SheetTutorLearn Excel by playBack to task

Evaluate dated cash flows using actual day intervals

Your goal: Keep date-value pairs aligned and use 365-day discounting for an irregular cash-flow schedule.

Full 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.

Actual days rather than equal row intervalsChoose the dated valuation origin0 cells selected
Keep each A/B date-value pair and same-date receipts. A2 is the valuation origin; D/E labels the annual discount. Calculated results appear after your formulas pass.Scroll the worksheet horizontally to inspect later source columns.
Click or drag source cells; selected cells change color Drag from selected cells to deselect.
#
1
2
3
4
5
6
7
8
9
Step 1 of 4

Choose the dated valuation origin

Select the source cells in the worksheet, then complete the choices below.

Rules for this step

Select the earliest initial cost and its date. This exercise measures every elapsed-day offset from A2.

0 cells selected