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.
Pricing
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.
Alternatively, \(P= { \sum\limits_{t=1}^N {CF_t \cdot (1+i)^{-t}} } \) in the spirit of Saunders and Cornett (2008)
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
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}} } \)
Or, expressed in the nomenclature of Saunders and Cornett (2008, p225) \( D = \dfrac{ \sum\limits_{t=1}^N {CF_t \cdot (1+i)^{-t} \cdot t}} { \sum\limits_{t=1}^N {CF_t \cdot (1+i)^{-t}} } = \dfrac{ \sum\limits_{t=1}^N {PV_t \cdot t}} { \sum\limits_{t=1}^N {PV_t }} \)
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.
- format(‘U’) < $release->format(‘U’) ) {
- The Excel solution file for this module: xlf-fin-exercise-bonds.xlsm 26KB
- The Excel solution file for this module: xlf-fin-exercise-bonds.xlsx 22KB [6 May 2019]
- This module was developed in Excel 2013 Pro 64 bit.
- Saunders A, and M Cornett, (2008), Financial Institutions Management, a Risk Management Approach, McGraw-Hill Irwin
echo “The solution link will open at ” . $release->format(‘g:i a \o\n l jS F Y’) . “ – [Australian Eastern Standard Time (EST)] “;
}
else
{
echo “
“;
echo “
“;
echo “
“;
echo “
“;
}
?>