Implementing Excel and VB's IRR function

Question

I need to port the IRR function found in Excel and VB to ActionScript.

• Excel IRR formula

Any idea how to find the "source" to such functions? does anyone have this implemented in some other c-like language?

Answer 1

This is fairly easy to code using an iterative solver such as the bisection method or Newton's method. If your cash flows C_t_j occur at times t_j then the internal rate of return r satisfies sum j = 1 to n of

C_t_j / (1 + r)^t_j

equals zero. Call this f(r). Then f'(r) is sum j = 1 to n of

-t_j * C_t_j / (1 + r)^(t_j+1).

Now you can apply Newton's method to solve for r.

Answer 2

Here's an IRR Excel macro I wrote many years ago. I can't explain how it works any more but I think it does the right thing:

It is invoked like: =IrrCont(A8:A15,F8:F15) where the first range is a range of dates and the second is a range of values. Some of the values must be positive and some must be negative.

Option Explicit
'
' Internal Rate of return -- Calculation
' Returns a result (Double) or an error message (String)

Private Function IrrCalc(DateRange As Object, ValueRange As Object)
    Dim i As Integer
    Dim it As Integer
    Dim Count As Integer
    Dim u As Double
    Dim time As Double
    Dim d_positive As Double
    Dim positive As Double
    Dim d_negative As Double
    Dim negative As Double
    Dim sum As Double
    Const epsilon As Double = 0.000001
    Const iterations As Integer = 20
    Dim StartTime As Double
    Dim pos As Boolean
    Dim neg As Boolean
    Dim value As Double
    Dim temp As Double
    Dim delta As Double

    If DateRange.Count <> ValueRange.Count Then
      IrrCalc = "*** Date Range (argument 1) and Value Range " & _
          "(argument 2) must contain the same number of cells. ***"
      Exit Function
    End If

    Count = DateRange.Count

    For i = 1 To Count
      If ValueRange.Cells(i).value > 0 Then pos = True
      If ValueRange.Cells(i).value < 0 Then neg = True
      If pos And neg Then Exit For
    Next i

    If Not pos Or Not neg Then
      IrrCalc = "*** Cannot calculate IRR: Need both income and expenditure. ***"
      Exit Function
    End If

    StartTime = Application.Min(DateRange)

    u = 0 ' Initial interest rate guess

    For it = 1 To iterations
      positive = 0
      d_positive = 0
      negative = 0
      d_negative = 0

      For i = 1 To Count
        value = ValueRange.Cells(i).value
        time = (DateRange.Cells(i).value - StartTime) / 365.2425
        If value > 0 Then
          temp = value * Exp(u * time)
          positive = positive + temp
          d_positive = d_positive + temp * time
        ElseIf value < 0 Then
          temp = -value * Exp(u * time)
          negative = negative + temp
          d_negative = d_negative + temp * time
        End If
      Next i
      delta = Log(negative / positive) / (d_negative / negative - d_positive / positive)
      If Abs(delta) < epsilon Then Exit For
      u = u - delta
    Next it

    If it > iterations Then
      IrrCalc = "*** irr does not converge in " & Str(iterations) & " iterations ***"
    Else
      IrrCalc = u
    End If
End Function

' ====================================================================================================
'
' Internal Rate of Return: Discrete interest calculation

Function IrrDiscrete(DateRange As Object, ValueRange As Object)
  Dim result As Variant
  result = IrrCalc(DateRange, ValueRange)
  If VarType(result) = vbDouble Then
    IrrDiscrete = Exp(-result) - 1#
  Else
    IrrDiscrete = result
  End If
End Function

' ====================================================================================================
'
' Internal Rate of Return: Continuous (compounding) interest calculation

Function IrrCont(DateRange As Object, ValueRange As Object)
  Dim result As Variant
  result = IrrCalc(DateRange, ValueRange)
  If VarType(result) = vbDouble Then
    IrrCont = -result
  Else
    IrrCont = result
  End If
End Function

Answer 3

Below is my iterative implementation, in ActionScript:

package xattam.net.math
{
    public class Financial
    {
     public static const MAX_IRR_ITERATIONS:int = 1000;

     public static function IRR(cashFlow:Array,guess:Number=0.1):Number {
      var npv:Number;
      var cnt:Number = 0;
      do
      {
       npv = Financial.NPV(guess,cashFlow);
       guess+= 0.001;

       if(cnt > Financial.MAX_IRR_ITERATIONS) return NaN;
       else cnt++;
      }
      while(npv > 0)

      return guess;
     }
     public static function NPV(discountRate:Number,cashFlow:Array):Number {
      var npv:Number = 0;
      for(var t:int = 0; t < cashFlow.length;t++) {

       npv += cashFlow[t] / Math.pow((1+ discountRate),t);
      }
      return npv;
     }
    } 
}