How to Calculate Discount Factors Using Government Bond Yields

Calculate discount factors for valuations using US Treasury zero-coupon bond yields directly in Excel with BlueGamma's Excel Add-in.

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:

Installation & Setup

Understanding Discount Factors

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

Present Value=Future Cashflow×Discount Factor\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:

DFt=1(1+rt)tDF_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:

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:

Step 2: Calculate the discount factor:

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

DF=1(1+0.0374)5=1(1.0374)5=0.8314DF = \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.

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.

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:

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:

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

Example: Historical Discount Factor

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=1(1+0.0431)5=0.8102DF = \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

Ready to build discount factors directly in your model?

Create a free BlueGamma trial and start using the Excel Add-in today.

PreviousHow to Pull Bond Yields in ExcelNextFunction Reference

Last updated

Was this helpful?