DEV Community

Peter
Peter

Posted on

Why XIRR Breaks When Your Angel Portfolio Hits 10+ Investments

#angel #investing #startup #portfolio

Why XIRR Breaks When Your Angel Portfolio Hits 10+ Investments

Excel's XIRR function works perfectly for your first few deals:

  • Investment: -$25,000 on 2024-01-15
  • Current value: $75,000 on 2026-04-10
  • XIRR: 44.2% annualized return

But as your portfolio grows to 10, 15, 25+ investments, something breaks. You can calculate XIRR for individual companies, but you can't get a portfolio-level XIRR. The cashflow schema that works for single investments becomes impossible at the portfolio level.

Here's why — and what to do about it.

How Most Angels Organize Their Spreadsheets (The Wrong Way)

Most angel tracking spreadsheets are organized by company — one row per investment:

Company A    $25K    01/15/24    $75K    3.0x MOIC    44.2% XIRR
Company B    $50K    03/10/24    $45K    0.9x MOIC    -23.1% XIRR  
Company C    $25K    06/20/24    $25K    1.0x MOIC    0.0% XIRR
Enter fullscreen mode Exit fullscreen mode

This structure makes it trivial to calculate XIRR for each individual investment using Excel's =XIRR(values, dates) function. But it makes portfolio-level XIRR impossible.

Why Portfolio XIRR Is Impossible in Company-Row Spreadsheets

XIRR requires a single timeline of ALL cash flows — investments, follow-ons, partial exits, and current values. But when you organize by company rows, your cash flows are scattered across different cells:

  • Company A cash flows: B2 (investment), F2 (current value)
  • Company B cash flows: B3 (investment), F3 (current value)
  • Company C cash flows: B4 (investment), F4 (current value)

You can't feed scattered cells to Excel's XIRR function. You need a unified cashflow timeline like this:

Date          Amount      Description
2024-01-15    -$25,000    Company A initial
2024-03-10    -$50,000    Company B initial
2024-06-20    -$25,000    Company C initial  
2024-12-01    -$25,000    Company A Series A follow-on
2025-08-15    +$15,000    Company B partial exit
2026-04-10    +$195,000   Current portfolio value
Enter fullscreen mode Exit fullscreen mode

Only then can XIRR calculate your true portfolio performance: =XIRR(Amount_Column, Date_Column).

Follow-Ons Break Everything

The problem gets worse when you make follow-on investments. Where do you put Company A's Series A follow-on in a company-row spreadsheet?

Option 1: New row for each investment

Company A (Seed)      $25K    01/15/24    $37.5K
Company A (Series A)  $25K    12/01/24    $37.5K
Company B             $50K    03/10/24    $45K
Enter fullscreen mode Exit fullscreen mode

Now you have duplicate companies, and calculating Company A's blended XIRR requires manual work.

Option 2: Additional columns

Company A    $25K (Seed)    $25K (Series A)    $75K    ???% XIRR
Enter fullscreen mode Exit fullscreen mode

How do you calculate XIRR when investments are in separate columns with different dates?

Option 3: Sum the investments, lose the timing

Company A    $50K    "Various"    $75K    ???% XIRR
Enter fullscreen mode Exit fullscreen mode

You've lost the investment timing, making XIRR meaningless.

The Right Way: Cashflow-First Organization

Professional portfolio managers use transaction-based tracking:

Date          Company    Type            Amount      Shares    Share Price
2024-01-15    Co A       Initial         -$25,000    25,000    $1.00
2024-03-10    Co B       Initial         -$50,000    20,000    $2.50
2024-06-20    Co C       Initial         -$25,000    50,000    $0.50
2024-12-01    Co A       Series A        -$25,000    12,500    $2.00
2025-08-15    Co B       Partial Exit    +$15,000    -6,000    $2.50
Enter fullscreen mode Exit fullscreen mode

From this transaction log, you can calculate:

  1. Portfolio XIRR: Feed all cash flows to XIRR function
  2. Individual company XIRR: Filter transactions by company
  3. Current ownership: Sum shares by company
  4. Cost basis tracking: For tax purposes
  5. Performance attribution: Which investments drive returns

When to Make the Switch

You should switch from spreadsheets to dedicated software when:

  • You hit 10+ investments (XIRR complexity threshold)
  • You start making follow-on investments (transaction complexity)
  • You have your first partial exit or SAFE conversion
  • You need to report performance to co-investors or family
  • You spend >2 hours/month updating your tracking

Remember: your angel portfolio is likely your third-largest asset after your house and 401k. It deserves better than broken Excel formulas.

The Bottom Line

XIRR isn't broken — your spreadsheet structure is. Company-row organization makes portfolio-level IRR calculations impossible. Transaction-based tracking solves this, but Excel becomes unwieldy at scale.

For portfolios under 5 investments, Excel works fine. Beyond that, you're fighting the tool instead of managing your portfolio.

Building AngelHub to solve exactly this problem. Free for up to 5 investments, designed around transaction-based portfolio management with automatic XIRR calculations.

Top comments (0)