VBA Novice - Calculations are Wrong-CodePudding

Thank you for any help in advance. I'm trying to create a function in excel where other people can input the type of bond, rating, and duration as below. The calculations are kind of close (within 2%) but never exact. When I put the actual calculation into excel I am getting different figures than the code is spitting out. Where am I going wrong?

Function S2Test(Rating As String, RiskCategory As String, Duration As Double)

Dim SDuration2 As Double
SDuration2 = WorksheetFunction.Max(1, Duration)

' 5 to 10 Duration Bucket - need to fix NR
If Rating = "BBB" And RiskCategory = "Corporate" And SDuration2 > 5 < 10 Then
    S2Test = 0.125   (Duration - 5) * 0.015
ElseIf Rating = "AAA" And RiskCategory = "Corporate" And SDuration2 > 5 < 10 Then
    S2Test = 0.045   (Duration - 5) * 0.005
ElseIf Rating = "AA" And RiskCategory = "Corporate" And SDuration2 > 5 < 10 Then
    S2Test = (Duration - 5) * 0.006   0.055
ElseIf Rating = "A" And RiskCategory = "Corporate" And SDuration2 > 5 < 10 Then
    S2Test = 0.07   (Duration - 5) * 0.007
ElseIf Rating = "BB" And RiskCategory = "Corporate" And SDuration2 > 5 < 10 Then
    S2Test = 0.225   (Duration - 5) * 0.025

CodePudding user response：

Adjust the criteria:

If Rating = "BBB" And RiskCategory = "Corporate" And SDuration2 > 5 And SDuration2 < 10 Then
    S2Test = 0.125   (Duration - 5) * 0.015

CodePudding user response：

And extract the commonality:-

Function S2Test(Rating As String, RiskCategory As String, Duration As Double)   
    Dim SDuration2 As Double
    SDuration2 = WorksheetFunction.Max(1, Duration) 
    If RiskCategory = "Corporate" And SDuration2 > 5 And SDuration2 < 10 Then
        If Rating = "BBB" Then
            S2Test = 0.125   (Duration - 5) * 0.015
        ElseIf Rating = "AAA" Then
            S2Test = 0.045   (Duration - 5) * 0.005
        ElseIf Rating = "AA"
            S2Test = (Duration - 5) * 0.006   0.055
        ElseIf Rating = "A"
            S2Test = 0.07   (Duration - 5) * 0.007
        ElseIf Rating = "BB"
            S2Test = 0.225   (Duration - 5) * 0.025
        End If
    End If
End Function