Implied financing rate at each AAPL LEAPS strike, versus the 1-year Treasury

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH
    (
        SELECT round(toFloat64(yield_1_year), 2)
        FROM global_markets.treasury_yields
        WHERE date <= (
                SELECT max(date)
                FROM global_markets.options_greeks
                WHERE underlying_symbol = 'AAPL'
        )
        ORDER BY date DESC
        LIMIT 1
    ) AS treasury_1y,
    (
        SELECT round(sum(paid), 4)
        FROM
        (
            SELECT max(toFloat64(cash_amount)) AS paid
            FROM global_markets.stocks_dividends
            WHERE ticker = 'AAPL'
              AND ex_dividend_date <= (
                    SELECT max(date)
                    FROM global_markets.options_greeks
                    WHERE underlying_symbol = 'AAPL'
              )
              AND ex_dividend_date > subtractDays(
                  (
                    SELECT max(date)
                    FROM global_markets.options_greeks
                    WHERE underlying_symbol = 'AAPL'
                  ), 365)
            GROUP BY ex_dividend_date
        )
    ) AS trailing_dividends
SELECT
    strike,
    round(100 * log(strike_k / financed) / term_years, 2)                          AS parity_rate_pct,
    round(100 * log(strike_k / (financed - trailing_dividends)) / term_years, 2)   AS dividend_adjusted_pct,
    treasury_1y                                                                    AS treasury_1y_pct,
    round(10000 * (log(strike_k / (financed - trailing_dividends))
                 - log(strike_k / financed)) / term_years, 0)                      AS dividend_adjustment_bps
FROM
(
    SELECT
        concat('$', toString(toUInt32(strike_price)))                 AS strike,
        toUInt32(strike_price)                                        AS strike_sort,
        toFloat64(strike_price)                                       AS strike_k,
        avg(toFloat64(underlying_close))
          - avgIf(toFloat64(option_close), leg = 'call')
          + avgIf(toFloat64(option_close), leg = 'put')               AS financed,
        max(days_to_expiry) / 365.0                                   AS term_years
    FROM
    (
        SELECT
            strike_price,
            underlying_close,
            option_close,
            days_to_expiry,
            if(lower(toString(option_type)) LIKE 'c%', 'call', 'put') AS leg
        FROM global_markets.options_greeks
        WHERE underlying_symbol = 'AAPL'
          AND date = (
                SELECT max(date)
                FROM global_markets.options_greeks
                WHERE underlying_symbol = 'AAPL'
          )
          AND expiration_date = (
                SELECT expiration_date
                FROM global_markets.options_greeks
                WHERE underlying_symbol = 'AAPL'
                  AND date = (
                        SELECT max(date)
                        FROM global_markets.options_greeks
                        WHERE underlying_symbol = 'AAPL'
                  )
                  AND days_to_expiry >= 200
                  AND toDayOfWeek(expiration_date) = 5
                GROUP BY expiration_date
                ORDER BY abs(toInt32(max(days_to_expiry)) - 450) ASC
                LIMIT 1
          )
          AND toFloat64(option_close) > 0
          AND toFloat64(strike_price) / toFloat64(underlying_close) BETWEEN 0.50 AND 0.95
          AND modulo(toUInt32(strike_price), 10) = 0
    )
    GROUP BY strike_price
    HAVING countIf(leg = 'call') > 0
       AND countIf(leg = 'put') > 0
    ORDER BY strike_price
    LIMIT 12
)
ORDER BY strike_sort