程序代写｜MSBA7017 Financial Engineering Assignment 1, Spring 2023

Download the fifile bondData.xlsx from Moodle. Below is a description of data:

Complete the following tasks.

(a). Build a spot rate curve from this dataset. To deliver the solution, you need to: (i) specify the spot rate model you use and why you choose this model; (ii) assessment of goodness of fifit; you can do it in the same style as Slide 60 of Topic1a; (iii) plot the price errors and the yield errors. Your choice of model/technique is not necessarily limited to those discussed in this course. You may choose any other model/technique you deem to be reasonable.

To complete this part, you need to compute the model-implied yields given the modelimplied prices. The Excel spreadsheet built-in function YIELD can help you with this. But to use this function, the “bond price” entered should be the clean price.

Specififically, clean price = B(t, T) – Accrued Interest, where B(t, T) is the bond price computed based on the spot rate curve model, and Accrued Interest (AI) is computed as the following:

AI = face value × coupon rate/frequency× t – previous coupon payment date/current coupon period length.

For this assignment, “face value” = 100; “frequency=2” (standing for semiannually paid coupons); “coupon rates” are contained in the column of “Coupon”; “t” is the time stamp “2/17/2023”. “previous coupon payment date” is the coupon payment date immediately before t, which can be retrieved by the spreadsheet function COUPPCD; “current coupon period length” is the time between the coupon payment date immediately after t and the previous coupon payment date, and in particular the coupon payment date immediately after t can be retrieved by the spreadsheet function COUPNCD. For each bond, after AI is computed as described, for a model-implied bond price B(t, T), call the function YIELD to retrieve the model-implied yield. When calling this function, “settlement” is “2/17/2023”, “maturity” is the maturity date in the column “Maturity”, “rate” is the coupon rate contained in column “Coupon”, “pr” is the clean price which equals B(t, T) – AI described above, “redemption” is the face value which equals ✩100; “frequency” equals 2; set “basis” at 1.

As an example for computing model-implied yield described above, look at the fifile “bonds allExamples.xlsm”, the sheet “Ex1 spotCurve p4”. The time stamp is 2/17/2023 set in cell A1. For each bond, column F computes the previous coupon payment date by calling COUPPCD, column G computes the next coupon payment date by calling COUPNCD, column H computes AI according to the formula above. In column J, the function YIELD is called to compute the model-implied yield; in particular, for the argument “pr”, the difffference between column I (containing model-implied bond price) and the column H (containing AI) is entered.

Remark: if you choose Python, there is no readily available function to calculate yield,so you need to implement on your own by solving the equation; the bisection method is straightforward to implement.

(b). Write a function forwardRate to compute f(t, t1, t1 +τ ) with given t, t1 and τ , based on the spot rate curve built in (a). A reminder is that you need the function to have access to s(t, t1) and s(t, t1 + τ ).

(c). Use τ = 0.5 to build a forward rate curve and plot it with the spot rate curve on the same graph. That is, for each t1 ≥ t on the x-axis, the y-axis value for spot rate curve is s(t, t1) and y-axis value for forward rate curve is f(t, t1, t1 + τ ). Comment on the graph.

(d). Using the spot rate curve you built in (a), develop an immunization strategy using bonds in the given dataset for an obligation of amount ✩1 Million that will occur on 18 February 2026, based on Fisher-Weil Duration; you only need to give the number of units to buy for each bond on the current time point (i.e. “2/17/2023”).

(e).* When doing (a), what difficulties did you encounter (e.g. long running time)? And what limitations of your chosen curve fitting techniques are suggested by these difficulties?

You may need calculators to do numerical computations in some of the problems in this section.

Problem 1

(a). We have a bond of face value ✩100 and an annual coupon rate of 2.5%. The coupon payment is made every half year and the face value is paid with the last coupon on the maturity date. That is, on each coupon payment date before the maturity date, the bond pays ✩1.25, and on the maturity date, the payment is ✩101.25. The current time is t, and the next coupon is known to happen on t + 0.25 (i.e. 3 months from now).

The maturity date of this bond is known to be T = t + 2.25. The annual yield now is quoted at 1.88%. Use d = 0.5 (i.e. the length of the reference time interval length is half a year). Compute the current bond price, B(t, T); round the answer to two decimal places.

You might need the formula for geometric sum: a + a 2 + a 3 + · · · + a n = a(1−a n)/1−a .

(b). Current time is t. Given the forward rate curve f(t, t1, t2) (that is, t is fifixed, t1 and t2 span all pairs of (t1, t2) that satisfy t ≤ t1 ≤ t2), recover the spot rate curve s(t, t0 ).

(c). Suppose there is a set of N zero-coupon bonds with maturities T1 < T2 < · · · < TN

(denote their prices now (t) by B0(t, Tn)). That is, the n-th bond makes only one payment on its maturity date Tn (and pays nothing before Tn); suppose the payment of n-th bond is Cn. Can you derive a discretely sampled spot rate curve (that is, obtain the values of s(t, t0 ) for a set of specifific values of t 0 ) such that the bond prices implied by this (discretely sampled) curve matches exactly with the observed prices? If yes,derive this curve using the given zero-coupon bonds; in particular, specify which values are used for t0 and the corresponding spot rates s(t, t0 ). If no, explain why.

(d). Your colleague fifits a spot rate curve (from a certain class of bonds) that has the following expression:

s(t, t0 ) = 0.06 + 0.006(t 0 − t) + 0.001(t 0 − t) 2 − 0.0005(t 0 − t) 3 , t0 ≥ t.

(As usual, for spot rates we use the continuous compounding.) t is the current time.

Some client request a fifixed-income product that will pay ✩5000 on t + 0.25 and ✩10000 on t + 1.5, and you happen to be able to construct these two cash flflows from a pool of bonds that belong to the same class of bonds from which the spot rate curve is fifitted. Compute the price that you will charge the client (before any fees). Suppose you compute the price and quote it to the client, he/she thinks it’s fair and would like to request another fifixed-income product. For this one, the client needs ✩10000 paid on t + 1 and ✩50000 on t + 7. Do you have enough information to set a price for this product? If yes, give the price; if no, explain why.

(e).** The discussion on bonds in this course is static, and changes in the underlying interest rate structure are restricted to be local. Explain.

Problem 2

(a). Current time is t. Recall from (b) of the last problem: a zero-coupon bond makes a one-shot payment which occurs on its maturity date. Show that both the Macaulay duration and Fisher-Weil duration for a zero-coupon bond equal its time to maturity (i.e. T − t). Based on this, argue that a long-term zero-coupon bond is riskier than a short-term one.