Create Yield to maturity formula in VBA

[Spurious]

Hello guys,

I got a question regarding some financial maths:

I want to write a function in VBA that calculates the following:

C: coupon
F: face value
P: price
y: Yield to maturity

P = C/(1+y)^1 + C/(1+y)^2 + C/(1+y)^3 + ... + C/(1+y)^n + F/(1+y)^n

Now let's assume C, F and P are given, I want to obtain y with a VBA tool, what's my play?

I dont want to use regular Excel.

Thanks in advance.

 

[Spurious]

No one?

 

[JoeMo]

Can you give some typical values of P, C, F ... and what about n, how does it get set or determined?

 

[Spurious]

Face value (F) = 1000
Coupon (%) (C) = 8%*F
Current price (P) = 1050
Maturity = 3

Btw, I forgot to mention that:
VERY IMPORTANT!

I want to solve it for y.

 

[JoeMo]

Largely untested. I have 4 named cells on the active sheet. The names are Price, FaceValue, CouponRate, and YearsToMaturity. These cells contain the obvious inputs. The Coupon rate can be entered as a % or a decimal value (e.g. 8% = 0.08).

]
Sub YieldToMaturity()
Dim F As Double, Cr As Double, Cv As Double, N As Integer, P As Double, Dif As Double
Dim Inc As Double, Ct As Long, YTM As Double, x As Double, curYld As Double
With ActiveSheet
    F = .Range("FaceValue")
    Cr = .Range("CouponRate").Value
    Cv = F * Cr
    P = .Range("Price")
    N = .Range("YearsToMaturity")
    curYld = Cv / P
    Inc = 0.00001
    'First pass trial value for YTM
    Select Case curYld
        Case Is > Cr
        YTM = curYld + Inc
        Do
            Ct = Ct + 1
            x = 1 + YTM
            Dif = P - (Cv * SumIt(N, x) + F / x ^ N)
            YTM = YTM + Inc
        Loop While Ct <= 100000 And Dif < 0
        
        Case Is < Cr
        YTM = curYld - Inc
        Do
            Ct = Ct + 1
            x = 1 + YTM
            Dif = P - (Cv * SumIt(N, x) + F / x ^ N)
            YTM = YTM - Inc
        Loop While Ct <= 100000 And Dif > 0
    End Select
End With
MsgBox "YTM is: " & Round(YTM * 100, 2) & "%" & vbNewLine & "After " & Ct & " iterations"
End Sub

Function SumIt(N As Integer, x As Double) As Double
For k = 1 To N
    SumIt = SumIt + 1 / (x) ^ k
Next k

End Function

The answer I get for the values you posted is YTM = 6.12%

 

[JoeMo]

Apologies, in my earlier post I forgot to include the trivial case where the current yield and coupon rate are the same (i.e. Price = FaceValue):

Sub YieldToMaturity()
Dim F As Double, Cr As Double, Cv As Double, N As Integer, P As Double, Dif As Double
Dim Inc As Double, Ct As Long, YTM As Double, x As Double, curYld As Double
With ActiveSheet
    F = .Range("FaceValue")
    Cr = .Range("CouponRate").Value
    Cv = F * Cr
    P = .Range("Price")
    N = .Range("YearsToMaturity")
    curYld = Cv / P
    Inc = 0.00001
    'First pass trial value for YTM
    Select Case curYld
        Case Is > Cr
        YTM = curYld + Inc
        Do
            Ct = Ct + 1
            x = 1 + YTM
            Dif = P - (Cv * SumIt(N, x) + F / x ^ N)
            YTM = YTM + Inc
        Loop While Ct <= 100000 And Dif < 0
        
        Case Is < Cr
        YTM = curYld - Inc
        Do
            Ct = Ct + 1
            x = 1 + YTM
            Dif = P - (Cv * SumIt(N, x) + F / x ^ N)
            YTM = YTM - Inc
        Loop While Ct <= 100000 And Dif > 0
        Case Else
            YTM = curYld
    End Select
End With
MsgBox "YTM is: " & Round(YTM * 100, 2) & "%" & vbNewLine & "After " & Ct & " iterations"
End Sub

Function SumIt(N As Integer, x As Double) As Double
For k = 1 To N
    SumIt = SumIt + 1 / (x) ^ k
Next k

End Function

 

[Spurious]

Thank you very much!

Going to look into it.