excelvbairr

Calculate IRR using custom functions with monthly payment

How should I modify the code to calculate the IRR based on the monthly payment? Because the value I obtained from the code does not match with the value I calculated manually via XIRR.

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

For example:

May I know what have I done wrong in my codes or is it possible to use XIRR function without providing the date?

Function CXIRR(TotalPremiumPaid As Double, MonthsOfPayment As Integer, PolicyMonths 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 monthly premium payment
    EachValue = TotalPremiumPaid / (MonthsOfPayment * 12)

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

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

    ' Fill the remaining months with zero (no cash flow)
    For n = MonthsOfPayment To PolicyMonths
        CashFlow(n) = 0
    Next n

    ' Add the final value at the end of the policy
    CashFlow(PolicyMonths) = FinalValue

    ' Provide an initial guess for IRR calculation
    InitialGuess = 0.1 ' 10% as a starting point

    ' Calculate and return the IRR
    CXIRR = WorksheetFunction.IRR(CashFlow, InitialGuess)
End Function

value from CXIRR, 0.43% vs. value from XIRR, 5.34%:

Solution

  • I am not sure that you can get what you want using the inbuilt functions, because of the nature of your cashflows. This is because you are using monthly flows, but mixing them with annualised calculations. The IRR given by XIRR is annualised.

    The following code gives a very good approximation to the answer from XIRR:

    Function CNPV(InitialGuess As Double, TotalPremiumPaid As Double, MonthsOfPayment As Integer, PolicyMonths As Integer, FinalValue As Double) As Double
    
    Dim RunningTotal As Double
    Dim CashFlow() As Double
    Dim n As Integer
    Dim MonthlyRate As Double
    Dim MonthlyPayment As Double

    MonthlyPayment = TotalPremiumPaid / MonthsOfPayment
    MonthlyRate = (1 + InitialGuess) ^ (1 / 12)
    RunningTotal = MonthlyPayment
    
    
    For n = 1 To MonthsOfPayment - 1
        RunningTotal = MonthlyPayment + (RunningTotal * MonthlyRate)
    Next n
    
    RunningTotal = RunningTotal * (1 + InitialGuess) ^ (((PolicyMonths + 1) - MonthsOfPayment) / 12)
    CNPV = (FinalValue - RunningTotal) / (1 + InitialGuess) ^ (((PolicyMonths + 1)) / 12)

End Function

Function CXIRR(TotalPremiumPaid As Double, MonthsOfPayment As Integer, PolicyMonths As Integer, FinalValue As Double) As Double
    
    Dim FirstNPV As Double
    Dim SecondNPV As Double
    Dim FirstGuess As Double
    Dim SecondGuess As Double
    Dim NextGuess As Double
    Dim DiffNPV As Double
    
    FirstGuess = 0.1
    FirstNPV = CNPV(FirstGuess, TotalPremiumPaid, MonthsOfPayment, PolicyMonths, FinalValue)
    If FirstNPV < 0 Then
        SecondGuess = 0.09
    Else
        SecondGuess = 0.11
    End If
    SecondNPV = CNPV(SecondGuess, TotalPremiumPaid, MonthsOfPayment, PolicyMonths, FinalValue)
    
    Do While Abs(SecondNPV) > 1
    
        DiffNPV = SecondNPV - FirstNPV
        NextGuess = SecondGuess - (SecondNPV / DiffNPV) * (SecondGuess - FirstGuess)
        FirstNPV = SecondNPV
        FirstGuess = SecondGuess
        SecondGuess = NextGuess
        SecondNPV = CNPV(SecondGuess, TotalPremiumPaid, MonthsOfPayment, PolicyMonths, FinalValue)
    Loop
    
    CXIRR = SecondGuess
End Function

    By way of explanation. You did not show your full cashflows, but the only way I could recreate your 5.34%, was by having the first premium on month 0, followed by a further 59 payments, with the final payout being on month 240. I have therefore based my code on this assumption.

    First I created a function to calculate the NPV based on the cashflows. To do this I run a loop to compound up the individual payments, to arrive first at a future value after the 60th payment (on month 59). This is done via a running total which is compounded up monthly by the formular (1 + annualised rate) ^ (1 / 12). For 10% seed value, you get a monthly factor of 1.007974, which is less than the 1.008333 which you get by a simple 1 + 0.1 / 12, which would be the correct formular if the interest rate was 10% p.a. monthly (as opposed to 10% p.a. annual). Having thus arrived at an intermediary value, I can compound this up in one straight go to the final date. I then net off the final payment and (again in one go) discount back to the start (Month 0).

    The second routine does an iteration on the first, starting with an initial guess of 10% and a second guess of 9% or 11% (depending on the outcome of the first guess). You could amend this to take a first guess as a parameter, with the second guess being say 90% or 110% of the first guess, again depending on the sign of the first NPV. In case you are interested, I go up or down with my second guess to limit the number of iterations. I know that the second guess must either be higher or lower than the first; I just don't know by how much. But at least I am moving in the right direction.

    Subsequent guesses are calculated according to the degree of accuracy of previous guesses. The third guess is usually an overshoot, due to straight-line interpolation against an exponential function, but the subsequent iterations hone in very quickly on a final answer of 5.345 (as opposed to 5.341 from XIRR).

    Why the difference? My calculation is based on a so-called 30/360 day basis (12 months each with 30 days in a 360 day year). I do not know, but XIRR could be on a so-called Actual/Actual basis or Actual/365F basis (whereby compounding is done on the actual days in the period divided by 365 or the actual number of days in the year in question). If XIRR uses Actual/365F, then a simple adjustment of XIRRs result (5.341 * 365.25/365) leads to 5.3446, very close to my 5.3454. It all depends on how accurate you want to be for your purposes. What is important, is to know on what basis your answer is expressed. If you need an Actual day based system, it would not be hard to amend my first routine to deal with this.