Excel
All Top 50 Excel & Business Modeling Questions#43
NPV vs XNPV: DCF Valuation Accuracy for Irregular Cash Flows
MediumGoldman SachsInterview Question #43
Asked at Goldman SachsAn analyst evaluates an acquisition with an initial cash outflow on 2026-03-15 of $1,000,000, followed by cash inflows on 2026-09-30 ($300k), 2027-04-15 ($500k), and 2028-12-31 ($700k) at a 10% discount rate. Explain why using `=NPV()` produces inaccurate results and why Wall Street mandates `=XNPV()`.
Input Table: ProjectCashFlows
4 rows preview| Date | Cash Flow ($) |
|---|---|
| 2026-03-15 | -1000000 |
| 2026-09-30 | 300000 |
| 2027-04-15 | 500000 |
| 2028-12-31 | 700000 |
Expected Output Structure2 rows
| Method | Valuation Result | Key Flaw / Accuracy |
|---|---|---|
| Excel NPV(10%, B3:B5) + B2 | $246,842 | INCORRECT: Assumes equal 365-day spacing between all cash flows |
| Excel XNPV(10%, B2:B5, A2:A5) | $282,149 | CORRECT: Discounts cash flows based on exact calendar day counts |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
E2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Date | Cash Flow ($) | ||||||||
| 2 | 2026-03-15 | -1000000 | Target [Enter Formula] | |||||||
| 3 | 2026-09-30 | 300000 | ||||||||
| 4 | 2027-04-15 | 500000 | ||||||||
| 5 | 2028-12-31 | 700000 | ||||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
This question demonstrates a technique that can't be graded as a single cell formula (it needs another sheet, a real PivotTable, or compares two methods side-by-side). Read the explanation below instead.