How to Pull Zero Rate Curves in Excel
Pull zero rate curves directly into Excel for discounting, DCF models, and interest rate analysis using BlueGamma's Excel Add-in.
Zero rate curves are essential for financial modelling — whether you're building discount factors, valuing swaps, or projecting interest costs. BlueGamma's Excel Add-in lets you pull zero rates for indices like SONIA, SOFR, and EURIBOR directly into your model.
Common Use Cases
Discount factors — Calculate present values using zero rates derived from the swap curve
Swap valuation — Build zero rate curves to value interest rate swaps
Interest projections — Project future interest costs based on the zero rate curve
Model audits — Pull zero rates as at a specific historical date for audit or reconciliation
Before You Start
To follow this guide, make sure you have:
Installed the BlueGamma Excel Add-in
Signed in through the BlueGamma tab in Excel
An active BlueGamma trial or subscription
If you haven't set this up yet, see:
1. The ZERO_RATE Function
Use the BlueGamma function to fetch zero rates:
index
string
The interest rate index (e.g., SONIA, SOFR, 3M EURIBOR)
date
date/string
The maturity date for the zero rate
valuation_date
string (optional)
The curve date. Defaults to live (today's curve).
Returns: Zero rate as a decimal (e.g., 0.0394 for 3.94%)
Note: This function returns a rate with simple compounding. To convert to a discount factor, use DF = 1 / (1 + rate × years). See Section 6 for details.
2. Pull a Single Zero Rate
To get the zero rate for SONIA maturing on 31 December 2030:
To get the zero rate for 3M EURIBOR maturing on 30 June 2035:
Tip: Multiply by 100 to display as a percentage:
3. Build a Full Zero Rate Curve
Set up your spreadsheet to pull zero rates across multiple maturities.
Setup:
A1
Index
B1
SONIA
Zero Rate Curve (Row 4 = headers, Row 5+ = data):
5
2025-12-31
=BlueGamma.ZERO_RATE($B$1, A5)
6
2026-12-31
=BlueGamma.ZERO_RATE($B$1, A6)
7
2027-12-31
=BlueGamma.ZERO_RATE($B$1, A7)
8
2028-12-31
=BlueGamma.ZERO_RATE($B$1, A8)
9
2029-12-31
=BlueGamma.ZERO_RATE($B$1, A9)
10
2030-12-31
=BlueGamma.ZERO_RATE($B$1, A10)
...
...
...
30
2050-12-31
=BlueGamma.ZERO_RATE($B$1, A30)
This gives you a complete zero rate curve that you can use for discounting or charting.
Example Output (SONIA, as at 31 December 2025):
2026-12-31
3.54%
2027-12-31
3.55%
2028-12-31
3.66%
2029-12-31
3.80%
2030-12-31
3.94%
2035-12-31
4.87%
2040-12-31
5.98%
2045-12-31
7.14%
2050-12-31
8.32%
Need live curves in your financial models? Book a demo and we'll get you set up on a trial. Book a demo →
4. Pull Zero Rates as at a Specific Date
For valuations, audits, or month-end reporting, you often need zero rates as at a specific historical date. Use the valuation_date parameter:
This returns the SONIA zero rate for a 31 December 2030 maturity, using the curve as at 31 December 2025.
Example — Year-End Zero Rate Curve:
A1
Index
B1
3M EURIBOR
A2
Valuation Date
B2
2025-12-31
5
2026-12-31
=BlueGamma.ZERO_RATE($B$1, A5, $B$2)
6
2027-12-31
=BlueGamma.ZERO_RATE($B$1, A6, $B$2)
7
2028-12-31
=BlueGamma.ZERO_RATE($B$1, A7, $B$2)
...
...
...
29
2050-12-31
=BlueGamma.ZERO_RATE($B$1, A29, $B$2)
Example Output (3M EURIBOR, as at 31 December 2025):
2026-12-31
2.06%
2027-12-31
2.17%
2028-12-31
2.31%
2029-12-31
2.45%
2030-12-31
2.59%
2035-12-31
3.24%
2040-12-31
3.91%
2045-12-31
4.48%
2050-12-31
4.94%
5. Build Zero Rate Curves for Multiple Indices
To compare zero rates across indices (e.g., SONIA vs 3M EURIBOR), set up multiple columns:
1
SONIA
3M EURIBOR
2
Maturity
Zero Rate
Zero Rate
3
2025-12-31
=BlueGamma.ZERO_RATE(B$1, $A3)
=BlueGamma.ZERO_RATE(C$1, $A3)
4
2030-12-31
=BlueGamma.ZERO_RATE(B$1, $A4)
=BlueGamma.ZERO_RATE(C$1, $A4)
5
2035-12-31
=BlueGamma.ZERO_RATE(B$1, $A5)
=BlueGamma.ZERO_RATE(C$1, $A5)
6
2040-12-31
=BlueGamma.ZERO_RATE(B$1, $A6)
=BlueGamma.ZERO_RATE(C$1, $A6)
7
2045-12-31
=BlueGamma.ZERO_RATE(B$1, $A7)
=BlueGamma.ZERO_RATE(C$1, $A7)
8
2050-12-31
=BlueGamma.ZERO_RATE(B$1, $A8)
=BlueGamma.ZERO_RATE(C$1, $A8)
Example Output (as at 31 December 2025):
2026-12-31
3.54%
2.06%
2030-12-31
3.94%
2.59%
2035-12-31
4.87%
3.24%
2040-12-31
5.98%
3.91%
2045-12-31
7.14%
4.48%
2050-12-31
8.32%
4.94%
The spread between SONIA (GBP) and 3M EURIBOR (EUR) is approximately 135-340 bps across the curve, reflecting the higher rate environment in the UK versus the Eurozone.
Want to pull live rates directly into Excel? Book a demo and we'll get you set up on a trial. Book a demo →
6. Convert Zero Rates to Discount Factors
BlueGamma zero rates use simple compounding by default. To convert a zero rate to a discount factor:
Where:
r= zero rate (as a decimal, e.g., 0.0394 for 3.94%)t= time to maturity in years
Example in Excel:
A
Maturity Date
2030-12-31
B
Zero Rate
=BlueGamma.ZERO_RATE("SONIA", A5)
C
Years
=YEARFRAC(TODAY(), A5)
D
Discount Factor
=1 / (1 + B5 * C5)
Example calculation (5Y SONIA at 3.94%):
Tip: BlueGamma also provides a dedicated DISCOUNT_FACTOR function if you need discount factors directly — this handles the compounding convention automatically:
Available Indices
Zero rates are available for all major interest rate indices:
SONIA
GBP
Actual/365
Simple
SOFR
USD
Actual/360
Simple
3M EURIBOR
EUR
Actual/360
Simple
6M EURIBOR
EUR
Actual/360
Simple
ESTR
EUR
Actual/360
Simple
CORRA
CAD
Actual/365
Simple
TONA
JPY
Actual/365
Simple
For the full list, see:
Formula Reference
Zero rate (live curve)
=BlueGamma.ZERO_RATE("SONIA", "2030-12-31")
Zero rate (historical curve)
=BlueGamma.ZERO_RATE("SONIA", "2030-12-31", "2025-12-31")
Zero rate (EURIBOR)
=BlueGamma.ZERO_RATE("3M EURIBOR", "2035-06-30")
Zero rate (with cell references)
=BlueGamma.ZERO_RATE($B$1, A5, $B$2)
Ready to pull zero rate curves directly into your model?
Create a free BlueGamma trial and start using the Excel Add-in today.
Last updated
Was this helpful?