How to determine Spot Rates and Forward Rates & Yield to Maturity

How to determine Forward Rates from Spot Rates

The relationship between spot and forward rates is given by the following equation:

ft-1, 1=(1+st)t ÷ (1+st-1)t-1 -1

Where

st is the t-period spot rate

ft-1,t is the forward rate applicable for the period (t-1,t)

If the 1-year spot rate is 11.67% and the 2-year spot rate is 12% then the forward rate applicable for the period 1 year – 2 years will be:

f1, 2 = (1+12%)2÷ (1+11.67%)1 -1 = 12.33%

You may calculate this in EXCEL in the following manner:

How to determine Spot Rates from Forward Rates

Alternatively (and equivalently) the relationship between spot rates and forward rates may be given by the following equation:

For example you have been given forward rates as follows:

f0,1 = 11.67%

f1,2 = 12.33%

f2,3 = 12.55%

f3,4 = 12.89%

f4,5 = 13.00%

The 5-year spot rate, s5, will be:

[(1+11.67%)×(1+12.33%)×(1+12.55%)×(1+12.89%)×(1+13.00%)]1/5-1 = 12.49%

You may calculate this in EXCEL in the following manner:

Alternatively you may first calculate accumulation factors, 1+f, for each forward rate as follows:

And then calculate the spot rate as follows:

How to calculate the Yield to Maturity (YTM) of a bond

The equation below gives the value of a bond at time 0. The cash flows of the bond, coupon payments (CP) and Maturity Value (MV = Principal Amount + Coupon payment) have been discounted at the yield-to-maturity (YTM) rate, r, in order to determine the present value of cash flows or alternatively the price or value of the bond (VBond).

For example, you have a 2-year bond with face value (or principal amount) or 100 which will be paid at maturity, i.e. the end of the tenor of the bond. The bond pays an annual coupon at the end of each year of 10. The value of the bond at time 0 is given as 95. Putting these values into the equation above we have:

CP = 10

MV = Face Value + CP = 100 +10 =110

VBond = 95 =10/(1+r) +110/(1+r)2

This may be solved using a trial and error process or the equation may be solved for in EXCEL using the GOAL SEEK functionality.

Trial and error process for calculating YTM of a bond

When r=0%, VBond– Discounted Value = Net Present Value (NPV) = -25.00

When r=15%, NPV = 3.00

Since one value is positive and the other negative the solution lies in the range 0% and 15%.

1. Half the interval. Consider r=7.5% and r=15%.

When r=7.5%, NPV = -9.49

When r=15%, NPV = 3.00

Again since one value is positive and the other negative the solution lies in the range 7.5% and 15%.

1. Again half the interval. Consider r=11.25% and r=15%.

When r=11.25%, NPV = -2.87

When r=15%, NPV = 3.00

Again since one value is positive and the other negative the solution lies in the range 11.25% and 15%.

1. Again half the interval. Consider r=13.125% and r=15%.

When r=13.125%, NPV = 0.20

When r=15%, NPV = 3.00

Since both values are the solution lies in the range 11.25% and 13.125% and not 13.125% and 15%.

1. Half the interval between 11.25% and 13.125%. Consider r=12.1875% and r=13.125%.

When r=12.1875%, NPV = -1.31

When r=13.125%, NPV = 0.20

Again since one value is positive and the other negative the solution lies in the range 12.1875% and 13.125%.

And so on till we arrive at the solution of r = 12.99731%. The complete list of iterations is given in the table below:

EXCEL’s Goal Seek method for calculating YTM of a bond

First set up the input at output cells on the worksheet as follows:

Note that the value of r (the YTM) is initially a dummy value.

Next, set up the Goal Seek functionality. In the Data Tab, go to “What-if Analysis” menu and Click on Goal Seek:

Then enter the values in the Goal Seek pop up window as follows:

• Set cell = NPV
• To Value = 0
• By changing cell = YTM

Click OK to solve for the YTM. The result is as follows:

As you can see Method 2 yields the same results as Method 1 (up to 7 decimal places) and is less cumbersome.

Share
Agnes

Evaluating portfolio performance. A single metric to rule them all?

What is the best way of evaluating portfolio performance allocation strategies? Should we just compare risk, return or risk adjusted…

February 5, 2019 12:51 pm

Project Plain speak – Sense making for the financial world.

6 mins read Introducing Project Plain speak. Currently a work in progress Plain speak focuses on bringing intelligent financial reporting…

January 22, 2019 6:08 am

Oil markets time machine. Past, present and future.

9 mins read What can we learn about oil markets from the last ten years? The next decade. The final…

January 19, 2019 3:47 pm

Signals in the data. Oil prices, tea leaves and crude price direction.

5 mins read What does the data say about future direction of crude oil markets. We look at OPEC spare…

January 19, 2019 12:44 pm

Building a supply side model for crude oil

5 mins read What factors would impact crude oil supply side equation in 2019? Russian non compliance, lower breakeven for…

January 19, 2019 5:14 am

Modeling demand for liquid fuels

5 mins read How would you model demand for crude oil? What are the key components? What is the long…

January 18, 2019 4:46 pm