Questions

1. Bootstrapping and Sensitivity Analysis (20 points)

(1) Based on the information provided in Table 1, calculate the prices of the treasury securities assuming par value $1,000. (Note: All Treasury securities are semiannual securities.) (3 points)

(2) Construct the theoretical spot rate curve assuming semiannual compounding. You can use the bootstrapping function I wrote. (10 points)

(3) Draw a chart to show the YTM curve and theoretical spot rate curve. Compare them. (2 points)

(4) Assuming semiannual compounding, calculate the 2-year forward rate 2.5 years from now using the theoretical spot rate curve you estimated in (2). (2 points)

(5) Based on the theoretical spot rate curve you estimated in (2), what should the price of a 3.75% 5-year Treasury security (paid semiannually) with par value $850,000 be? (3 points)

Table 1
Maturity (year)	Coupon Rate	YTM
0.50	0.00%	1.25%
1.00	0.00%	1.75%
1.50	4.25%	1.85%
2.00	4.75%	2.15%
2.50	5.45%	2.35%
3.00	5.85%	2.55%
3.50	6.15%	2.80%
4.00	6.55%	3.25%
4.50	6.95%	3.65%
5.00	7.35%	4.05%

2. Bond pricing and YTM (12 points)

(1) Suppose you wanted to invest in a 25-year bond which pays 3.45% annual coupons and has a face value of $50,000. Determine the fair price of the bond. Note that in order to answer this question you need to interpolate the yields between years 5-10, years 10-15, years 15-20, and years 20-25. (9 points)

(2) Plot on a chart the term structure of interest rates based on the yields given in Table 2 and the yields obtained through interpolations. (1.5 points)

(3) Determine the yield to maturity (YTM) for the bond described in (1). Hint: You can use the built-in function rate( ), Excels Solver, or Goal Seek to figure out the answer. (1.5 points)

Table 2
TTM	1yr	2 yr	3yr	4yr	5yr	10yr	15yr	20yr	25yr
Spot Rate	1.48%	2.07%	2.89%	3.13%	3.42%	4.36%	5.47%	6.48%	7.15%

3. Loan amortization (18 points)

Suppose you obtain a home mortgage loan from Wells Fargo Bank. The loan information is as follows:

Loan amount: $525,000

Interest rate (p.a.): 3.900%

Loan term: 30 years (paid monthly)

Start date (of financial payment): May 1, 2020

(1) Construct the amortization schedule for this mortgage loan. Please make sure you show the monthly payment, the proportion of interest and principal paid, and the ending balance for each scheduled payment date. (10 points)

(2) Without considering the time value of money, estimate the total interests paid during 30 years. (2 points)

(3) Sensitivity analysis: Examine the same 30-year $525,000 mortgage loan. If the mortgage rate per year were 3.250%, 3.375%, 3.500%, 3.625%, 3.750%, 3.875%, 4.000%, 4.125%, 4.250%, 4.375%, 4.500%, 4.625%, 4.750%, 4.875%, or 5.000%, what would be (i) the monthly mortgage payment and (ii) the total interest payments? Please construct a data table to answer the question. (6 points)

Questions 1. Bootstrapping and Sensitivity Analysis (20 points) (1) Based on the information provided in Table 1, calculate the prices of the treasury securities assuming par value $1,000. (Note: All Treasury securities are semiannual securities.) (3 points) (2) Construct the theoretical spot rate curve assuming semiannual compounding. You can use the bootstrapping function I wrote. (10 points) (3) Draw a chart to show the YTM curve and theoretical spot rate curve. Compare them. (2 points) (4) Assuming semiannual compounding, calculate the 2-year forward rate 2.5 years from now using the theoretical spot rate curve you estimated in (2). (2 points) (5) Based on the theoretical spot rate curve you estimated in (2), what should the price of a 3.75% 5-year Treasury security (paid semiannually) with par value $850,000 be? (3 points) Table 1 Maturity (year) Coupon Rate YTM 0.50 0.00% 1.25% 1.00 0.00% 1.75% 1.50 4.25% 1.85% 2.00 4.75% 2.15% 2.50 5.45% 2.35% 3.00 5.85% 2.55% 3.50 6.15% 2.80% 4.00 6.55% 3.25% 4.50 6.95% 3.65% 5.00 7.35% 4.05% 2. Bond pricing and YTM (12 points) (1) Suppose you wanted to invest in a 25-year bond which pays 3.45% annual coupons and has a face value of $50,000. Determine the fair price of the bond. Note that in order to answer this question you need to interpolate the yields between years 5-10 years 10- 15, years 15-20, and years 20-25. (9 points) (2) Plot on a chart the term structure of interest rates based on the yields given in Table 2 and the yields obtained through interpolations. (1.5 points) (3) Determine the yield to maturity (YTM) for the bond described in (1). Hint: You can use the built-in function rate, Excel's "Solver", or "Goal Seek" to figure out the answer. (1.5 points) Table 2 TTM 1yr 2 yr 3 yr 4yr 5yr 10yr 15yr 20yr 25yr | Spot Rate 1.48% 2.07% 2.89% 3.13% 3.42% 4.36% 5.47% 6.48% | 7.15% 3. Loan amortization (18 points) Suppose you obtain a home mortgage loan from Wells Fargo Bank. The loan information is as follows: Loan amount: $525,000 Interest rate (p.a.): 3.900% Loan term: 30 years (paid monthly) Start date (of financial payment): May 1, 2020 (1) Construct the amortization schedule for this mortgage loan. Please make sure you show the monthly payment, the proportion of interest and principal paid, and the ending balance for each scheduled payment date. (10 points) (2) Without considering the time value of money, estimate the total interests paid during 30 years. (2 points) (3) Sensitivity analysis: Examine the same 30-year $525,000 mortgage loan. If the mortgage rate per year were 3.250%, 3.375%, 3.500%, 3.625%, 3.750%. 3.875%. 4.000%, 4.125%, 4.250%, 4.375%, 4.500%, 4.625%, 4.750%, 4.875%, or 5.000%, what would be (i) the monthly mortgage payment and (ii) the total interest payments? Please construct a data table to answer the question. (6 points) Questions 1. Bootstrapping and Sensitivity Analysis (20 points) (1) Based on the information provided in Table 1, calculate the prices of the treasury securities assuming par value $1,000. (Note: All Treasury securities are semiannual securities.) (3 points) (2) Construct the theoretical spot rate curve assuming semiannual compounding. You can use the bootstrapping function I wrote. (10 points) (3) Draw a chart to show the YTM curve and theoretical spot rate curve. Compare them. (2 points) (4) Assuming semiannual compounding, calculate the 2-year forward rate 2.5 years from now using the theoretical spot rate curve you estimated in (2). (2 points) (5) Based on the theoretical spot rate curve you estimated in (2), what should the price of a 3.75% 5-year Treasury security (paid semiannually) with par value $850,000 be? (3 points) Table 1 Maturity (year) Coupon Rate YTM 0.50 0.00% 1.25% 1.00 0.00% 1.75% 1.50 4.25% 1.85% 2.00 4.75% 2.15% 2.50 5.45% 2.35% 3.00 5.85% 2.55% 3.50 6.15% 2.80% 4.00 6.55% 3.25% 4.50 6.95% 3.65% 5.00 7.35% 4.05% 2. Bond pricing and YTM (12 points) (1) Suppose you wanted to invest in a 25-year bond which pays 3.45% annual coupons and has a face value of $50,000. Determine the fair price of the bond. Note that in order to answer this question you need to interpolate the yields between years 5-10 years 10- 15, years 15-20, and years 20-25. (9 points) (2) Plot on a chart the term structure of interest rates based on the yields given in Table 2 and the yields obtained through interpolations. (1.5 points) (3) Determine the yield to maturity (YTM) for the bond described in (1). Hint: You can use the built-in function rate, Excel's "Solver", or "Goal Seek" to figure out the answer. (1.5 points) Table 2 TTM 1yr 2 yr 3 yr 4yr 5yr 10yr 15yr 20yr 25yr | Spot Rate 1.48% 2.07% 2.89% 3.13% 3.42% 4.36% 5.47% 6.48% | 7.15% 3. Loan amortization (18 points) Suppose you obtain a home mortgage loan from Wells Fargo Bank. The loan information is as follows: Loan amount: $525,000 Interest rate (p.a.): 3.900% Loan term: 30 years (paid monthly) Start date (of financial payment): May 1, 2020 (1) Construct the amortization schedule for this mortgage loan. Please make sure you show the monthly payment, the proportion of interest and principal paid, and the ending balance for each scheduled payment date. (10 points) (2) Without considering the time value of money, estimate the total interests paid during 30 years. (2 points) (3) Sensitivity analysis: Examine the same 30-year $525,000 mortgage loan. If the mortgage rate per year were 3.250%, 3.375%, 3.500%, 3.625%, 3.750%. 3.875%. 4.000%, 4.125%, 4.250%, 4.375%, 4.500%, 4.625%, 4.750%, 4.875%, or 5.000%, what would be (i) the monthly mortgage payment and (ii) the total interest payments? Please construct a data table to answer the question. (6 points)