Home › Templates › RSU and ESPP tracker
RSU and ESPP Tracker Spreadsheet
A free RSU tracker spreadsheet and ESPP spreadsheet in one file for Excel and Google Sheets. Enter your grants, vests, ESPP purchases and sales. It builds the vesting schedule, logs income at each vest, works out the ESPP purchase price, tests each sale as qualifying or disqualifying, splits ordinary income from capital gain under IRS rules, corrects the cost basis your broker reports, and shows totals by year and how much of your portfolio sits in employer stock.
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 and no broker connection: you copy the numbers from your equity portal, Form 3922 and Form 1099-B.
US only. The rules are US federal income tax rules for RSUs and qualified employee stock purchase plans (Internal Revenue Code §423). It does not estimate tax, and it does not cover state tax, AMT, ISOs, nonqualified options or wash sales.
| Sale date | Lot | Shares | Price | Holding | ESPP test | Ordinary income | Adjusted basis | Capital gain/loss |
|---|---|---|---|---|---|---|---|---|
| 15 Aug 2024 | RSU R01 | 100 | $64.00 | Long-term | n/a | $0.00 | $4,400.00 | $2,000.00 |
| 20 Aug 2025 | ESPP E01 | 154 | $76.00 | Long-term | Qualifying | $970.20 | $6,468.00 | $5,236.00 |
| 10 Sep 2025 | ESPP E03 | 129 | $74.00 | Long-term | Disqualifying | $2,644.50 | $8,127.00 | $1,419.00 |
| 14 Nov 2025 | ESPP E05 | 92 | $66.00 | Short-term | Disqualifying | $1,702.00 | $7,176.00 | -$1,104.00 |
| 15 Apr 2026 | ESPP E02 | 129 | $47.50 | Long-term | Qualifying | $645.00 | $6,127.50 | $0.00 |
| 20 May 2026 | RSU R32 | 17 | $58.00 | Short-term | n/a | $0.00 | $884.00 | $97.05 |
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, the tax rules used, colour key, limits, disclaimer |
| Summary | As-of date, current share price, total portfolio value, alert threshold (10% to start) | Shares held, value, cost basis, unvested RSUs, employer-stock share of your portfolio, next vest, ESPP lots that would qualify if sold today; a year-by-year table of RSU income, ESPP ordinary income, short- and long-term gains and a §423 $25,000 screen; two native charts |
| RSU grants | Up to 10 grants: ID, grant date, units, optional vesting start, cliff months, vest frequency, total months | A generated vesting schedule (up to 48 vest dates per grant) with cumulative and per-vest shares, vested vs unvested, next vest date and shares, and a check of logged vests against the schedule |
| RSU vests | Up to 300 vests: lot ID, grant, vest date, shares vested, FMV at vest, shares withheld for tax | Ordinary income (shares × FMV), value withheld, net shares, cost basis per share, shares sold and left, value now, the date a sale turns long-term |
| ESPP purchases | Up to 200 purchases from Form 3922: offering date, purchase date, both FMVs, discount, lookback Y/N, shares, optional actual price | Purchase price, cost, discount at purchase, the box 8 "as if at offering" price, the most a qualifying sale can make ordinary, the "qualifying after" date, shares left and status if sold today |
| Sales | Up to 300 sale rows: date, RSU or ESPP, lot ID, shares, price, fees, optional 1099-B basis | Holding period, qualifying or disqualifying test, ordinary income, adjusted basis, capital gain or loss split short-term/long-term, and the basis difference to fix on Form 8949 |
| Example Summary, Example RSU grants, Example RSU vests, Example ESPP purchases, Example Sales | Already filled | Fictional Example Corp data: 4 grants, 38 vests, 7 ESPP purchases and 6 sales covering every case |
Every input tab has a Check column. A row that is not OK is left out of every total, and Summary rows 12–16 count rows that need a fix. Lot IDs must be unique across the RSU vests and ESPP purchases tabs, so a sale always finds the right lot.
The RSU grants tab also works on its own as an RSU vesting schedule template: type a grant with its cliff and frequency, and it lists every vest date, the shares in each vest and their value at today's price, even before you log any vests.
How to use it
- On Summary, type the current share price and the total value of all your investment accounts (including employer stock). Leave the as-of date blank for today.
- On RSU grants, add each grant from your grant agreement or equity portal: units, cliff (0 if none), vest frequency in months and total months. Example: a 4-year grant with a 1-year cliff and quarterly vesting is cliff 12, every 3, total 48.
- On RSU vests, add each vest from the broker's release confirmation: shares vested, FMV at vest and shares withheld for tax. Give each vest a lot ID (R01, R02…).
- On ESPP purchases, copy boxes 1–6 of each Form 3922, plus the plan's discount and whether it has a lookback. If your plan rounds the price, type the actual price paid (box 5) in column I.
- On Sales, add one row per lot sold: date, RSU or ESPP, lot ID, shares, sale price, fees. If you have the Form 1099-B, type the basis it shows to see the adjustment you need.
- Compare the Summary's yearly RSU income with box 1 of your W-2 and the yearly gains with your Form 8949. Fix any red Check cells first.
How RSUs are taxed (US)
An RSU is a promise of shares later. An IRS presentation on stock compensation puts it this way: "The employee does not receive any shares upon grant of the RSU." When the shares are delivered at vest, the restricted-property rule in IRS Publication 525 applies: "In most cases, if you receive property for your services, you must include its FMV in your income in the year you receive the property."
- Income at vest = shares vested × FMV on the vest date. It is ordinary income (wages), not a capital gain.
- It is on your W-2. The IRS presentation says employers "include an income on the Form W-2 at the Fair Market Value of the Restricted Stock Units as of the date they vested. This Fair Market Value will be shown in boxes 1, 3 and 5 on the Form W-2." (The presentation is about nonresident aliens; the W-2 reporting it describes is the same for US employees.)
- Withheld shares are still income. The shares your employer keeps to cover tax are part of the vest; you just never receive them. The tracker logs them separately and counts only the net shares as yours.
- Cost basis per share = FMV at vest, because you already paid tax on that value.
- Holding period starts at vest: Publication 525 says "Your holding period for this property begins when the property becomes substantially vested." Publication 550 adds: "If you hold investment property more than 1 year, any capital gain or loss is a long-term capital gain or loss."
How ESPP sales are taxed: qualifying vs disqualifying
A qualified ESPP lets you buy employer stock at a discount of up to 15%, often with a lookback: the price is the discount applied to the lower of the price on the offering date and the price on the purchase date. You owe no tax when you buy. IRS Topic 427 says you generally don't include any amount in income "when you receive or exercise the option", and that if you don't meet the holding period rules, "you'll have to treat income from the sale as ordinary income."
The holding period test comes from IRC §423(a)(1): "no disposition of such share is made by him within 2 years after the date of the granting of the option nor within 1 year after the transfer of such share to him". For an ESPP, the grant date is the first day of the offering period (Form 3922 box 1).
| Disqualifying sale | Qualifying sale | |
|---|---|---|
| When | Within 2 years of the offering date, or within 1 year of the purchase date | More than 2 years after the offering date and more than 1 year after the purchase date |
| Ordinary income | (FMV at purchase − price paid) × shares. Pub. 525: "This ordinary income isn’t limited to your gain from the sale of the stock." | The lesser of (FMV at offering − price as if bought at offering) and (sale price − price paid), times shares; zero if you sold at a loss |
| Basis for the capital gain | Price paid + the ordinary income | Price paid + the ordinary income |
| Capital gain or loss | Short-term or long-term, from the purchase date | Always long-term |
For the qualifying case, Publication 525 lists the two amounts and adds: "if the option price wasn’t fixed or determinable at the time the option was granted, the option price is figured as if the option had been exercised at the time it was granted." That is the price on Form 3922 box 8, "Exercise Price Per Share Determined as if the Option Was Exercised on the Date Shown in Box 1". The tracker computes it as (1 − discount) × FMV at offering.
Checked against the IRS examples. Publication 525 Examples 10 and 11 (an option price of $20, FMV of $22 at grant and $23 at exercise, sold at $30) were typed into the Sales tab. The sheet returns $200 of wages and an $800 capital gain for the qualifying sale, and $300 of wages and a $700 capital gain for the disqualifying one. Both match the publication.
The $25,000 limit. Under IRC §423(b)(8), a plan can't let your right to buy stock accrue "at a rate which exceeds $25,000 of fair market value of such stock (determined at the time such option is granted) for each calendar year". The limit uses the offering-date FMV, not the discounted price. The Summary adds the offering-date FMV of shares bought each year and flags any year above $25,000. It is only a rough screen; your plan applies the exact rule.
The cost basis trap on Form 1099-B
Your broker's Form 1099-B often shows a basis that leaves out income you already paid tax on. For ESPP shares, Publication 525 is explicit: "For options granted on or after January 1, 2014, the basis information reported to you on Form 1099-B won’t reflect any amount you included in income upon grant or exercise of the option." It continues: "It’s your responsibility to make any appropriate adjustments to the basis information reported on Form 1099-B by completing Form 8949."
The Form 8949 instructions tell you what to do when "the basis shown in box 1e on Form 1099-B … is incorrect": enter code B and correct it, and "Increase your basis by any amount you included in income upon grant or exercise of the option." If you skip this, the discount is taxed twice: once as wages and again as a capital gain. The Sales tab's last-but-one column (adjusted basis − 1099-B basis) is the amount to check. For RSU shares, compare the 1099-B basis with the vest-date FMV in your vest log; if the broker shows no basis or a lower one, the same double-counting risk applies.
Concentration: how much employer stock is too much?
RSUs and an ESPP keep adding shares of one company, the same company that pays your salary. FINRA's guide to company stock says: "In the event your company falters, not only might your investments tumble, but you might also find yourself out of work at the same time." On the size of the position, FINRA says there is no single formula, and: "Some experts recommend investing no more than 10 percent of total investment assets in a single stock, including stock of your company—and that could be too high, depending on your goals and circumstances."
The Summary divides the value of the employer shares you hold by the total portfolio value you type, and compares it with your threshold (10% to start, editable). A second line adds unvested RSUs to both sides, because they are part of your exposure even before they vest. FINRA's concentration risk guide lists "Company stock concentration" as one of the common causes.
The formulas, explained
Exact formulas from the blank tabs (first data row). On Sales, B is the plan, J the matched lot row, K the acquired date, N the basis per share before adjustment. Summary!$B$10 is the as-of date.
| Field | Formula |
|---|---|
| RSU grants: vest date in the schedule (C21) | =IF(A21="","",EDATE($J$7,($K$7+0*$F$7))) |
| RSU grants: cumulative shares vested (D21) | =IF(A21="","",ROUNDDOWN($C$7*($K$7+0*$F$7)/$G$7,0)) |
| RSU vests: ordinary income at vest (I8) | =IF(H8<>"OK","",D8*E8) |
| ESPP purchases: purchase price (L8) | =IF(K8<>"OK","",IF(I8<>"",I8,(1-F8)*IF(G8="Y",MIN(D8,E8),E8))) |
| ESPP purchases: price as if bought at offering, Form 3922 box 8 logic (O8) | =IF(K8<>"OK","",(1-F8)*D8) |
| ESPP purchases: qualifying if sold after (Q8) | =IF(K8<>"OK","",MAX(EDATE(B8,24),EDATE(C8,12))) |
| Sales: holding period (L8) | =IF(I8<>"OK","",IF(A8>EDATE(K8,12),"Long-term","Short-term")) |
| Sales: ESPP disposition test (M8) | =IF(I8<>"OK","",IF(B8="RSU","n/a",IF(AND(A8>EDATE(INDEX('ESPP purchases'!$B$8:$B$207,J8),24),A8>EDATE(K8,12)),"Qualifying","Disqualifying"))) |
| Sales: ordinary income from the sale (O8) | =IF(I8<>"OK","",IF(B8="RSU",0,IF(M8="Disqualifying",(INDEX('ESPP purchases'!$E$8:$E$207,J8)-N8)*D8,MAX(0,MIN(INDEX('ESPP purchases'!$P$8:$P$207,J8),E8-N8))*D8))) |
| Sales: adjusted basis (P8) | =IF(I8<>"OK","",N8*D8+O8) |
| Sales: capital gain or loss (R8) | =IF(I8<>"OK","",Q8-P8) |
| Sales: adjusted basis minus Form 1099-B basis (U8) | =IF(OR(I8<>"OK",G8=""),"",P8-G8) |
| Summary: employer stock share of portfolio (B27) | =IF(OR(N(B6)=0,B22=""),"",B22/B6) |
| Summary: RSU income for a year (D35) | =SUMIFS('RSU vests'!$I$8:$I$307,'RSU vests'!$H$8:$H$307,"OK",'RSU vests'!$R$8:$R$307,A35) |
Vesting schedule. The first vest is at the cliff month, or after one period when there is no cliff. Then a vest every N months until the total. Cumulative shares = ROUNDDOWN(units × months elapsed ÷ total months). Each vest is a whole number of shares, and the last vest brings the total to exactly the grant. Plans round differently, so the RSU grants tab compares the schedule with what you logged.
Dates. "More than 1 year" is tested as sale date > EDATE(acquired, 12). A sale on the 1-year anniversary is still short-term, and so is a sale on the 2-year anniversary of the offering date (still disqualifying). Publication 550's example: bought on January 31, 2024 and sold on January 29, 2025 is short-term; sold on February 6, 2025 is long-term.
Worked example: Example Corp (fictional)
The Example tabs hold 4 grants, 38 vests, 7 ESPP purchases (15% discount, 6-month offerings with a lookback) and 6 sales, as of 30 Oct 2026. All names, prices and amounts are made up.
Vesting schedule with rounding
Grant G3-2024 is 300 units, quarterly over 48 months with no cliff: 18.75 units a quarter. Whole shares come from the cumulative rounding:
| Vest date | Cumulative vested | Shares this vest | Status on 30 Oct 2026 |
|---|---|---|---|
| 15 Jun 2024 | 18 | 18 | Vested |
| 15 Sep 2024 | 37 | 19 | Vested |
| 15 Dec 2024 | 56 | 19 | Vested |
| 15 Mar 2025 | 75 | 19 | Vested |
| 15 Jun 2025 | 93 | 18 | Vested |
| 15 Sep 2025 | 112 | 19 | Vested |
By 30 Oct 2026, 187 of its shares have vested and 113 are unvested; the next vest is 19 shares on 15 Dec 2026. The new-hire grant G1-NEWHIRE (800 units, 12-month cliff, then quarterly) vested 200 shares at the cliff on 15 Mar 2023 and was fully vested on 15 Mar 2026.
Each sale, step by step
- RSU, long-term: 100 shares from the 15 Mar 2023 vest sold on 15 Aug 2024 at $64.00. Basis is the vest-date FMV ($44.00), so the gain is $2,000.00, long-term because the sale is more than a year after vest. No new ordinary income: it was on the 2023 W-2.
- ESPP, qualifying: lot E01 was offered on 1 Feb 2023 at $42.00 and bought on 31 Jul 2023 at $35.70 (85% of the lower $42.00). Sold on 20 Aug 2025 at $76.00, after 1 Feb 2025, so it qualifies. Ordinary income = the lesser of $6.30 (15% × $42.00) and $40.30 per share = $970.20. Long-term gain $5,236.00.
- ESPP, disqualifying but long-term: lot E03 bought on 31 Jul 2024 and sold on 10 Sep 2025: more than a year after purchase, but less than 2 years after the 1 Feb 2024 offering. Ordinary income is the full purchase discount, ($63.00 − $42.50) × 129 = $2,644.50; the remaining $1,419.00 is a long-term gain.
- ESPP, disqualifying at a loss: lot E05 bought at $59.50 when the FMV was $78.00, sold at $66.00. Ordinary income is still $1,702.00, and the basis rises to $7,176.00, so the sale shows a short-term capital loss of -$1,104.00.
- ESPP, qualifying with a small gain: lot E02 sold at $47.50, only $5.00 above the $42.50 price paid. The offering-date cap would be $7.80 a share, but the income is limited to the actual gain: $645.00 of ordinary income and a capital gain of $0.00.
- RSU, short-term with a fee: the 17 net shares of the 15 Mar 2026 vest sold on 20 May 2026, with a $4.95 fee: $97.05 short-term gain.
The 1099-B basis typed for the four ESPP sales is the price paid × shares, as the IRS says the broker will report it. The Sales tab shows adjustments of $970.20, $2,644.50, $1,702.00 and $645.00: each one equals that sale's ordinary income.
By year
| Year | RSU income (W-2) | ESPP OI, disqualifying | ESPP OI, qualifying | Total ordinary income | Short-term | Long-term | RSU shares scheduled |
|---|---|---|---|---|---|---|---|
| 2023 | $20,151.25 | $0.00 | $0.00 | $20,151.25 | $0.00 | $0.00 | 425 |
| 2024 | $22,076.27 | $0.00 | $0.00 | $22,076.27 | $0.00 | $2,000.00 | 356 |
| 2025 | $26,732.84 | $4,346.50 | $970.20 | $32,049.54 | -$1,104.00 | $6,655.00 | 375 |
| 2026 | $16,268.43 | $0.00 | $645.00 | $16,913.43 | $97.05 | $0.00 | 305 |
| 2027 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | 180 |
| 2028 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | $0.00 | 99 |
In 2025, RSU vests added $26,732.84 of wages and ESPP sales $5,316.70, for $32,049.54 of equity ordinary income. Capital results were a -$1,104.00 short-term loss and a $6,655.00 long-term gain. The scheduled column keeps going after 2026: 180 shares are due to vest in 2027 and 99 in 2028.
Concentration
At $72.00 a share, the 1,182 shares held are worth $85,104.00: 27.5% of the $310,000 portfolio, above the 10% threshold. Counting the 323 unvested RSUs ($23,256.00) brings the exposure to 32.5%. Of the ESPP lots still held, 1 would be a qualifying sale on 30 Oct 2026 and 2 would be disqualifying.
Common mistakes
- Adding RSU income twice. Vest income is already in W-2 wages. On Form 8949 the basis is the vest-date FMV, not zero.
- Trusting the 1099-B basis for ESPP shares. It leaves out the ordinary income. Correct it on Form 8949, or the discount is taxed twice.
- Counting withheld shares as yours. Only net shares reach your account. Selling "all" the gross shares will show as an oversold lot.
- Using the purchase date for the 2-year test. The 2-year clock starts on the offering (grant) date, the 1-year clock on the purchase date. A sale needs both.
- Assuming a loss means no ordinary income. In a disqualifying sale, the purchase discount is ordinary income even if the stock fell.
- Ignoring concentration. Vests and purchases keep adding employer stock. Check the Summary percentage after each vest.
Limits
- US federal rules only. No tax estimate, state tax, AMT, NIIT, wash sales, dividends or stock splits.
- Covers RSUs and qualified (§423) ESPPs. Not ISOs, nonqualified stock options, restricted stock awards with an 83(b) election, or non-US plans.
- One employer and one share class. You pick the lot for each sale (specific identification); there is no automatic FIFO.
- The holding period uses the purchase date; Publication 525 measures 1 year from when the stock "was transferred to you" (Form 3922 box 7), which is usually the same day. Check yours.
- Vest rounding follows one common convention (cumulative, rounded down). Your plan may differ; the RSU grants tab shows the difference from your logged vests.
- Up to 10 grants with 48 vest dates each, 300 vests, 200 ESPP purchases and 300 sale rows. Recalculation was tested in LibreOffice; the functions also exist in Excel 2016+ and Google Sheets.
FAQ
How are RSUs taxed when they vest?
In the US, the value of the shares you receive is ordinary income: shares vested × fair market value (FMV) on the vest date. IRS Publication 525 says that when property received for services "becomes substantially vested, you must include its FMV, minus any amount you paid for it, in your income for that year." Your employer reports it as wages on your W-2, and that FMV becomes your cost basis per share.
Is RSU income already on my W-2?
Generally yes. An IRS presentation on stock compensation says employers "include an income on the Form W-2 at the Fair Market Value of the Restricted Stock Units as of the date they vested", shown in boxes 1, 3 and 5. Shares sold or withheld to cover tax don't change that. Check your W-2 against the RSU income column in this tracker. Don't add the same income again.
What is the difference between a qualifying and a disqualifying ESPP disposition?
A sale is qualifying when it is more than 2 years after the offering (grant) date and more than 1 year after the purchase date (IRC §423(a)(1)). If you sell sooner, the sale is disqualifying: the discount at purchase (FMV on the purchase date minus the price you paid) is ordinary income, even if you sold at a loss. In a qualifying sale, ordinary income is capped at the smaller of the offering-date discount and your actual gain.
Why is the cost basis on my 1099-B wrong for ESPP shares?
IRS Publication 525 says that for options granted on or after January 1, 2014, "the basis information reported to you on Form 1099-B won’t reflect any amount you included in income upon grant or exercise of the option." Add the ordinary income to the basis on Form 8949. The Sales tab shows this difference for each sale.
When do RSU shares become long-term?
Your holding period starts when the shares vest. Sales more than 1 year later are long-term capital gains or losses; sales 1 year or less after the vest date are short-term. The tracker uses the vest date as the acquisition date and calls a sale long-term only if it is after the same date one year later.
Can I use this RSU tracker in Google Sheets?
Yes. In Google Sheets, choose File → Import → Upload and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. It uses only functions that work in Excel 2016+ and Google Sheets: EDATE, SUMIFS, COUNTIFS, INDEX/MATCH, IFERROR, ROUNDDOWN, MIN/MAX and the usual IF/AND/OR. No macros, no dynamic arrays.
Related tools and templates
- Capital gains tax calculatorEstimate tax on a sale
- Stock portfolio trackerAll your holdings in one sheet
- Portfolio rebalancing spreadsheetBring employer stock back to target
- Tax-loss harvesting spreadsheetLots with losses to use
- Investment trackerContributions and returns
- All templatesFree spreadsheets
Educational template only, not tax, legal or investment advice. It follows US federal rules as described in IRS Publication 525 (2025), Topic 427, the Instructions for Forms 3921 and 3922 (04/2025), the Instructions for Form 8949 (2025), Publication 550 and IRC §423, all retrieved on 3 November 2026; rules can change, and your plan documents and tax adviser decide your case. Example Corp and all example numbers are fictional.