VBA to Calculate Yield to Maturity of a Bond

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.

where

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.

Excel Spreadsheet to Solve Bond Pricing Equation for Yield using Bisection and VBA