> For the complete documentation index, see [llms.txt](https://www.bluegamma.io/documentation/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.bluegamma.io/documentation/integrations/excel-add-in/how-to-guides/how-to-pull-zero-rate-curves-in-excel.md).

# 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 to the add-in (the **BlueGamma** button on the **Home** tab in Excel)
* An active BlueGamma trial or subscription

If you haven't set this up yet, see:

{% content-ref url="/pages/cIh9SrulGOViAKc9OE27" %}
[Installation & Setup](/documentation/integrations/excel-add-in/installation-and-setup.md)
{% endcontent-ref %}

***

### 1. The ZERO\_RATE Function

Use the BlueGamma function to fetch zero rates:

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

| 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).                      |
| `compounding`    | string *(optional)* | `Simple`, `Compounded` (annual) or `Continuous`. Defaults to `Simple`. |

**Returns:** Zero rate as a decimal (e.g., `0.0394` for 3.94%)

**Note:** By default this function returns a rate with **simple compounding**. To convert it to a discount factor, use `DF = 1 / (1 + rate × years)`. See [Section 6](#6-convert-zero-rates-to-discount-factors) 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   | 2027-12-31        | `=BlueGamma.ZERO_RATE($B$1, A5)`  |
| 6   | 2028-12-31        | `=BlueGamma.ZERO_RATE($B$1, A6)`  |
| 7   | 2029-12-31        | `=BlueGamma.ZERO_RATE($B$1, A7)`  |
| 8   | 2030-12-31        | `=BlueGamma.ZERO_RATE($B$1, A8)`  |
| 9   | 2031-12-31        | `=BlueGamma.ZERO_RATE($B$1, A9)`  |
| 10  | 2032-12-31        | `=BlueGamma.ZERO_RATE($B$1, A10)` |
| ... | ...               | ...                               |
| 28  | 2050-12-31        | `=BlueGamma.ZERO_RATE($B$1, A28)` |

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%     |

***

### 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   | 2027-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)` |

For example output for each index, see the SONIA and 3M EURIBOR tables in sections 3 and 4.

***

### 6. Convert Zero Rates to Discount Factors

BlueGamma zero rates use **simple compounding** by default. To convert a simple 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

{% hint style="info" %}
**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.
{% endhint %}

**Example in Excel:**

| Column | Header          | Formula (Row 5)                     |
| ------ | --------------- | ----------------------------------- |
| A      | Maturity Date   | 2030-12-31                          |
| B      | Zero Rate       | `=BlueGamma.ZERO_RATE("SONIA", A5)` |
| C      | Years           | `=(A5-TODAY())/365`                 |
| 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
```

SONIA uses Actual/365, so the years are days / 365. For an Actual/360 index such as SOFR, divide by 360 instead.

If you prefer a continuously compounded rate, pass `"Continuous"` as the fourth argument and use `DF = EXP(-rate × years)`:

```
=BlueGamma.ZERO_RATE("SONIA", A5, , "Continuous")
```

**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      |
| `TONAR`      | JPY      | Actual/365 | Simple      |

{% hint style="info" %}
**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`.
{% endhint %}

For the full list, see:

{% content-ref url="/pages/V5BVY98UcwQpCdpKqzbj" %}
[Available Indices](/documentation/data-coverage/data-coverage/available-indices.md)
{% endcontent-ref %}

***

### 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)`                        |
| Zero rate (continuous)           | `=BlueGamma.ZERO_RATE("SONIA", "2030-12-31", , "Continuous")` |

***

**Ready to pull zero rate curves directly into your model?**

[Create a free BlueGamma trial](https://auth.bluegamma.io/sign-up) and start using the Excel Add-in today.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://www.bluegamma.io/documentation/integrations/excel-add-in/how-to-guides/how-to-pull-zero-rate-curves-in-excel.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.