Excel
All Top 50 Excel & Business Modeling Questions#44
Calculating Private Equity Returns with IRR and XIRR
MediumMorgan StanleyInterview Question #44
Asked at Morgan StanleyA private equity firm invests $5M in a startup on 2023-01-15. It invests an additional $2M on 2024-06-01, receives a dividend of $1M on 2025-03-20, and exits the entire position for $12M on 2026-09-04. Write the formula to calculate the exact annualized Internal Rate of Return (IRR).
Input Table: PE_CashFlows (A1:B5)
4 rows preview| Date (Col A) | Cash Flow ($) (Col B) |
|---|---|
| 2023-01-15 | -5000000 |
| 2024-06-01 | -2000000 |
| 2025-03-20 | 1000000 |
| 2026-09-04 | 12000000 |
Expected Output Structure1 rows
| Metric | Formula Output |
|---|---|
| Annualized Realized Return (XIRR) | 24.8% |
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 (Col A) | Cash Flow ($) (Col B) | ||||
| 2 | 2023-01-15 | -5000000 | Target [Enter Formula] | |||
| 3 | 2024-06-01 | -2000000 | ||||
| 4 | 2025-03-20 | 1000000 | ||||
| 5 | 2026-09-04 | 12000000 | ||||
| 6 | ||||||
| 7 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect