Charts & Visual Data Modeling
Step Chart Formula: Piecewise-Constant Rate Staircase Coordinates & Time-Weighted Average
Plot discrete jumps (central-bank policy rates, SaaS pricing tiers, inventory reorders) accurately as a true Step Chart instead of misleading diagonal slopes, and calculate the exact Time-Weighted Average Rate!
Why standard Line Charts lie about interest rates and inventory — and how duplicated X-vertices create a crisp Step Chart!
When a central bank holds its policy rate at 4.50% from Day 0 until Day 60 and hikes instantaneously to 5.00% on Day 60, a standard Line Chart draws a diagonal ramp implying the rate was 4.75% on Day 30 — a visual falsehood! To render an exact vertical riser on an XY Scatter-with-Straight-Lines chart, each jump date t_k appears TWICE in the plotting table: once holding the prior rate (t_k, r_{k-1}) to finish the horizontal tread, and immediately beneath it at the new rate (t_k, r_k) to draw the vertical step!
Better still, a simple arithmetic average of rate announcements ignores how long each rate stayed in force! Weighting each rate r_k by its holding duration Delta_t_k = t_{k+1} - t_k via SUMPRODUCT(Rates, Durations) / Total_Days yields the exact Time-Weighted Effective Rate across the period!
| Announcement Step k | Start Day t_k | Next Change / End Day t_{k+1} | Holding Days Delta_t_k | Policy Rate r_k (%) | Basis-Point Jump (bps) |
|---|---|---|---|---|---|
| Regime 1 | 0 | 60 | 60 days | 4.50% | — |
| Regime 2 (+50 bps Hike) | 60 | 150 | 90 days | 5.00% | +50 bps |
| Regime 3 (-25 bps Cut) | 150 | 180 | 30 days | 4.75% | -25 bps |
Discrete Step Size in Basis Points: 100 * (r_k - r_{k-1})
Computes the instantaneous vertical riser height at t_2 in basis points (1% = 100 bps).
=ROUND(100 * (C3 - C2), 1)Exact Time-Weighted Average Rate Across Step Intervals
Evaluates SUMPRODUCT(B3:B5 - B2:B4, C2:C4) / (B5 - B2) across step timestamps B2:B5 (with end-of-horizon anchor B5 = 180) and active rates C2:C4.
=IF(OR(B3 < B2, B4 < B3, B5 <= B4), "Step Timestamps Must Be Non-Decreasing With Positive Horizon", ROUND(SUMPRODUCT(B3:B5 - B2:B4, C2:C4) / (B5 - B2), 4))Try the example
Use the original values or change them to test your understanding.