Home › Templates › Trading cost spreadsheet
Trading Cost Spreadsheet
A free trading fees calculator and commission tracker for Excel and Google Sheets. Log each closed trade once, and it splits the cost into commission, spread, slippage, financing or swap, SEC and FINRA sale fees, currency conversion and other fees. You see the total cost per trade in $, as % of the position, as % of the gross profit and in R, plus gross vs net P/L, the break-even move and monthly totals.
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 gate, no add-in.
| Exit date | Market | Symbol | Side | Qty | Gross P/L | Total cost | Net P/L | Cost % of notional |
|---|---|---|---|---|---|---|---|---|
| 2026-01-13 | Stock | SMPL | Long | 400 | $96.00 | $0.08 | $95.92 | 0.000% |
| 2026-01-20 | Forex | USDJPY | Short | 100,000 | −$174.64 | $21.42 | −$196.06 | 0.021% |
| 2026-01-26 | Forex | GBPUSD | Long | 20,000 | $12.20 | $3.60 | $8.60 | 0.013% |
| 2026-01-30 | Forex | EURUSD | Long | 100,000 | $198.00 | $20.00 | $178.00 | 0.017% |
| 2026-02-04 | Forex | USDJPY | Long | 10,000 | −$11.49 | $1.53 | −$13.02 | 0.015% |
| 2026-02-10 | Stock | XMPL | Long | 300 | $24.00 | $6.06 | $17.94 | 0.019% |
| 2026-02-20 | Forex | GBPUSD | Long | 50,000 | $135.00 | $15.63 | $119.37 | 0.025% |
| 2026-02-23 | Stock | DEMO | Short | 50 | $113.00 | $2.82 | $110.18 | 0.065% |
| 2026-02-27 | Stock | PNNY | Long | 80,000 | −$2,480.00 | $409.79 | −$2,889.79 | 0.342% |
| 2026-03-02 | Forex | EURUSD | Long | 20,000 | −$27.40 | $3.80 | −$31.20 | 0.016% |
| 2026-03-09 | Stock | PNNY | Long | 80,000 | $8,640.00 | $409.79 | $8,230.21 | 0.224% |
| 2026-03-16 | Stock | FICT | Long | 100 | −$154.00 | $4.02 | −$158.02 | 0.031% |
Why track trading costs trade by trade
Costs come in two kinds. Explicit costs appear on the statement: commission, regulatory fees, overnight financing, currency conversion, borrow fees. Implicit costs hide inside the fill price: the bid-ask spread and slippage. A zero-commission account still pays the second kind on every trade.
The SEC's investor education site puts the general point plainly: "Transaction fees are charged each time you buy, sell or exchange an investment," and "These fees may seem small, but over time they can have a major impact on your investment portfolio" (Investor.gov, How Fees and Expenses Affect Your Investment Portfolio).
EU rules ask firms to show clients the same picture this sheet builds. Under Article 50 of Delegated Regulation (EU) 2017/565, the "aggregated costs and charges shall be totalled and expressed both as a cash amount and as a percentage", and Annex II lists transaction costs such as "Broker commissions", "mark ups (embedded in the transaction price), stamp duty, transactions tax and foreign exchange costs" (EUR-Lex).
In the US, FINRA Rule 5310 requires brokers to "use reasonable diligence to ascertain the best market for the subject security and buy or sell in such market so that the resultant price to the customer is as favorable as possible under prevailing market conditions" (FINRA Rule 5310). Logging your intended price next to the fill is how you see what execution actually cost you.
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, formulas in plain words, colour key, limits, sources, disclaimer |
| Summary | Nothing | Your totals next to the Example's: gross P/L, every cost line, total cost, net P/L, cost % of notional and of gross P/L, average cost in R, gross winners turned net losers. Cost by market. Monthly cost tables (24 months) with stacked charts |
| Trade log | Up to 300 closed trades: dates, market, symbol, side, quantity, intended and actual prices, FX rate, spread, commission (fixed and %), financing or swap, FX fee %, US sale fees Y/N, other fees, initial risk, notes | Per trade: row status, entry and exit notional, P/L at fills, spread, slippage, commission, SEC fee, FINRA TAF, FX fee, explicit and implicit costs, total cost, gross and net P/L, cost % of notional, cost % of gross P/L, cost and net P/L in R, break-even move, month, days held |
| Example | Already filled: 60 fictional trades in US stocks, a non-US stock, forex and crypto | The same tab with every result visible |
| Fee rates | New rows when the SEC or FINRA change a rate | SEC Section 31 and FINRA TAF rates with effective dates; each sale picks the rate in force on its date |
How to use it
- Open Trade log. For each closed trade, type the entry and exit dates, market, symbol, Long or Short, quantity and the actual entry and exit fill prices. Those are required; every other column is optional and counts as 0 when blank.
- Add the costs you know: commission per side (a fixed amount, a % of the trade value, or both), the spread when you traded (ask − bid in price units: 0.0001 is 1 pip on EURUSD, 0.01 on USDJPY), financing or swap from your statement (a credit is negative), an FX conversion fee %, and other fees such as borrow or exchange fees.
- For slippage, type the price you meant to get: the quote you saw, your limit or your stop level. The sheet counts the gap to the fill on both sides. Price improvement shows as a negative number.
- If prices are in another currency than your account, type the FX rate: account currency per 1 unit of the quote currency. For USDJPY in a USD account that is 1 ÷ USDJPY.
- For US stock and ETF trades, type Y in column R to add the SEC Section 31 fee and the FINRA TAF on the sale side. Check that the Row status says OK, then read the Summary tab.
How the costs are calculated
Fill prices already contain the spread and any slippage. So the sheet adds them back to get a gross P/L before costs, then takes every cost off once:
Gross P/L = P/L at fills + spread cost + slippage
Total cost = commission + SEC fee + TAF + FX fee + financing + other + spread + slippage
Net P/L = gross P/L − total cost = P/L at fills − explicit fees
Break-even move % = total cost ÷ entry notional
Exact formulas from the Trade log tab (row 8 shown). In the file each one is wrapped in IF($V8="OK", …, "") so a row stays blank until its inputs are valid.
| Field | Formula |
|---|---|
| Entry notional (X8) | =$G8*$I8*$W8 |
| P/L at fill prices (Z8) | =IF($F8="Long",1,-1)*$G8*($K8-$I8)*$W8 |
| Spread cost (AA8) | =N($M8)*$G8*$W8 |
| Slippage (AB8) | =IF($F8="Long",1,-1)*$G8*$W8*(IF(ISNUMBER($H8),$I8-$H8,0)+IF(ISNUMBER($J8),$J8-$K8,0)) |
| Commission (AC8) | =2*N($N8)+N($O8)*($X8+$Y8) |
| SEC Section 31 fee (AD8) | =IF($R8="Y",IF($F8="Long",$Y8,$X8)*INDEX('Fee rates'!$B$7:$B$16,COUNTIF('Fee rates'!$A$7:$A$16,"<="&IF($F8="Short",$B8,$C8)))/1000000,0) |
| FINRA TAF (AE8) | =IF($R8="Y",MIN($G8*INDEX('Fee rates'!$B$22:$B$31,COUNTIF('Fee rates'!$A$22:$A$31,"<="&IF($F8="Short",$B8,$C8))),INDEX('Fee rates'!$C$22:$C$31,COUNTIF('Fee rates'!$A$22:$A$31,"<="&IF($F8="Short",$B8,$C8)))),0) |
| FX conversion fee (AF8) | =N($Q8)*($X8+$Y8) |
| Total cost (AK8) | =$AI8+$AJ8 |
| Gross P/L before costs (AL8) | =$Z8+$AJ8 |
| Net P/L (AM8) | =$Z8-$AI8 |
| Cost % of entry notional (AN8) | =$AK8/$X8 |
| Cost % of gross P/L (AO8) | =IF($AL8>0,$AK8/$AL8,"n/a: gross <= 0") |
| Cost in R (AP8) | =IF(ISNUMBER($T8),$AK8/$T8,"") |
| Break-even move, price units (AR8) | =$AK8/($G8*$W8) |
SEC Section 31 fee and FINRA TAF: current rates
Both fees apply to sales, so the sheet charges them on the exit of a long trade and on the entry of a short. Each trade picks the rate in force on its sale date from the Fee rates tab.
| Fee | Rate | In force | Source |
|---|---|---|---|
| SEC Section 31 | $27.80 per $1,000,000 of sales | 22 May 2024 to 13 May 2025 | SEC Fee Rate Advisories |
| SEC Section 31 | $0.00 per $1,000,000 | 14 May 2025 to 3 Apr 2026 | SEC Fee Rate Advisories |
| SEC Section 31 | $20.60 per $1,000,000 | From 4 Apr 2026 | SEC Release 34-104909 |
| FINRA TAF (stocks) | $0.000166 per share sold, max $8.30 per trade | 2024 and 2025 | FINRA Schedule A, Section 1 |
| FINRA TAF (stocks) | $0.000195 per share sold, max $9.79 per trade | 1 Jan to 30 Sep 2026 | SR-FINRA-2024-019 |
| FINRA TAF (stocks) | $0.00 (paused) | 1 Oct to 31 Dec 2026 | SR-FINRA-2026-021 |
| FINRA TAF (stocks) | $0.000195 per share sold, max $9.79 per trade | From 1 Jan 2027 | SR-FINRA-2026-021 |
The SEC order reads: "the fee rates applicable under Sections 31(b) and (c) of the Exchange Act shall be $20.60 per $1,000,000 effective on April 4, 2026." The SEC explains that these fees are paid by exchanges and FINRA, and "Broker-dealers, in turn, generally charge their customers per-transaction charges" (SEC, Section 31 basic information). FINRA's filing pauses the TAF "for transactions from October 1, 2026 through December 31, 2026", "with TAF assessment resuming on January 1, 2027." Some third-party pages still quote the older $0.000166 and $8.30 figures.
These rates were checked on 8 November 2026. The SEC resets its rate each fiscal year, and FINRA's schedule lists further steps. When a rate changes, add a row with its start date on the Fee rates tab; older trades keep their old rate.
Worked example from the sample data
A US stock short with borrow, SEC and TAF fees
2026-07-02: short 300 shares of the fictional XMPL at $125.54 (intended $125.56), covered on 2026-07-07 at $123.78.
- Sale side: for a short, the sale is the entry, worth 300 × $125.54 = $37,662.00. SEC fee = $37,662.00 × 20.60 ÷ 1,000,000 = $0.7758. TAF = 300 × $0.000195 = $0.0585.
- Spread 0.05 × 300 = $15.00; slippage $15.00; commission $0.00; borrow fee (in Other) $56.50.
- Total cost $87.33: 0.232% of the position, 15.7% of the $558.00 gross P/L, 0.14R against $612.00 of initial risk. Net P/L $470.67.
The TAF cap on a large share count
2026-03-09: 80,000 shares of the fictional PNNY sold at $2.390. Uncapped, the TAF would be 80,000 × $0.000195 = $15.60; the 2026 cap limits it to $9.79. On low-priced shares the fee is the small part: the spread (0.002 × 80,000 = $160.00) and slippage ($240.00) make up most of the $409.79 total cost.
A forex trade quoted in yen
2026-08-07: long 20,000 USDJPY at 149.091, closed at 149.346. P/L is in yen, so the FX rate is 1 ÷ 149.346 = 0.006696. Spread 0.012 (1.2 pips) × 20,000 × 0.006696 = $1.61. The fictional swap is a credit of $0.61, which lowers the cost. Total cost $2.26; break-even move 0.0169 yen, or 0.011%.
A gross winner that costs turned into a net loser
2026-03-23: short 0.2 BTC/USD at $99,472.64, covered at $99,400.49. The trade made $22.00 before costs, but commission at 0.04% per side ($15.91), the spread ($1.60) and slippage ($5.97) cost $23.48, so the net result is −$1.48. The Example has 5 trades like this out of 30 gross winners.
All 60 trades
Gross P/L before costs $5,879.79, total cost $1,710.62 (29.1% of gross, 0.092% of the $1,857,503 traded), net P/L $4,169.16. Explicit fees were only 34% of the total; the rest was spread and slippage.
| Cost | Example total | Share of total cost |
|---|---|---|
| Spread | $524.90 | 30.7% |
| Slippage | $597.96 | 35.0% |
| Commission | $228.61 | 13.4% |
| SEC Section 31 fees | $7.53 | 0.4% |
| FINRA TAF | $20.33 | 1.2% |
| FX conversion fees | $151.23 | 8.8% |
| Financing / swap, net | $100.00 | 5.8% |
| Other fees (borrow) | $80.06 | 4.7% |
| Total cost | $1,710.62 | 100.0% |
| Market | Trades | Entry notional | Total cost | Cost % of notional | Average cost per trade |
|---|---|---|---|---|---|
| Stock | 27 | $806,056 | $1,281.36 | 0.159% | $47.46 |
| Forex | 19 | $940,812 | $183.03 | 0.019% | $9.63 |
| Crypto | 14 | $110,635 | $246.23 | 0.223% | $17.59 |
Common mistakes
- Counting only commission. On zero-commission and spread-only accounts, the spread and slippage are most of the cost.
- Counting the spread twice. Fill prices already include it. Either log P/L at fills and add the spread back for a gross figure (as this sheet does), or log mid prices; not both.
- Forgetting the sale side. SEC and TAF fees apply to sales: the exit of a long, the entry of a short.
- Using an old fee rate. The SEC rate was $0.00 per million for most of fiscal 2026 and $20.60 from 4 April 2026; the TAF is paused for the last quarter of 2026. Rates go by date.
- Ignoring financing. Swaps, margin interest and crypto funding add up on positions held overnight, and they can be credits too.
- Judging costs only in dollars. $5 is small on a $50,000 position and large on a $500 one. Read cost % of notional and cost in R; the ROI calculator shows the same effect on returns.
Limits
- Spread and slippage are only as good as what you type: the sheet cannot see quotes or order books.
- The SEC fee is unrounded; brokers may round or combine it. Your trade confirmation is the final number.
- FINRA does not assess the TAF when the execution price is below the per-share rate; the sheet ignores that edge case.
- One account currency per file. Taxes, data feeds and platform subscriptions are not per-trade costs and are left out unless you put them in Other.
- The monthly tables cover 24 months from the first trade. Recalculation was tested in LibreOffice; the file uses only functions that exist in Excel 2016+ and Google Sheets.
FAQ
How do you calculate trading costs in Excel?
List each cost per trade in its own column, then add them up. Commission = fixed fee per side × 2 + % fee × (entry value + exit value). Spread cost = (ask − bid) × quantity. Slippage = the gap between the price you expected and the fill, on entry and exit. Add regulatory fees, financing and currency conversion. Net P/L = gross P/L − total cost. This template does all of it with one row per trade and adds monthly and per-market totals.
What is the SEC fee on a stock sale?
It is the Section 31 transaction fee. The SEC set it at $20.60 per $1,000,000 of covered sales from 4 April 2026 (it was $0.00 per million before that, from 14 May 2025). Exchanges and FINRA pay it to the SEC, and brokers generally pass it on to customers on sales. A $10,000 sale works out to about $0.21. The rate is reset each fiscal year, so check the SEC's Fee Rate Advisories page.
What is the FINRA TAF fee?
The Trading Activity Fee is a FINRA member fee on sales of covered securities that brokers often pass on. For stocks the 2026 rate is $0.000195 per share sold, capped at $9.79 per trade. FINRA paused the TAF for trades from 1 October to 31 December 2026 and says it resumes on 1 January 2027 at $0.000195 and $9.79.
Is the bid-ask spread a cost if my broker charges zero commission?
Yes. A market buy fills at the ask and a market sell at the bid, so a round trip gives up about one full spread compared with the mid price. It does not show as a fee on your statement because it is inside the fill price. The sheet makes it visible: spread × quantity, plus any slippage you log.
How do you calculate the break-even move after costs?
Divide the total cost by the position size. In price units: total cost ÷ (quantity × FX rate). In percent: total cost ÷ entry value. A $12 round-trip cost on a $10,000 position needs a 0.12% move in your favour just to break even. The sheet shows both for every trade.
Does this trading cost spreadsheet work 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 IF, AND, OR, NOT, ISNUMBER, ISTEXT, N, COUNTA, COUNT, COUNTIF, COUNTIFS, SUMIFS, SUMPRODUCT, INDEX, MIN, MAX, AVERAGE, DATE, YEAR, MONTH, EDATE, which exist in Excel 2016 and later, Google Sheets and LibreOffice. No macros.
Related tools and templates
- ROI calculatorReturn after costs
- Trading journalLog trades and review them
- Day trading journalIntraday trade log
- Trading performance reportWin rate, expectancy and more
- Expense ratio spreadsheetFund fees over time
- Position size calculatorSize a trade from your risk
- Lot size calculatorForex lots from risk
- All templatesFree spreadsheets
Educational template only, not investment, tax or brokerage advice. Example data is fictional: the symbols, prices, fees and results are made up to show the formulas and say nothing about any real strategy, broker or future result. Fee rates change; check the sources before relying on them. Sources: SEC Release 34-104909 and Fee Rate Advisories (sec.gov); FINRA By-Laws Schedule A, Section 1 (finra.org), SR-FINRA-2024-019 and SR-FINRA-2026-021; FINRA Rule 5310; Investor.gov; Delegated Regulation (EU) 2017/565 (EUR-Lex). Retrieved 8 November 2026.