Question

how do i figure yield on the following 3-year bond 2 years, 2 yr bond 3 years, 4 yr bond 3yrs and 5-year bond 2 years forward using this information? Please, may I have the answers and how to input them into excel to achieve the correct answer? I have tried a few ways that I thought the professor said to do and they are not yielding any results. Thank You

Maturity Yield

1 yr 2.0 %

2 Yr 2.20 %

3yr 3.40 %

4 yr 4.50%

5yr 5.00 %

6yr 5.0%

7 yr 5.20 %

8 yr 5.40

9 yr 5.50 %

10 5.50%

Answer 1

I will start with an example to explain the concept. Post that, we will come to answer your question and excel sheet formula.

Forward Rate:

We already know the annual yields of the bonds (table in your question). So, if you want to invest my $x in bonds for 5 years, then I have many options, like I will list two options to explain the concept

1. Invest $x in 5 year bond:  You will get 5% (form the table in your question) annual return. Which means value of your $x at the end of 5 years will be $x * (1+5%)^⁵

1. Invest $x in 3 year bond and roll over after 2 years: You will get 3.4% (form the table in your question) return. Value of your $x will at the end of 3 years will be $x * (1 + 3.4%)^³. But, as your plan was to invest for 5 years, so you need to invest money $x * (1 + 3.4%)^³ for next 2 years. We know the 2 year spot (current) rate is 2.2% (form the table in your question), but do not know what would be the rate at the end of the 3 year now! If we assume that 2 year rate at the end of 3 year from now is f_2, then f₂ will called forward rate. And value of your $x at the end of 2 years will be $x * (1 + 3.4%)^³ * (1 + f₂)^²

Now, we always expect the market to be ideal and the spot rate reflect the sentiments of the market. Therefore, in our option 1 and 2 above both should give us same end result, so

$x * (1+5%)^⁵ = $x * (1 + 3.4%)^³ * (1 + f₂)^²

$x * (1+5%)^⁵ = $x * (1 + 3.4%)^³ * (1 + f₂)^²

f₂ = [ (1+5%)^⁵ / (1 + 3.4%)^³ ] ^1/2  – 1

= Square Root of [ 1.2762815625 / 1.105507304 ] ^1/2  – 1

= [1.154475921]^1/2 – 1

= 1.074465412 – 1

= 0.074465412 = 7.45%

This is 2 year forward or implied rate after 3 years or 2 year forward rate for a 3 year bond.

3 Year Bond 2 Year Forward (same as my example above)

R3 = 3.4% (from table)

R5 = 5% (from table; 5 year as 3 + 2 forward)

F_{2 (3 year bond}) = [ (1 + R5)^⁵ / (1 + R3)^³]^1/2 -1 = [ (1+5%)^⁵ / (1 + 3.4%)^³ ] ^1/2  – 1 = 7.45%

2 Year Bond 3 Year Forward

R2 = 2.2% (from table)

R5 = 5% (from table; 5 year as 2 + 3 forward)

F_{3 (2 year bond}) = [ (1 + R5)^⁵ / (1 + R3)^³]^1/2 -1 = [ (1+5%)^⁵ / (1 + 2.2%)^² ] ^1/3 - 1 = 1.221925432^1/3 – 1 =

1.069091561 – 1 = 0.069091561 = 6.91%

In the same manner, for other rates can be calculated

4 Year Bond 3 Year Forward

F_{3 (4 year bond}) = [ (1 + R7)^⁷ / (1 + R4)^⁴]^1/3 -1 = [ (1+5.2%)^⁷ / (1 + 4.5%)^⁴ ] ^1/3 - 1 = 6.14%

5 Year Bond 2 Year Forward

F_{2 (5 year bond}) = [ (1 + R7)^⁷ / (1 + R5)^⁵]^1/2 -1 = [ (1+5.2%)^⁷ / (1 + 5.0%)^⁴ ] ^1/2 - 1 = 5.70%

In Excel:

See the picture below, formula indicated by blue arrow for the answer in green cell

Change the Yellow cell and get the answer in green cell