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:

Installation & Setup

---

1. The ZERO_RATE Function

Use the BlueGamma function to fetch zero rates:

=BlueGamma.ZERO_RATE(index, date, [valuation_date])

Parameter	Type	Description
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:

=BlueGamma.ZERO_RATE("SONIA", "2030-12-31")

To get the zero rate for 3M EURIBOR maturing on 30 June 2035:

=BlueGamma.ZERO_RATE("3M EURIBOR", "2035-06-30")

Tip: Multiply by 100 to display as a percentage:

=BlueGamma.ZERO_RATE("SONIA", "2030-12-31") * 100

---

3. Build a Full Zero Rate Curve

Set up your spreadsheet to pull zero rates across multiple maturities.

Setup:

Cell	Content
A1	Index
B1	SONIA

Zero Rate Curve (Row 4 = headers, Row 5+ = data):

Row	A (Maturity Date)	B (Zero Rate Formula)
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):

Maturity	Zero Rate
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:

=BlueGamma.ZERO_RATE("SONIA", "2030-12-31", "2025-12-31")

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:

Cell	Content
A1	Index
B1	3M EURIBOR
A2	Valuation Date
B2	2025-12-31

Row	A (Maturity Date)	B (Zero Rate Formula)
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):

Maturity	Zero Rate
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:

Row	A (Maturity Date)	B (SONIA)	C (3M EURIBOR)
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):

Maturity	SONIA	3M EURIBOR
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:

$$DF = \frac{1}{1 + r \times t}$$

Where:

• r = zero rate (as a decimal, e.g., 0.0394 for 3.94%)

• t = time to maturity in years

Important: Use the simple compounding formula above, not the annual compounding formula 1/(1+r)^t. BlueGamma returns simple rates for OIS indices (SONIA, SOFR, ESTR) and EURIBOR.

Example in Excel:

Column	Header	Formula (Row 5)
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%):

DF = 1 / (1 + 0.0394 × 5) = 1 / 1.197 = 0.8354

Tip: BlueGamma also provides a dedicated DISCOUNT_FACTOR function if you need discount factors directly — this handles the compounding convention automatically:

=BlueGamma.DISCOUNT_FACTOR("SONIA", "2030-12-31")

---

Available Indices

Zero rates are available for all major interest rate indices:

Index	Currency	Day Count	Compounding
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

Day count matters: When calculating time to maturity for the discount factor formula, use the day count convention for your index. For Actual/365 indices (SONIA, CORRA), use days/365. For Actual/360 indices (SOFR, EURIBOR), use days/360.

For the full list, see:

Available Indices

---

Formula Reference

Use Case	Formula
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.