Unable to obtain output using excel VBA (when yrs of investment > 25 or when return is less than certain amount)

Question

I am trying to calculate the IRR for insurance policies using VBA. The four input variables are total premium paid, Years of Payment, Number of Policy Years and Final expected value.

I am able to obtain the correct IRR% when the number of Policy Years is 10, but when I change to a higher / lower number of policy years such as 30 or 3, the output is "#VALUE!" as shown in the picture below. Moreover, this issue also appears when the final expected value is less than a certain amount.

picture

picture 2

However, i am able to find the correct IRR value when using the in-built function in excel

IRR excel function

May i know what is causing the limitations in my code? thanks

Function CIRR(TotalPremiumPaid As Double, YearsOfPayment As Integer, PolicyYears As Integer, FinalValue As Double) As Double
    Dim EachValue As Double
    Dim CashFlow() As Double
    Dim n As Integer
    Dim InitialGuess As Double

    ' Calculate the annual premium payment
    EachValue = TotalPremiumPaid / YearsOfPayment

    ' Resize the cash flow array based on the total policy years
    ReDim CashFlow(0 To PolicyYears)

    ' Fill the cash flow array with negative values for the years of payment
    For n = 0 To YearsOfPayment - 1
        CashFlow(n) = -1 * EachValue
    Next n

    ' Fill the remaining years with zero (no cash flow)
    For n = YearsOfPayment To PolicyYears
        CashFlow(n) = 0
    Next n

    ' Add the final value at the end of the policy
    CashFlow(PolicyYears) = FinalValue
    
    ' Provide an initial guess for IRR calculation
    InitialGuess = 0.1 ' 10% as a starting point

    ' Calculate and return the IRR
    CIRR = IRR(CashFlow, InitialGuess)
End Function