> For the complete documentation index, see [llms.txt](https://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://bluegamma.io/documentation/integrations/excel-add-in/how-to-guides/how-to-calculate-discount-factors-in-excel.md).

# How to Calculate Discount Factors Using Government Bond Yields

Discount factors are essential for present value calculations — whether you're building a DCF model, valuing future cashflows, or pricing fixed income instruments. This guide shows you how to calculate discount factors using US Treasury zero-coupon bond yields directly in Excel.

### Why Use Treasury Yields for Discount Factors?

US Treasury yields are the benchmark **risk-free rate** for USD-denominated valuations:

* **DCF models** — Discount future cashflows at the risk-free rate plus a risk premium
* **Bond pricing** — Value fixed income instruments using government yield curves
* **Derivatives valuation** — Use risk-free rates as the foundation for pricing
* **Project finance** — Establish base discount rates for long-dated cashflows

### 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:

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

***

### Understanding Discount Factors

A discount factor converts a future cashflow to its present value:

$$
\text{Present Value} = \text{Future Cashflow} \times \text{Discount Factor}
$$

The discount factor for a cashflow at time *t* is calculated from the zero-coupon yield:

$$
DF\_t = \frac{1}{(1 + r\_t)^t}
$$

Where:

* **DF** = Discount factor
* **r** = Zero-coupon yield for maturity *t*
* **t** = Time in years to the cashflow

***

### 1. The GOV\_YIELD Function

BlueGamma's `GOV_YIELD` function returns **zero-coupon yields** — exactly what you need for discount factor calculations:

```
=BlueGamma.GOV_YIELD(country_code, maturity, [forward_start], [valuation_date])
```

| Parameter        | Type                | Description                                             |
| ---------------- | ------------------- | ------------------------------------------------------- |
| `country_code`   | string              | ISO 2-letter country code (e.g., `US`)                  |
| `maturity`       | string              | Maturity as a tenor (e.g., `5Y`) or date (`2030-06-15`) |
| `forward_start`  | string *(optional)* | Forward start tenor or date. Defaults to spot.          |
| `valuation_date` | string *(optional)* | Historical valuation date. Defaults to live.            |

**Returns:** Zero-coupon yield as a decimal (e.g., `0.0419` for 4.19%)

***

### 2. Calculate a Single Discount Factor

To calculate the discount factor for a 5-year cashflow using the US Treasury curve:

**Step 1:** Get the 5-year zero-coupon yield:

```
=BlueGamma.GOV_YIELD("US", "5Y")
```

**Step 2:** Calculate the discount factor:

```
=1 / (1 + BlueGamma.GOV_YIELD("US", "5Y")) ^ 5
```

**Example:** With the current 5Y US Treasury yield at 3.74%, the discount factor is:

$$
DF = \frac{1}{(1 + 0.0374)^{5}} = \frac{1}{(1.0374)^{5}} = 0.8314
$$

This means a $100 cashflow in 5 years is worth $83.14 today at the risk-free rate.

{% hint style="info" %}
**Note on Compounding:** BlueGamma yields use semi-annual compounding (market convention), but for practical DCF purposes the difference vs annual compounding is only \~4-6 basis points — negligible for most valuations.
{% endhint %}

***

### 3. Build a Discount Factor Curve

Set up your spreadsheet to calculate discount factors across multiple maturities:

**Setup:**

| Cell | Parameter    | Value |
| ---- | ------------ | ----- |
| B1   | Country Code | US    |

**Discount Factor Schedule (Formulas):**

| Row | A (Tenor) | B (Years) | C (ZC Yield)                      | D (Discount Factor)    |
| --- | --------- | --------- | --------------------------------- | ---------------------- |
| 3   | 1Y        | 1         | `=BlueGamma.GOV_YIELD($B$1, A3)`  | `=1 / (1 + C3) ^ B3`   |
| 4   | 2Y        | 2         | `=BlueGamma.GOV_YIELD($B$1, A4)`  | `=1 / (1 + C4) ^ B4`   |
| 5   | 3Y        | 3         | `=BlueGamma.GOV_YIELD($B$1, A5)`  | `=1 / (1 + C5) ^ B5`   |
| 6   | 5Y        | 5         | `=BlueGamma.GOV_YIELD($B$1, A6)`  | `=1 / (1 + C6) ^ B6`   |
| 7   | 7Y        | 7         | `=BlueGamma.GOV_YIELD($B$1, A7)`  | `=1 / (1 + C7) ^ B7`   |
| 8   | 10Y       | 10        | `=BlueGamma.GOV_YIELD($B$1, A8)`  | `=1 / (1 + C8) ^ B8`   |
| 9   | 20Y       | 20        | `=BlueGamma.GOV_YIELD($B$1, A9)`  | `=1 / (1 + C9) ^ B9`   |
| 10  | 30Y       | 30        | `=BlueGamma.GOV_YIELD($B$1, A10)` | `=1 / (1 + C10) ^ B10` |

**Example Output (as of December 2024):**

| Tenor | ZC Yield | Discount Factor |
| ----- | -------- | --------------- |
| 1Y    | 3.51%    | 0.9661          |
| 2Y    | 3.51%    | 0.9334          |
| 3Y    | 3.56%    | 0.9003          |
| 5Y    | 3.74%    | 0.8314          |
| 7Y    | 3.97%    | 0.7601          |
| 10Y   | 4.25%    | 0.6601          |
| 20Y   | 5.05%    | 0.3739          |
| 30Y   | 5.03%    | 0.2278          |

This gives you a complete discount factor curve based on US Treasury zero-coupon yields.

***

### 4. Discount Factors for Specific Dates

For DCF models, you often need discount factors for exact cashflow dates rather than standard tenors. Use specific maturity dates:

**Setup:**

| Cell | Parameter      | Value      |
| ---- | -------------- | ---------- |
| B1   | Country Code   | US         |
| B2   | Valuation Date | 2024-12-15 |

**Cashflow Schedule:**

| Row | A (Cashflow Date) | B (Years to Cashflow) | C (ZC Yield)                     | D (Discount Factor)  |
| --- | ----------------- | --------------------- | -------------------------------- | -------------------- |
| 5   | 2025-06-15        | `=YEARFRAC($B$2, A5)` | `=BlueGamma.GOV_YIELD($B$1, A5)` | `=1 / (1 + C5) ^ B5` |
| 6   | 2025-12-15        | `=YEARFRAC($B$2, A6)` | `=BlueGamma.GOV_YIELD($B$1, A6)` | `=1 / (1 + C6) ^ B6` |
| 7   | 2026-06-15        | `=YEARFRAC($B$2, A7)` | `=BlueGamma.GOV_YIELD($B$1, A7)` | `=1 / (1 + C7) ^ B7` |
| 8   | 2026-12-15        | `=YEARFRAC($B$2, A8)` | `=BlueGamma.GOV_YIELD($B$1, A8)` | `=1 / (1 + C8) ^ B8` |

The `YEARFRAC` function calculates the exact time in years between your valuation date and each cashflow date.

***

### 5. Complete DCF Valuation Example

Here's a full example: valuing a series of cashflows using Treasury-based discount factors.

**Parameters:**

| Cell | Parameter      | Value      |
| ---- | -------------- | ---------- |
| B1   | Country Code   | US         |
| B2   | Valuation Date | 2024-12-15 |
| B3   | Risk Premium   | 2.00%      |

**DCF Model:**

| Row | A (Date)   | B (Years)             | C (Risk-Free Rate)               | D (Discount Rate) | E (Discount Factor) | F (Cashflow) | G (Present Value) |
| --- | ---------- | --------------------- | -------------------------------- | ----------------- | ------------------- | ------------ | ----------------- |
| 6   | 2025-12-15 | `=YEARFRAC($B$2,A6)`  | `=BlueGamma.GOV_YIELD($B$1,A6)`  | `=C6+$B$3`        | `=1/(1+D6)^B6`      | 10,000       | `=F6*E6`          |
| 7   | 2026-12-15 | `=YEARFRAC($B$2,A7)`  | `=BlueGamma.GOV_YIELD($B$1,A7)`  | `=C7+$B$3`        | `=1/(1+D7)^B7`      | 10,000       | `=F7*E7`          |
| 8   | 2027-12-15 | `=YEARFRAC($B$2,A8)`  | `=BlueGamma.GOV_YIELD($B$1,A8)`  | `=C8+$B$3`        | `=1/(1+D8)^B8`      | 10,000       | `=F8*E8`          |
| 9   | 2028-12-15 | `=YEARFRAC($B$2,A9)`  | `=BlueGamma.GOV_YIELD($B$1,A9)`  | `=C9+$B$3`        | `=1/(1+D9)^B9`      | 10,000       | `=F9*E9`          |
| 10  | 2029-12-15 | `=YEARFRAC($B$2,A10)` | `=BlueGamma.GOV_YIELD($B$1,A10)` | `=C10+$B$3`       | `=1/(1+D10)^B10`    | 110,000      | `=F10*E10`        |

**Total Present Value:**

```
=SUM(G6:G10)
```

This model:

1. Pulls the risk-free rate from the US Treasury curve for each cashflow date
2. Adds a risk premium to get the full discount rate
3. Calculates the discount factor for each period
4. Computes the present value of each cashflow
5. Sums to get the total valuation

***

### 6. Historical Discount Factors

For backtesting or historical valuations, add a valuation date parameter:

```
=BlueGamma.GOV_YIELD("US", "5Y", , "2024-06-30")
```

Note the empty parameter for `forward_start` — use a comma to skip it when you only need the valuation date.

**Example: Historical Discount Factor**

```
=1 / (1 + BlueGamma.GOV_YIELD("US", "5Y", , "2024-06-30")) ^ 5
```

This calculates the 5-year discount factor as of June 30, 2024.

Using the 5Y US Treasury yield of 4.31% from that date:

$$
DF = \frac{1}{(1 + 0.0431)^{5}} = 0.8102
$$

***

### Key Conventions

BlueGamma's government bond yields use the following conventions:

* **Compounding:** Semi-annual (US Treasury market standard)
* **Day Count:** Actual/Actual
* **Output:** Zero-coupon yields (not par yields)

For most practical DCF applications, the difference between semi-annual and annual compounding is only \~4-6 basis points — negligible for valuation purposes. The formulas in this guide use annual compounding for simplicity.

***

### Formula Reference

| Use Case                       | Formula                                                            |
| ------------------------------ | ------------------------------------------------------------------ |
| Single discount factor (tenor) | `=1 / (1 + BlueGamma.GOV_YIELD("US", "5Y")) ^ 5`                   |
| Single discount factor (date)  | `=1 / (1 + BlueGamma.GOV_YIELD("US", A1)) ^ YEARFRAC(TODAY(), A1)` |
| Historical discount factor     | `=1 / (1 + BlueGamma.GOV_YIELD("US", "5Y", , "2024-06-30")) ^ 5`   |
| With risk premium              | `=1 / (1 + BlueGamma.GOV_YIELD("US", "5Y") + $B$1) ^ 5`            |

**Note:** For the "with risk premium" formula, store your risk premium in a cell (e.g., `B1 = 2%` for a 200bp credit spread) and reference it in your formula. This makes it easy to update across your model.

***

### Troubleshooting

| Issue                   | Solution                                                     |
| ----------------------- | ------------------------------------------------------------ |
| #VALUE! error           | Check country code is valid (e.g., "US" not "USA")           |
| Discount factor > 1     | Check your yield is positive and time period is correct      |
| Results don't match     | Verify compounding convention matches your model assumptions |
| Historical data missing | Check the valuation date is a valid business day             |

***

### Next Steps

* [How to Pull Bond Yields in Excel](/documentation/integrations/excel-add-in/how-to-guides/how-to-pull-bond-yields-in-excel.md) — More details on the GOV\_YIELD function
* [Get Government Bond Yield](/documentation/integrations/excel-add-in/functions/get-government-bond-yield.md) — Full function reference
* [Government Bond Curves](/documentation/methodology/government-bond-curves.md) — Methodology details

***

**Ready to build discount factors directly in 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 dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://bluegamma.io/documentation/integrations/excel-add-in/how-to-guides/how-to-calculate-discount-factors-in-excel.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
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.