Calculate IRR using custom functions in Excel VBA

Question

I would like to calculate the IRR of my savings insurance based on the total premium paid, years of payment, policy years, and the value received at the end of policies.

For example:

total premium paid = 50,000
years of premium payment = 5
policy years = 20
value received at the end of 20 years = 125,000

I know it is achievable using the IRR function, however I dont want to change the number of rows, my payment per year and etc every time when I want to make a comparison.

I have found smtg similar which is answered Here, however it does not exactly fulfill my requirement. How can i modify the VBA code to suit my need??

Function CIRR(TotalPremiumPaid, timePeriod, finalValue)
    
    eachValue = TotalPremiumPaid / timePeriod
    Dim cashFlow() As Double
    Dim n As Integer
    ReDim cashFlow(0 to timePeriod)
    
        For n = 0 To (timePeriod - 1)
            cashFlow(n) = -1 * eachValue
        Next n

    cashFlow(timePeriod) = finalValue
    CIRR = IRR(cashFlow)
 
End Function

Thanks a lot

ErnestHub · Answer 1 · 2023-08-29T07:19:19.707

Insurance policies are paid starting from year 0, however it matures at year 20 as intended. There is no need to extend the IRR calculation to year 21

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
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

output compared with using excel built-in function

Calculate IRR using custom functions in Excel VBA

1 Answers1