MATH2FM3
Problem #1:
A 3 year bond with semiannual coupons of 11% has a yield of 18.12%. You are to use Excel's "SumProductl function to compute the duration of this bond. Given two ranges of values, "SumProduct" computes their dot product. Create a table as shown, with entries appropriate to your bond, replacing the question marks with appropriate formulas.
In a single cell, use the functions "SumProduct" and "Sum" to compute the duration of this bond using only the cells shown above.
(a) What is the duration of this bond?
(b) What formula using the functions SumProduct and Sum (and using 37 characters or less) calculates this duration?
Problem #2:
Excel's Solver utility can also find an optimum solution involving more than one variable. We will use Solver to find the best fit of a straight line yield curve for some bond prices (more information on Solver is available in the previous assignment, or in the online tutorial).
You are to calibrate a straight line yield curve where
y(t) = yo+mt
by finding appropriate values for yo and m. The value y(t) is the semiannually compounding bond yield, or IRR, for a bond of maturity t.
The yield curve is to be the best straight-line fit for the following four bonds:
Bond 1 has face value $100 and a term of 1 year with semiannual coupons of 5% and a price of $101.13.
Bond 2 has face value $100 and a term of 1.5 year with semiannual coupons of 8% and a price of $104.19.
Bond 3 has face value $100 and a term of 2 year with a semiannual coupon of 6% and a price of $100.95.
Bond 4 has face value $100 and a term of 5 year with a semiannual coupon of 3% and a price of $82.47.
Create a spreadsheet with headings similar to the following:
In column G, you are to price the bond based on the yield from column F. Column H contains the actual price of the bond. Column I is the difference between the two prices, squared. In cell I13, we have the sum of thel differences squared, and it is this value that we seek to minimize. Use Excel's Solver add-in to simultaneously solve for yo and m to minimize the sum of the differences squared.
(a) What is the value for yo found by Solver?
(b) What is the value for m found by Solver?
Problem #3:
In a previous assignment, Excel wrote a Macro for you by recording your actions. The language the Macro was written in was Visual Basic, or VBA. You can use VBA to program functions or subroutines for yourself. In this exercise you are to write an interpolation function in VBA. You will need to have the "Developer" tab appearing on your ribbon. If it's not there, find "Options" under File or Home, click on "Customize Ribbon" and activate "Developer". Make a yield curve by interpolating and extrapolating from four given terms and yields. Create a table of values on a spreadsheet as shown below, but with the following terms and yields:
Term Yield
1 4.00%
1.5 6.50%
3 3.50%
5 7.50%
Create a straight-line interpolation function in VBA called, FInterp(x, xarray, yarray), that takes as input an array of x values, an array of their matching y values, and the x value for which you want it to interpolate/extrapolate a y value. Instructions for doing this are here.
Create a new table with column headings
Term Interpolated Vlookup true Vlookup false
Under "Term", create a column of terms running from O to 6.0 by increments of 0.2. In the "Interpolated" column, for each term, call your FInterp to interpolate/extrapolate the yield for that term (If your sheet appears like the one above, the xarray input for the interpolator is $B$5:$B$8, while the yarray is $C$5:$C$8).
For comparison, also find what the "vlookup" function produces. In the column beside your "Interpolated" column, use vlookup on the table $B$5:$C$8 for each term, with the optional 4th argument, "Range lookup", set to "TRUE" to have vlookup provide the closest match. In the last column, again use vlookup, but with "Range_lookup" set to "FALSE" so that it returns #N/A if there is no exact match. (note: with vlookup set to false, vlookup may return #N/A even for terms of 3 and 5. The failure to find a match is due to unseen numerical errors somewhere around the 16th decimal place of the value in the "Term" column. Your solution will be accepted even with the #N/As).
(a) What value does your interpolator return for a term of 2.0?
(b) What value does your interpolator return for a term of 0.6?
Problem #4:
Referring to Problem #3 above, select all the data in all four columns of the table that you created, and "Insert" a Scatter Chart plotting all three columns of results against terms from O to 6. For the plot, select the type that indicates each of the points, and connects those with straight lines. Take a screen shot with your graph clearly visible. Paste it into an application (like Paint), and save it as a (.png) file. Upload your screenshot here:
Problem #5:
A zero coupon bond of term 3 years has a continuously compounding yield of 2.17%. A zero coupon bond of term 5 years has a continuously compounding yield of 3.55%.
Use Excel to compute the two year quarterly compounding rate 3 years forward.