xlf | exercise – bonds

Coupon bonds

Exercise: XLF Co has issued a bond with a face value of $100,000 paying an annual coupon of 9%. The bond matures in 10 years from now, the market yield is 8% pa and a coupon has just been paid.


The price of a bond \(P\) is given by the formula \(P=\sum\limits_{j=1}^T \dfrac{C_j} {(1+i)^{t_j}}\) where \(C_j\) is the periodic cash flow, \(T\) is the number of \(j^{th}\) cash flows, and \(i\) is the current periodic yield.

Task 1: In Excel, setup a table with columns showing the timing and magnitude of each coupon, plus the face value of the bond at maturity. Also include columns for the vector of discount factors, and the discounted value of each cash flow. Calculate the present value of the bond. Present the table in suitable business style.


Duration measures the weighted average time to maturity of the cash flows for a bond, and the measure is often used in financial risk analysis.
A common measure of duration is the method documented by Macaulay where duration \( D = \dfrac{ \sum\limits_{j=1}^T {t_j \cdot C_j \cdot (1+i)^{-t_j}}} { \sum\limits_{j=1}^T {C_j \cdot (1+i)^{-t_j}} } \)

Task 2 a: In Excel, setup a table to display the components used in the calculation of the duration measure shown in the equation. This table will be similar to the one developed in task 1, with the addition of the time weight component \(t_j\). Also include one linked cell that allows the user to vary the amount of the face value. Calculate the value of the Macaulay duration measure in years. Present the table in appropriate business style.

Task 2 b: Try different amounts for face value > 0 and examine the resultant change in the Macaulay duration number.