Calculate the redemption yield of a bond via the bisection method and VBA.
The yield to maturity of a bond isn’t given by a simple, explicit equation – you need iterative methods to backsolve the bond pricing formula.
Excel’s RATE function, for example, iteratively calculate bond yields. However, you might want to compute this quantity with VBA instead.
But why would you use VBA when RATE already exists? Well, there are several good reasons.
- You learn theory behind the bisection method and apply the concepts to a practical problem
- You can leverage your new found knowledge to solve other tricky numerical problems, such as calculating implied volatility
- You gain familiarity with VBA – a widely used tool in business and finance (you can’t call yourself an Excel guru without knowing at least a little VBA)
This equation describes the price of a bond.
- C is the coupon
- i is the yield to maturity
- M is the par value
- F is the payment frequency
- n is the number of payments
This equation cannot be rearranged to give i. So here’s some VBA that applies the bisection method to solve this equation for i
Function BondPrice(ParValue As Double, NumberOfPayments As Double, YieldToMaturity As Double, Coupon As Double, Frequency As Double) BondPrice = Coupon / Frequency * (1 - 1 / (1 + YieldToMaturity / Frequency) ^ (NumberOfPayments * Frequency)) / (YieldToMaturity / Frequency) _ + ParValue / (1 + YieldToMaturity / Frequency) ^ (NumberOfPayments * Frequency) End Function Function YieldToMaturity( _ ByVal ParValue As Double, _ ByVal NumberOfPayments As Double, _ ByVal Coupon As Double, _ ByVal Price As Double, _ ByVal Frequency As Double) As Double Dim epsilonABS As Double Dim epsilonSTEP As Double Dim iMid As Double Dim niter As Integer Dim iLower As Double Dim iUpper As Double epsilonABS = 0.0000001 epsilonSTEP = 0.0000001 niter = 0 iLower = 0.0000001 iUpper = 1 Do While niter < 100 _ Or iUpper - iLower >= epsilonSTEP _ Or Abs(BondPrice(ParValue, NumberOfPayments, iLower, Coupon, Frequency) - Price) >= epsilonABS _ And epsilonABS <= Abs(BondPrice(ParValue, NumberOfPayments, iUpper, Coupon, Frequency) - Price) >= epsilonABS iMid = (iLower + iUpper) / 2 If Abs(BondPrice(ParValue, NumberOfPayments, iMid, Coupon, Frequency) - Price) <= epsilonABS Then Exit Do ElseIf ((BondPrice(ParValue, NumberOfPayments, iLower, Coupon, Frequency) - Price) * (BondPrice(ParValue, NumberOfPayments, iMid, Coupon, Frequency) - Price) < 0) Then iUpper = iMid Else iLower = iMid End If niter = niter + 1 Loop YieldToMaturity = iLower End Function
The code contains two functions.
- BondPrice(ParValue, NumberOfPayments, YieldToMaturity, Coupon, Frequency). This calculates the price of a bond.
- YieldToMaturity(ParValue, NumberOfPayments, Coupon, Price, Frequency). This function backsolves the BondPrice to give the yield to maturity.
This Excel spreadsheet implements the VBA, and contrasts the results to the built-in RATE function. The results, of course, agree.
The VBA isn’t locked away – feel free to view, modify and learn from the code.