09 Oct Bond valuation and yield curve interpolation Topic: Structure of interest rates – Interpolation FNCE – Purpose: The purpose of this assignment is for you to
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.