Case study 02 – Bond valuation and yield curve interpolation Topic: Structure of interest rates – Interpolation FNCE –
Purpose: The purpose of this assignment is for you to value a bond using a yield curve where you must interpolate missing values.
Directions: Using the Case02_ExcelTemplate, follow the guidelines below to interpolate the yield curve and complete the bond valuation.
Step 1: Go to https://www.treasury.gov/resource-center/data-char… and fill in the yield curve (blue-font cells in column C). The date you must use is 09/01/2023.
Step 2: Complete the linear interpolation in column D.
Step 3: Complete the Nelson-Siegel model (as per video in brightspace). The final step is to find the value of a bond with 12 years to maturity, has an annual coupon payment and coupon rate of 7%. Par value is $1,000.
Step 4: Fill in cells L16 through W17 with the bond cash flows. Do the same for cells L24 through W24.
Step 5: Using the linear interpolated yield curve, fill in cells L17 through W17. Using the Nelson-Siegel yield curve, fill in cells L25 through W25. The bond prices will automatically calculate for you.
Template to fill in
| As of 9/1/2023 | ||||||||||||||||||||||
| ttm (years) | Treasury yield | Linear Interpolation | Nelson-Siegel model | Squared residual for N-S | ||||||||||||||||||
| 1 | 0.12% | 0.12% | 0.0000 | alpha1 | 1 | |||||||||||||||||
| 2 | 0.13% | 0.13% | 0.0000 | alpha2 | 1 | |||||||||||||||||
| 3 | 0.14% | 0.14% | 0.0000 | alpha3 | 1 | |||||||||||||||||
| 4 | beta | 2 | ||||||||||||||||||||
| 5 | 0.26% | 0.26% | 0.0000 | |||||||||||||||||||
| 6 | Sum of squared residuals | 0.00042774 | ||||||||||||||||||||
| 7 | 0.46% | 0.46% | 0.0000 | |||||||||||||||||||
| 8 | ||||||||||||||||||||||
| 9 | ||||||||||||||||||||||
| 10 | 0.68% | 0.68% | 0.0000 | |||||||||||||||||||
| 11 | ||||||||||||||||||||||
| 12 | Linear model | |||||||||||||||||||||
| 13 | Period (annual) | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |||||||||
| 14 | Bond Cash flows | |||||||||||||||||||||
| 15 | Discount Rate | |||||||||||||||||||||
| 16 | Present Value | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||
| 17 | ||||||||||||||||||||||
| 18 | Bond Price | $ – 0 | ||||||||||||||||||||
| 19 | ||||||||||||||||||||||
| 20 | 1.20% | 1.20% | 0.0001 | NS model | ||||||||||||||||||
| 21 | Period (annual) | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |||||||||
| 22 | Bond Cash flows | |||||||||||||||||||||
| 23 | Discount Rate | |||||||||||||||||||||
| 24 | Present Value | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||
| 25 | ||||||||||||||||||||||
| 26 | Bond Price | $ – 0 | ||||||||||||||||||||
| 27 | ||||||||||||||||||||||
| 28 | ||||||||||||||||||||||
| 29 | ||||||||||||||||||||||
| 30 | 1.43% | 1.43% | 0.0002 |
,
Case study 02 – Bond valuation and yield curve interpolation
Topic: Structure of interest rates – Interpolation
FNCE 4430 –
Purpose: The purpose of this assignment is for you to value a bond using a yield curve where
you must interpolate missing values.
Directions: Using the Case02_ExcelTemplate, follow the guidelines below to interpolate the
yield curve and complete the bond valuation.
Step 1: Go to https://www.treasury.gov/resource-center/data-chart-center/interest-
rates/pages/textview.aspx?data=yield and fill in the yield curve (blue-font cells in
column C). The date you must use is 09/01/2023.
Step 2: Complete the linear interpolation in column D.
Step 3: Complete the Nelson-Siegel model (as per video in brightspace).
The final step is to find the value of a bond with 12 years to maturity, has an annual coupon
payment and coupon rate of 7%. Par value is $1,000.
Step 4: Fill in cells L16 through W17 with the bond cash flows. Do the same for cells L24
through W24.
Step 5: Using the linear interpolated yield curve, fill in cells L17 through W17. Using the
Nelson-Siegel yield curve, fill in cells L25 through W25. The bond prices will
automatically calculate for you.
