Fix Backtesting PnL Errors: Add Real Transaction Costs in 20 Minutes

Stop inflating backtest returns. Learn how to integrate spreads, slippage, and commissions into your trading strategy for accurate profit calculations in 2025.
·
6 min read
·
Mark
·
Machine Learning Python

The Problem That Kept Breaking My Backtest Results

My strategy showed 47% annual returns in backtesting. Live trading? Down 3%.

The culprit: I ignored transaction costs. Spreads, slippage, and commissions demolished my edge. Every tutorial showed clean PnL calculations with perfect fills at mid-price. Real markets don't work that way.

I spent 6 hours debugging this so you don't have to.

What you'll learn:

  • Calculate bid-ask spreads realistically for each trade
  • Model slippage based on order size and volatility
  • Integrate commission structures (flat, tiered, percentage)
  • Compare naïve vs. realistic PnL side-by-side

Time needed: 20 minutes | Difficulty: Intermediate

Why Standard Solutions Failed

What I tried:

  • Flat 0.1% cost per trade - Ignored spread widening during volatility
  • Mid-price execution assumption - Missed the reality that you buy at ask, sell at bid
  • Post-trade adjustment - Too late; needed costs in the decision logic

Time wasted: 6 hours chasing ghost profits

The real issue: transaction costs aren't static. They change with market conditions, order size, and time of day.

My Setup

  • OS: macOS Ventura 13.5
  • Python: 3.11.5
  • Pandas: 2.1.1
  • Trading data: 1-minute bars, US equities

Development environment setup My actual setup showing Python environment with trading libraries

Tip: "I keep a separate conda environment for each strategy version. Saves debugging time when costs models break."

Step-by-Step Solution

Step 1: Calculate Realistic Bid-Ask Spreads

What this does: Replaces mid-price fills with actual bid/ask execution prices based on spread width.

import pandas as pd
import numpy as np

# Personal note: Learned this after my first live trade got filled 0.15% worse than backtest
def calculate_spread_cost(df, spread_bps=10):
    """
    Apply bid-ask spread to entry/exit prices
    
    spread_bps: basis points (10 = 0.10%)
    """
    spread_pct = spread_bps / 10000
    
    # Buy at ask (mid + half spread), sell at bid (mid - half spread)
    df['entry_price_adjusted'] = df['entry_price'] * (1 + spread_pct / 2)
    df['exit_price_adjusted'] = df['exit_price'] * (1 - spread_pct / 2)
    
    # Watch out: Don't apply spread twice if you're already using bid/ask data
    return df

# Example with sample data
trades = pd.DataFrame({
    'entry_price': [100.50, 152.30, 87.90],
    'exit_price': [102.10, 150.80, 89.45],
    'shares': [100, 50, 200]
})

trades = calculate_spread_cost(trades, spread_bps=10)
print(trades[['entry_price', 'entry_price_adjusted', 'exit_price', 'exit_price_adjusted']])

Expected output: Entry prices increase slightly, exit prices decrease, reflecting real execution.

Terminal output after Step 1 My Terminal after adding spread costs - notice the $0.05 difference per share

Tip: "Crypto has 2-5 bps spreads during high liquidity, 50+ bps during crashes. Don't use static spreads."

Troubleshooting:

  • Spread seems too small: US large-caps average 1-10 bps, small-caps 20-100 bps
  • Negative PnL suddenly: Good sign - you're seeing reality

Step 2: Add Slippage Based on Order Size

What this does: Models price impact from your order moving the market against you.

# Personal note: My 1000-share orders moved mid-caps 0.08% on average
def calculate_slippage(df, volatility_col='daily_volatility', size_threshold=1000):
    """
    Slippage increases with order size and volatility
    
    Formula: slippage = base_rate * (order_size / threshold) * volatility_factor
    """
    base_slippage_bps = 5  # 0.05% base
    
    # Size impact: linear scaling
    size_multiplier = df['shares'] / size_threshold
    
    # Volatility impact: higher vol = more slippage
    # Assume 1% daily vol = 1x multiplier, 2% = 2x, etc.
    vol_multiplier = df[volatility_col] / 0.01
    
    slippage_bps = base_slippage_bps * size_multiplier * vol_multiplier
    slippage_pct = slippage_bps / 10000
    
    # Apply slippage on top of spread
    df['entry_price_final'] = df['entry_price_adjusted'] * (1 + slippage_pct)
    df['exit_price_final'] = df['exit_price_adjusted'] * (1 - slippage_pct)
    
    return df

# Add volatility data
trades['daily_volatility'] = [0.012, 0.025, 0.018]  # 1.2%, 2.5%, 1.8% daily vol

trades = calculate_slippage(trades)
print(trades[['entry_price_adjusted', 'entry_price_final']])

Expected output: Larger orders and high-volatility periods show more slippage.

Slippage impact visualization Real data: 100 shares = minimal slippage, 1000 shares = 0.12% average

Tip: "I measure actual slippage from live trades every month and adjust my model. Markets change."

Troubleshooting:

  • Slippage too high: Reduce base_slippage_bps to 2-3 for liquid large-caps
  • Slippage too low: Increase for small-caps or low-volume stocks

Step 3: Integrate Commission Structures

What this does: Adds broker fees (flat, tiered, or percentage-based).

def calculate_commissions(df, commission_type='flat', params=None):
    """
    Three common commission structures:
    - flat: $1 per trade (Interactive Brokers)
    - tiered: Volume-based ($0.005/share under 10k shares/month)
    - percentage: 0.1% of trade value (some crypto exchanges)
    """
    if params is None:
        params = {'flat': 1.0, 'tiered': 0.005, 'percentage': 0.001}
    
    if commission_type == 'flat':
        # $1 per side (entry + exit = $2 total)
        df['commission'] = params['flat'] * 2
    
    elif commission_type == 'tiered':
        # $0.005 per share, both sides
        df['commission'] = df['shares'] * params['tiered'] * 2
    
    elif commission_type == 'percentage':
        # 0.1% of notional value
        entry_notional = df['entry_price_final'] * df['shares']
        exit_notional = df['exit_price_final'] * df['shares']
        df['commission'] = (entry_notional + exit_notional) * params['percentage']
    
    return df

# Using tiered commissions (most common for retail)
trades = calculate_commissions(trades, commission_type='tiered', params={'tiered': 0.005})
print(trades[['shares', 'commission']])

Expected output: Commission scales with order size for tiered, stays flat for flat structure.

Tip: "IBKR Pro charges $0.005/share but caps at 1% of trade value. Always use their fee calculator first."

Step 4: Calculate Realistic PnL

What this does: Compares naïve PnL (no costs) vs. realistic PnL (all costs included).

def calculate_pnl_comparison(df):
    """
    Side-by-side comparison of naïve vs realistic returns
    """
    # Naïve PnL: perfect mid-price execution
    df['pnl_naive'] = (df['exit_price'] - df['entry_price']) * df['shares']
    
    # Realistic PnL: spreads + slippage + commissions
    df['pnl_realistic'] = (df['exit_price_final'] - df['entry_price_final']) * df['shares'] - df['commission']
    
    # Cost breakdown
    df['cost_total'] = df['pnl_naive'] - df['pnl_realistic']
    df['cost_pct'] = (df['cost_total'] / (df['entry_price'] * df['shares'])) * 100
    
    return df

trades = calculate_pnl_comparison(trades)

print("\n=== PnL Comparison ===")
print(trades[['pnl_naive', 'pnl_realistic', 'cost_total', 'cost_pct']])
print(f"\nTotal Naïve PnL: ${trades['pnl_naive'].sum():.2f}")
print(f"Total Realistic PnL: ${trades['pnl_realistic'].sum():.2f}")
print(f"Total Costs: ${trades['cost_total'].sum():.2f} ({trades['cost_pct'].mean():.2f}% avg per trade)")

Expected output: Transaction costs typically eat 0.2-1.5% per round-trip trade.

Performance comparison Real metrics: Naïve PnL $437 → Realistic PnL $289 = 33.9% cost impact

Troubleshooting:

  • Costs over 2% per trade: Check if you're applying spreads twice or using wrong units
  • Negative realistic PnL: Your strategy might only work without costs (very common)

Testing Results

How I tested:

  1. Ran my momentum strategy on SPY 1-min data (Jan-Oct 2024)
  2. Applied three cost models: optimistic (5 bps), realistic (10 bps), pessimistic (20 bps)
  3. Compared against my actual Interactive Brokers statements

Measured results:

  • Naïve backtest: 47.3% annual return
  • With realistic costs: 11.8% annual return
  • My live trading: 9.2% annual return (close enough!)

Key finding: Transaction costs destroyed 75% of my backtest returns. The remaining difference came from execution timing delays.

Final working application Complete cost model showing all components - took 3 weeks to calibrate

Key Takeaways

  • Spreads aren't optional: Even 0.05% per side kills high-frequency strategies
  • Slippage scales non-linearly: Your 100-share backtest won't work with 10,000 shares
  • Commission structure matters: Tiered pricing changes optimal trade sizing
  • Backtest conservatively: Use wider spreads than current market to survive volatility spikes

Limitations: This model assumes normal market conditions. Flash crashes, news events, and low liquidity can 10x your costs instantly.

Your Next Steps

  1. Immediate action: Add spread costs to your next backtest and watch returns drop
  2. Verification step: Compare backtest Sharpe ratio before/after costs

Level up:

  • Beginners: Start with flat 0.15% round-trip cost as first approximation
  • Advanced: Model intraday spread patterns (wider at open/close, tighter mid-day)

Tools I use:

  • QuantConnect: Free backtesting with built-in slippage models - quantconnect.com
  • Alpaca API: Commission-free trades but you still pay spreads - alpaca.markets