To put a 401k statement to Excel, copy two things from each statement: the account summary (beginning balance, your contributions, employer contributions, fees, gain or loss, ending balance) and the fund holdings (shares, price, value). Give each quarter one row in a roll-forward table. Each ending balance must equal the next quarter's beginning balance.
What on a 401k statement is worth putting in Excel?
A 401k statement has two parts that matter for a spreadsheet: an account summary for the period, and a list of the funds you hold at the end of it. The summary tells you how the balance changed. The holdings tell you where the money sits. Everything else is explanation, disclosures and contact details.
How often you get one depends on the plan. The U.S. Department of Labor's answers on retirement plans and ERISA for workers say a defined contribution plan such as a 401k must send benefit statements quarterly if participants direct their own investments, and annually otherwise. Most people who pick their own funds therefore get four statements a year, which is a good unit for a table.
The same page lists what such statements may include: the amounts you and your employer contributed, how your investments performed, and fees the plan charges to participants. Those three items are most of the summary block you will copy.
The summary block
Statement layouts vary by plan provider, but the summary usually holds:
- Beginning balance for the period.
- Contributions, often split into your money and your employer's.
- Fees charged to the account.
- Gain or loss, sometimes labelled investment earnings or change in market value.
- Withdrawals, loans or transfers, when there were any.
- Ending balance.
The detail you can usually skip
Many statements also print a transaction history with every payroll contribution on its own line. For a yearly view, the summary totals are enough. Keep the detail only if you need to match individual paychecks, for example to check that a raise changed your contribution when it should have.
Fund balances on a 401k statement: shares, price and value
Fund balances on a 401k statement are printed as holdings: fund name, shares or units owned, price per share on the statement date, and value. In Excel, put each in its own column and compute value as shares times price. If the computed values add up to the ending balance, the holdings table is complete.
Columns for holdings
| Column | What it holds |
|---|---|
| Statement date | The last day of the period |
| Fund | Name as printed |
| Shares | Units held, with all the decimals printed |
| Price | Price per share on the statement date |
| Value | =Shares*Price, rounded to cents |
| Source | Employee, Employer or Roth, if the statement splits it |
Keep every decimal place the statement prints for shares. Fund shares are often carried to three or more decimals, and dropping them changes the value by cents or dollars. Format the cells to show the decimals instead of rounding the stored number.
Stable value and money market lines
Some funds keep a price of 1.00 per unit, so shares and value are the same number. They still belong in the table. Without them, the holdings total falls short of the ending balance by exactly that fund's value, which is an easy gap to misread as a conversion error.
Employee and employer 401k contributions in separate columns
Employee and employer 401k contributions should sit in separate columns because they answer different questions. Your contributions came out of your pay. Employer contributions are the match or profit-sharing money, and they may be subject to a vesting schedule. If your plan has Roth and pre-tax sources, split those too.
Why the split matters
Three reasons come up often:
- Checking the match. If your employer matches half of what you put in up to a limit, the employer column should be about half the employee column each quarter. A quarter where it is not is worth asking about.
- Vesting. If you leave before you are fully vested, part of the employer money stays behind. Your statement may show a vested balance separately. Copy it into its own column so you know what you could actually take with you.
- Taxes later. Roth money and pre-tax money are taxed differently when withdrawn, so tracking them apart now saves work later.
Contributions versus paychecks
Contributions on the statement are usually dated by when the plan received them, not by your pay date. A paycheck at the end of a quarter can land in the next statement. That is a timing difference, not a missing contribution. If you match statement contributions to your pay stubs, allow for a few days of lag around each quarter end.
Contributions start in your bank account as part of your pay, which is a separate record. If you want to line up the net pay deposits with the 401k quarters, a bank statement exported to CSV gives you those deposits as rows to sit beside this table.
Where does the gain on a 401k statement come from?
The gain on a 401k statement is the change in value that contributions, fees and withdrawals do not explain. Work it out as ending balance minus beginning balance, minus contributions, plus fees and withdrawals. If that matches the gain or loss the statement prints, your summary numbers are copied correctly.
Gain is not the same as return
A gain in dollars depends on how much money was in the account and when it arrived. A quarter with large contributions can show a bigger dollar gain than a quarter with a better market, simply because more was invested. If you want a rate of return, divide the gain by a rough average balance, such as the beginning balance plus half the contributions. It is an approximation, and the statement's own rate of return, if printed, is the better figure.
Dividends and reinvestment
Some statements list dividends and capital gain distributions separately. Inside a 401k, these are usually reinvested in the same fund, so they show up as more shares rather than as cash. In the roll-forward, they belong in gain or loss. Do not add them to contributions, because that would make it look as though more of your own money went in.
Joining quarterly 401k statements into one table
Joining quarterly 401k statements means giving each statement one row in a roll-forward table, in date order, with the same columns every time. The test that the table is right is simple: each quarter's ending balance must equal the next quarter's beginning balance. A break in that chain means a statement is missing or a number was misread.
A worked example roll-forward
The three quarters below use example values, not a real account.
| Quarter | Beginning | Employee | Employer | Fees | Gain or loss | Ending |
|---|---|---|---|---|---|---|
| 2026 Q1 | 48,200.00 | 1,950.00 | 975.00 | -18.40 | 1,342.15 | 52,448.75 |
| 2026 Q2 | 52,448.75 | 1,950.00 | 975.00 | -18.90 | -2,104.60 | 53,250.25 |
| 2026 Q3 | 53,250.25 | 2,100.00 | 1,050.00 | -19.25 | 1,688.30 | 58,069.30 |
Each row adds across: beginning plus contributions, plus fees (negative here), plus gain or loss, equals ending. Each ending equals the next beginning. In Q3 the employee column rose, which on a real account would point to a raise or a changed contribution rate.
Holdings that tie to the last row
The holdings table for the end of Q3 should add up to the same 58,069.30. In this example:
| Fund | Shares | Price | Value |
|---|---|---|---|
| Example 2050 Target Date Fund | 812.304 | 41.27 | 33,523.79 |
| Example S&P 500 Index Fund | 115.820 | 172.40 | 19,967.37 |
| Example Stable Value Fund | 4,578.140 | 1.00 | 4,578.14 |
| Total | 58,069.30 |
When the summary and the holdings agree, both parts of the statement came across. If they differ, check share decimals first, then whether a fund is missing.
Formulas that keep the chain honest
Put a check column at the end of the roll-forward with =B3-G2, the beginning of this quarter minus the ending of the last. Every value should be zero. Add a second check with =B2+C2+D2+E2+F2-G2 for each row's own arithmetic. Any non-zero cell tells you exactly which statement to reopen.
Loans, rollovers and fee lines that break a 401k roll-forward
Loans, rollovers, withdrawals and one-off fees are the entries that most often stop a 401k roll-forward from tying. Each moves money in or out without being a contribution or a market change. Give each its own column in the roll-forward, or the gain you compute will absorb them and look wrong.
Common breakers
- Loans. Taking a 401k loan moves money out of the account; repayments move it back. Treat loan out as a withdrawal and repayments as their own column, not as contributions.
- Rollovers in. Money rolled in from an old plan or an IRA arrives as one large amount. It is not a contribution from pay, so it needs its own column.
- Distributions. Any money paid out reduces the balance and belongs in a withdrawals column.
- Plan-level fees. Some plans charge a yearly or one-off fee that appears only in one quarter. Copy it with the other fees so the gain stays clean.
When to stop tracking quarters
A quarterly table is worth it while you are adding money and want to check contributions and fees. After you leave a job and the account just sits, a yearly row is enough. The same structure works for other accounts held outside a bank: an HSA statement in Excel with contributions and medical withdrawals uses a very similar roll-forward, and a crypto exchange statement turned into Excel rows adds trades and prices on top.
Where the bank side fits
FastBankConvert is built for bank and credit card statements, not retirement statements, and it checks that the balances add up before you download. It earns its place in this workbook on the cash side, turning the checking account that receives your pay into rows. You can see how a statement conversion works on the homepage.