Understanding Zero-Coupon Yields from Forward-Forward Yields in Excel

A zero-coupon bond is a bond that does not pay any interest during its lifetime. It is sold at a discount from its face value and pays the full face value at maturity. For example, a zero-coupon bond with a face value of $1000 and a maturity of 10 years might be sold for $600 today. The investor who buys the bond will receive $1000 after 10 years, but no interest payments in between.

A forward-forward yield is the expected yield of a bond that will be issued in the future for a certain period of time. For example, a forward-forward yield for a 5-year bond that will be issued in 2 years is the expected yield of that bond when it is issued. It is based on the current market conditions and expectations of future interest rates.

The relationship between zero-coupon yields and forward-forward yields is based on the principle of no arbitrage, which means that there is no risk-free profit opportunity in the market. If two bonds have the same cash flows, they should have the same price. Therefore, the price of a zero-coupon bond with a certain maturity should be equal to the price of a series of forward-forward bonds that have the same maturity and cash flows.

For example, suppose we want to find the zero-coupon yield for a 10-year bond. We can use the forward-forward yields for 1-year, 2-year, 3-year, …, 10-year bonds that will be issued in the future. The price of the zero-coupon bond is the present value of its face value, discounted by the zero-coupon yield. The price of the series of forward-forward bonds is the present value of their face values, discounted by their respective forward-forward yields. By equating these two prices, we can solve for the zero-coupon yield.

Basic Theory

Forward-Forward Yields

Forward-forward yields represent the interest rate applicable to a future period, agreed upon today. It is essentially the forward rate for a future period starting from a future date. The formula for forward-forward yield is as follows:

$F_{t, t_1, t_2} = \left( \frac{1 + Y_{t_2}}{1 + Y_{t_1}} \right)^{\frac{t_2 - t}{t_2 - t_1}} - 1$

Where:

$F_{t, t_1, t_2}$ is the forward-forward yield from time $t$ to $t_2$ with the original forward rate from $t_1$ to $t_2$ .
$Y_{t_1}$ and $Y_{t_2}$ are the spot yields at times $t_1$ and $t_2$ respectively.
$t$ is the current time, $t_1$ is the start time, and $t_2$ is the end time.

Zero-Coupon Yields

Zero-coupon yields, also known as spot yields, represent the yield on a bond with a single cash flow at a specific future date. The formula for zero-coupon yield is:

$Y_{t_1, t_2} = \left( \frac{1}{(1 + F_{t, t_1, t_2})^{\frac{t_2 - t_1}{t_2 - t}}} \right) - 1$

Where:

$Y_{t_1, t_2}$ is the zero-coupon yield from time $t_1$ to $t_2$ .
$F_{t, t_1, t_2}$ is the forward-forward yield from time $t$ to $t_2$ with the original forward rate from $t_1$ to $t_2$ .
$t$ is the current time, $t_1$ is the start time, and $t_2$ is the end time.

Procedures

Collect Data: Gather the spot yields at different time points and the original forward rates.
Calculate Forward-Forward Yields: Use the forward-forward yield formula to calculate the interest rates for future periods.
Derive Zero-Coupon Yields: Utilize the zero-coupon yield formula to obtain the yields for specific time intervals.
Create Excel Spreadsheet: Organize your data in an Excel table for ease of calculation and clarity.

Excel Scenario

Let’s consider the following scenario with spot yields:

$Y_{t_1} = 2\%$ at time $t_1 = 1$ year
$Y_{t_2} = 3\%$ at time $t_2 = 2$ years
Original forward rate from $t_1 = 1$ year to $t_2 = 2$ years is $F_{t, t_1, t_2} = 4\%$

Now, let’s calculate the forward-forward yield and the corresponding zero-coupon yield using the Excel table.

Time (years)	Spot Yield (%)
1	2
2	3

	A	B	C
1	Spot Yield
2	2	3
3	Forward Rate
4		4
5	Forward-Forward
6
7	Zero-Coupon Yield

In cell B5, use the forward-forward yield formula:

= (1 + B2) / (1 + B3) ^ (3 - 1) / (3 - 2) - 1

In cell B7, use the zero-coupon yield formula:

= 1 / (1 + B5) ^ (2 - 1) / (2 - 1) - 1

The values in cells B5 and B7 will give you the forward-forward yield and the corresponding zero-coupon yield.

Results

The forward-forward yield for the period from $t = 1$ to $t_2 = 2$ years is approximately $4\%$ , and the zero-coupon yield for the same period is approximately $3.8462\%$ .

Other Approaches

Excel Functions

Alternatively, you can use Excel functions directly. For the forward-forward yield:

=FV(B4%, B6-B5, , -1)/(1+B5)-1

And for the zero-coupon yield:

=FV(B5%, B7-B6, , -1)/(1+B6)-1

These formulas achieve the same results as the manual calculation.

Solver Add-In

For more complex scenarios, consider using Excel’s Solver Add-In to find the zero-coupon yields iteratively.