A free Excel spreadsheet built for ETF traders who need to track more than just entry and exit prices. This template covers expense ratios, sector exposure, leveraged ETF performance decay, and NAV vs market price analysis — the metrics that matter for swing and position traders rotating across sectors and fund types.
What’s Included
- Trade Log Sheet — Central log with columns for ticker, entry/exit price, shares, ETF category, expense ratio, and sector tag. Pre-formatted for filtering and sorting.
- Expense Ratio Tracker — Aggregates the annualized cost of holding each ETF position, prorated to your actual holding period. Calculates total expense drag on your portfolio.
- Sector Exposure Dashboard — Pie chart and table showing your current and historical allocation across 11 GICS sectors. Updates automatically as you log trades.
- Leveraged/Inverse ETF Sheet — Dedicated tracker for 2x/3x and inverse funds. Compares your ETF’s actual return against the expected leveraged return to quantify compounding decay.
- NAV vs Price Analysis — Daily log for recording closing price and NAV. Highlights premium/discount percentage to help time entries and exits.
- Monthly P&L Summary — Auto-generated charts breaking down realized P&L by sector, ETF type, and holding period. Includes win rate and average gain/loss calculations.
- Position Sizing Calculator — Enter your account size and risk tolerance to get recommended share counts based on stop-loss distance and ETF volatility.
How to Use
Step 1: Log Your ETF Trades
Open the Trade Log sheet and enter each trade starting in row 4. Fill in columns A through L: date, ticker, direction (long/short), entry price, exit price, shares, ETF category (equity, bond, commodity, sector, leveraged), expense ratio, sector tag, and notes. The P&L in column M calculates automatically.
Step 2: Track Sector Exposure
As you add trades, the Sector Exposure tab pulls your open positions and calculates allocation percentages. Use the dropdown in cell B2 to toggle between current positions and all-time history. The pie chart in cells D4:J20 updates in real time.
Step 3: Monitor Leveraged ETF Decay
For any leveraged or inverse ETF, switch to the Leveraged Tracker sheet. Enter the trade date, ETF ticker, leverage factor (2x, 3x, -1x, etc.), and daily closing prices for both the ETF and underlying index. The decay column shows cumulative divergence from expected returns.
Step 4: Review NAV vs Price Spreads
Use the NAV Analysis tab to log end-of-day NAV (available on the fund provider’s website) alongside the ETF’s closing market price. The sheet calculates the premium or discount percentage in column D. A spread above 0.5% on liquid ETFs may signal a timing opportunity — or a warning to avoid entry.
Step 5: Analyze Monthly Performance
At month-end, the Monthly Summary sheet aggregates all closed trades. Review the sector breakdown chart to see where your returns are concentrated. Compare win rates across ETF categories to identify whether your edge is stronger in sector rotation, broad market funds, or leveraged plays.
Key Benefits
- Expense ratio visibility — Most traders ignore fund costs. This sheet surfaces the actual dollar impact of expense ratios on each position, turning a hidden cost into a decision factor.
- Sector rotation tracking — See your allocation shift over time. Useful for swing traders who rotate between sectors based on economic cycles or relative strength.
- Leveraged ETF accountability — Holding TQQQ or SOXL for more than a day? The decay tracker shows exactly how much compounding drag costs you, preventing the common mistake of treating leveraged ETFs as long-term holds.
- NAV premium/discount awareness — Particularly valuable for bond ETFs and international funds where market price can diverge meaningfully from underlying value.
Template vs JournalPlus App
| Feature | This Template | JournalPlus App |
|---|---|---|
| Trade Import | Manual entry | Auto-import from 50+ brokers |
| Expense Ratio Tracking | Manual lookup and entry | Automatic with fund data |
| Sector Exposure | Basic pie chart | Real-time allocation with drill-down |
| P&L Calculations | Formula-based | Real-time with commissions and fees |
| NAV vs Price Analysis | Manual daily entry | Automatic end-of-day sync |
| Analytics | 5 built-in charts | 30+ performance metrics |
| Price | Free | $159 one-time |
This template is a strong starting point for ETF traders who want structured record-keeping without a subscription. When your portfolio grows beyond a handful of positions or you want automatic broker imports and deeper analytics, JournalPlus picks up where the spreadsheet leaves off.
Download
Download the free ETF Trading Journal Spreadsheet and start tracking your trades today. No account required — just open the file in Excel or Google Sheets and begin logging.
Frequently Asked Questions
What fields should an ETF trading journal include?
Beyond standard trade fields like entry price, exit price, and position size, an ETF journal should track expense ratios, fund category, sector exposure, NAV vs market price, and whether the fund is leveraged or inverse. These fields help identify cost drag and tracking errors that eat into returns.
How do you track leveraged ETF decay in a spreadsheet?
Log both the leveraged ETF price and the underlying index value each day you hold the position. Compare the ETF’s actual return against the expected leveraged return (index move × leverage factor) to quantify daily compounding decay over your holding period.
Is an Excel spreadsheet good enough for tracking ETF trades?
A spreadsheet works well for swing and position traders making 5-20 ETF trades per month. It gives full control over custom fields like expense ratios and sector tags. For higher frequency trading or portfolios with 50+ positions, a dedicated journal app with auto-import saves significant time.
How do you calculate true ETF trading costs?
Add the bid-ask spread cost, broker commission, and the annualized expense ratio prorated to your holding period. For a $10,000 position in an ETF with a 0.50% expense ratio held for 3 months, the expense ratio cost alone is approximately $12.50. Track these in a risk management spreadsheet to keep total costs visible.
Should you track NAV vs market price for ETF trades?
Yes. ETFs can trade at a premium or discount to their net asset value, especially in volatile markets or with less liquid funds. Tracking this spread helps avoid buying at inflated premiums and can reveal entry opportunities when funds trade at a discount. Pair this analysis with a regular trade review to refine your timing.
