Hey guys, this is the first Bond spreadsheet I’m uploading so I thought I’d start off with a relatively simple one – many more to come hopefully. This spreadsheet calculates the Yield to Maturity (YTM) of a non-callable, coupon paying bond.
Below is the link to download my Yield to Maturity spreadsheet:
The spreadsheet adopts Newton’s Method to calculate the Bond’s yield. Newton’s Method is a numerical technique which converges on the yield by first calculating f(x); the difference between the Bond’s calculated price (using an initial guess for the yield) and the Bond’s purchase price (where x is the yield), the end goal being to equate f(x) to zero. And then refining the yield estimate via the following equations:
Where f’(xn) is the derivative of the difference between the Bond’s calculated price and the Bond’s purchase price. This process is then repeated until f(xn) is smaller than the specified tolerance. Convergence here is at least quadratic, meaning the number of correct digits roughly at least doubles each step which is why Newton’s is the favourable method for approximating the YTM.
The spreadsheet requires a number of inputs before the calculation can commence. These inputs are the following:
- Coupon rate as an annual percentage;
- Face value (or par value);
- Years to maturity;
- Number of coupon payments per year – this is typically 2 but the spreadsheet can accommodate any number of coupon payments;
- Current market value of the bond; and
- Desired tolerance which is essentially the users acceptable zero value – once f(x) falls below this value, the calculation will cease (for values below 1e-16, roundoff error is expected).
Once the user has input the required information and initiated the calculation, an approximation for the YTM as an annual percentage is output. The status bar will provide the user with progress information including the number of elapsed iterations and the convergence of the tolerance to that specified by the user.
The spreadsheet will also check the result by calculating the Bond price using the calculated yield and comparing that to the current market price – remember, these are meant to be the same.
Let me know how you go with this one. I’d be interested to hear how suitable the format is. Perhaps users might prefer a format where they can input a number of Bonds and calculate each of their yields in quick succession?