Yield to Maturity in VBA

Here is a VBA function that easily allows you to calculate the yield to maturity on any bond.




Function YTM(Price As Double, ParValue As Double, CouponRate As Double, Frequency As Double, TimeToMaturity As Double)
Dim i As Integer, j As Integer, P(10000) As Double, r(10000) As Double

'Approximates the YTM using the secant method
    P(1) = 0
    r(1) = 1
    For j = 2 To 9999
        For i = 1 To (Frequency * TimeToMaturity)
            P(j) = P(j) + ((CouponRate * ParValue / Frequency) / ((1 + (r(j) / Frequency)) ^ (i)))
        Next i
        P(j) = P(j) + ParValue / ((1 + (r(j) / Frequency)) ^ (Frequency * TimeToMaturity))
        
        If P(j) = P(j - 1) Then
            YTM = r(j)
            Exit For
        Else
            r(j + 1) = r(j) - (P(j) - Price) * (r(j) - r(j - 1)) / _
                (P(j) - (P(j - 1)))
        End If
    Next j
'

End Function