Home › Templates › Investment tracker
Investment Tracker Spreadsheet
A free investment tracker that brings all your accounts into one file: log contributions and withdrawals with dates, list what each account holds, and see the value, gain, simple return and XIRR per account, CAGR over time, and allocation by account and by asset class with charts.
Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up, no email, no price feed to break. Formulas are SUMIFS, INDEX, COUNT and XIRR.
| Account | Type | Contributions | Withdrawals | Value | Gain | Simple return | XIRR |
|---|---|---|---|---|---|---|---|
| Work 401(k) | 401(k) | $17,000.00 | $0.00 | $19,030.00 | $2,030.00 | 11.94% | 8.25% |
| Roth IRA | Roth IRA | $20,500.00 | $0.00 | $22,890.00 | $2,390.00 | 11.66% | 6.44% |
| Brokerage | Brokerage | $12,000.00 | $3,000.00 | $11,184.00 | $2,184.00 | 18.20% | 7.44% |
| Total | $49,500.00 | $3,000.00 | $53,104.00 | $6,604.00 | 13.34% | 7.25% |
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, colour key, what each return means, limits, disclaimer |
| Summary | Valuation date, up to 8 accounts with a type label (401(k), 403(b), IRA, Roth IRA, Brokerage, ISA, SIPP, HSA, 529, Pension, Other), optional target % per asset class | Contributions, withdrawals, net contributed, current value, gain, simple return, XIRR, first cash-flow date and % of portfolio per account and in total; value, % and gap to target per asset class; two checks; a pie chart by account and a bar chart actual vs target |
| Holdings | Account (dropdown), holding, asset class (dropdown), units and price, or a value. 200 rows. | Current value and % of the whole portfolio |
| Cash flows | Date, account, Contribution or Withdrawal, amount, note. 500 rows. | Signed cash flow for XIRR (money in negative, money out positive) |
| Growth | Snapshot dates and total value (for example each year-end). 30 rows. | Flows between snapshots, gain and return per period, growth of $1, net contributed to date, value CAGR, return CAGR, total return, a line chart |
| Calc | Nothing | The dated cash-flow table that XIRR reads, one column per account |
| Example tabs | Already filled | 3 fictional accounts, 40 cash flows, 8 holdings, 6 snapshots |
How to use it
- On Summary, type each account name once and pick its type.
- On Cash flows, log every deposit as a Contribution and every withdrawal as a Withdrawal, with the date and a positive amount. Buys and sells inside an account are not cash flows.
- Already have money invested? Log each account's value on your start date as a Contribution on that date.
- On Holdings, list what each account holds: units × price, or just a value (for cash). Pick an asset class.
- Back on Summary, type the valuation date: the date your holdings values are from. Returns and allocation update.
- Check that both "not listed" checks show $0.00. If not, an account name is spelled two ways.
- Optional: on Growth, add the total value at each year-end to see returns per period and CAGR.
Simple return, XIRR and CAGR: which one to read
When you add money over time, the balance grows from deposits as well as from investment returns. The three figures handle that differently.
| Measure | What it answers | Example result |
|---|---|---|
| Simple return | Gain ÷ total contributions. Ignores when the money went in. | 13.34% |
| XIRR (money-weighted) | The annual rate that makes every dated deposit, withdrawal and today's value net to zero. | 7.25% a year |
| Return CAGR | Annualized rate from chaining period returns, so deposits do not count as growth. | 6.60% a year |
| Value CAGR | How fast the balance grew, deposits included. Not a return. | 52.01% a year |
Microsoft describes XIRR as returning "the internal rate of return for a schedule of cash flows that is not necessarily periodic." Payments "are discounted based on a 365-day year", and XIRR "expects at least one positive cash flow and one negative cash flow; otherwise, XIRR returns the #NUM! error value." Google Sheets has the same function for "potentially irregularly spaced cash flows", and so does LibreOffice Calc, where "The first pair of dates defines the start of the payment plan."
That last rule is why the template feeds XIRR from the Calc tab instead of the raw log: row 4 holds the earliest date with a zero amount, blank log rows become zeros, and the last row holds today's value. You can type cash flows in any order and leave gaps, and XIRR still works in all three apps.
For a single start and end value with no deposits in between, the CAGR calculator gives the same answer as the Growth tab.
Allocation by account and asset class
The SEC's Beginners' Guide to Asset Allocation, Diversification, and Rebalancing on Investor.gov says asset allocation "involves dividing an investment portfolio among different asset categories, such as stocks, bonds, and cash", and that "There is no single asset allocation model that is right for every financial goal." It describes rebalancing as "bringing your portfolio back to your original asset allocation mix." The Summary tab shows each asset class against the target you type, in % and in dollars, so you can see how far the mix has drifted. Choosing the targets is up to you.
The formulas, explained
Exact formulas from the file (first data row shown).
| Field | Formula |
|---|---|
| Cash flow for XIRR (Cash flows F) | =IF(OR(D5="",C5=""),"",IF(C5="Withdrawal",D5,-D5)) |
| Contributions per account (Summary C) | =IF($A7="","",SUMIFS('Cash flows'!$D$5:$D$504,'Cash flows'!$B$5:$B$504,$A7,'Cash flows'!$C$5:$C$504,"Contribution")) |
| Current value per account (Summary F) | =IF($A7="","",SUMIFS(Holdings!$G$5:$G$204,Holdings!$A$5:$A$204,$A7)) |
| Simple return (Summary H) | =IF(OR($A7="",N(C7)=0),"",G7/C7) where G = value − net contributed |
| XIRR per account (Summary I) | =IF(OR($A7="",N($B$3)=0,N(C7)=0),"",IFERROR(XIRR(Calc!D$4:D$505,Calc!$A$4:$A$505),"")) |
| Calc: one account's flow on a log row | =IF(AND(D$3<>"",ISNUMBER('Cash flows'!$A5),'Cash flows'!$B5=D$3,ISNUMBER('Cash flows'!$F5)),'Cash flows'!$F5,0) |
| Holding value (Holdings G) | =IF(AND(N(D5)<>0,N(E5)<>0),D5*E5,IF(N(F5)<>0,F5,"")) |
| Period return (Growth G) | =IF(F8="","",IF(N(B7)+E8/2<=0,"",F8/(B7+E8/2))) (Modified Dietz: flows counted at mid-period) |
| Return CAGR (Growth L8) | =IF(OR(COUNT($A$7:$A$36)<2,INDEX($H$7:$H$36,COUNT($A$7:$A$36))=""),"",INDEX($H$7:$H$36,COUNT($A$7:$A$36))^(1/L6)-1) |
Worked example from the sample data
Per account
- Work 401(k): $500 on the 15th of every month from January 2024 to October 2026, $17,000 in total. Value $19,030.00, gain $2,030.00, simple return 11.94%, XIRR 8.25%. The XIRR is lower than you might expect from the simple return because it is a yearly rate, and with monthly deposits the average dollar has been invested for under a year and a half.
- Roth IRA: $6,500 in January 2024 and $7,000 in January 2025 and 2026. Value $22,890.00, simple return 11.66%, XIRR 6.44%.
- Brokerage: $10,000 in March 2024, $2,000 in June 2025, and a $3,000 withdrawal in April 2026. Value $11,184.00, so the gain is $11,184.00 − ($12,000 − $3,000) = $2,184.00, simple return 18.20%, XIRR 7.44%.
Growth over time
| Snapshot | Value | Net flows since last | Gain in period | Period return | Growth of $1 |
|---|---|---|---|---|---|
| 30 Jun 2024 | $20,140.00 | 1.0000 | |||
| 31 Dec 2024 | $23,610.00 | $3,000.00 | $470.00 | 2.17% | 1.0217 |
| 30 Jun 2025 | $35,980.00 | $12,000.00 | $370.00 | 1.25% | 1.0345 |
| 31 Dec 2025 | $40,250.00 | $3,000.00 | $1,270.00 | 3.39% | 1.0695 |
| 30 Jun 2026 | $49,120.00 | $7,000.00 | $1,870.00 | 4.27% | 1.1153 |
| 23 Oct 2026 | $53,104.00 | $2,000.00 | $1,984.00 | 3.96% | 1.1594 |
Over 2.32 years the balance went from $20,140 to $53,104: a value CAGR of 52.01%. Most of that is new money. Chaining the period returns gives a total return of 15.94%, or a return CAGR of 6.60% a year. The portfolio XIRR (7.25%) differs because it starts at the first deposit in January 2024, not at the first snapshot, and weights each period by how much money was invested in it.
Common mistakes
- Logging buys and sells as cash flows. Only money moving into or out of an account counts. A fund purchase inside the account changes holdings, not contributions.
- Counting reinvested dividends as contributions. They are part of the return. They show up in the value, not in the log.
- Forgetting the valuation date. XIRR uses it as the date of today's value. An old date with new values overstates the return.
- Reading value growth as return. A balance that doubled because you doubled your deposits did not earn 100%.
- Mixing currencies. Convert everything to one currency before you type it.
- Spelling an account two ways. "Roth IRA" and "Roth-IRA" are different to SUMIFS. Use the dropdowns and watch the checks.
FAQ
How do I track my investments in a spreadsheet?
Keep three lists. A cash-flow log with every deposit and withdrawal and its date, a holdings list with what each account holds today, and a summary per account that adds them up with SUMIFS. Then compare today's value with the money you put in. The free template on this page has all three, plus XIRR and allocation charts.
How do you calculate XIRR for an investment portfolio?
List each contribution as a negative amount and each withdrawal as a positive amount, with their dates. Add today's portfolio value as a final positive amount on today's date. Then use =XIRR(amounts, dates). In the example, $49,500 in and $3,000 out over almost three years, with $53,104.00 today, gives an XIRR of 7.25% a year.
What is the difference between XIRR and CAGR?
CAGR compares a start value and an end value and assumes no money was added or taken out in between. XIRR takes the date and size of every deposit and withdrawal into account. In the example, the balance grew at a CAGR of 52.0% a year, mostly from new deposits; the return-based CAGR is 6.60% and the XIRR 7.25%.
Does XIRR work in Google Sheets?
Yes. XIRR exists in Google Sheets, Microsoft Excel and LibreOffice Calc with the same arguments: values, dates and an optional guess. The template's XIRR results were recalculated in LibreOffice and match an independent calculation.
What is a good simple return vs XIRR?
They answer different questions. Simple return is total gain ÷ total contributions (13.34% in the example) and ignores time. XIRR is an annual rate, so it is the one to compare with other annual figures. Neither is good or bad on its own, and past returns do not predict future ones.
Can I track a 401(k), IRA and ISA in one spreadsheet?
Yes, as labels. Type each account once and pick a type such as 401(k), IRA, Roth IRA, Brokerage, ISA or SIPP. The sheet does not apply any tax or contribution-limit rules, and it assumes one currency for the whole file.
Related tools and templates
- CAGR calculatorAnnual growth from start to end value
- Stock portfolio trackerTrades, cost basis, P/L per stock
- Net worth trackerAssets, debts and net worth over time
- DCA trackerRecurring buys and average cost
- Dividend trackerIncome, yield on cost, payments
- Compound interest calculatorGrowth with regular deposits
- Investment policy statementWrite down your targets and rules
- All templatesFree spreadsheets
Educational template only, not financial, tax or investment advice. Past returns do not predict future returns. Account types are labels; no tax rules are applied. Example data is fictional: the accounts, FUND-INTL, FUND-US, FUND-RE, FUND-X, COIN-B and all amounts and prices are made up. Quotes from Microsoft Support, Google Docs Editors Help, LibreOffice Help and Investor.gov were retrieved on 27 October 2026.