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
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 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 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect