I Introduction to internal rate of return (IRR)

The Internal Rate of Return (IRR) is when NPV (net present value) is equal to zero Discount rate . To calculate the internal rate of return, several discount rates are used for trial calculation until the discount rate with net present value equal to or close to zero is found. Internal rate of return is the expected rate of return of an investment project.

It is the desired rate of return for an investment. The larger the index, the better. Generally speaking, when the internal rate of return is greater than or equal to the benchmark rate of return of the market, the project is feasible, but it will cause losses anyway. The sum of the product of cash flow and discount rate in each year of the investment project is the net present value of the project. When the net present value is zero, the discount rate is the internal rate of return of the project.

XIRR needs to be accurate to specific days, which is more accurate than IRR.

II Algorithmic thought

Algorithm formula:

 

Ci is the paid in cash flow of each transaction

di is the date when each paid in cash flow of investment occurs

d1 is the first paid in date

Rate is internal gross rate of return - Gross IRR

The same as the Excel algorithm formula, Rate is the discount Rate when XNPV is 0, and the sum of this formula is XNPV.

The simplest algorithm idea to find out the Rate when XNPV=0 is to find in half. Give a guess value and bring it into the formula for continuous iteration. Finally, find the required value. This value needs to meet the verification of the formula. It can make the accuracy of XNPV less than 1E-15, that is, it is approximately 0.

III code implementation

1.JAVA implementation

public double xirr(double [] values, Date [] dates, double guess)
	{
		double 	result 					= Double.NaN;			
		double 	irrGuess 				= 0.1D;	
		double 	sumCashFlows 			= 0.0D;					
		boolean wasHi 					= false;				
		double 	npv 					= 0.0D;					
		int 	negativeCashFlowCount	= 0;					
		int		positiveCashFlowCount	= 0;					
		
		if (values == null || values.length == 0) return result;
		if (dates == null || dates.length == 0) return result;
		if (values.length != dates.length) return result;
			
		
		for (int i = 0; i < values.length; i++) 
		{
			sumCashFlows += values[i];
			if (values[i] > 0.0)
			{
				negativeCashFlowCount++;
			} else if (values[i] < 0.0)
			{
				positiveCashFlowCount++;
			}
		}
		
		if(negativeCashFlowCount <= 0 || positiveCashFlowCount <= 0) return result;
			
		if (!Double.isNaN(guess)) 
		{
			irrGuess = guess;
			if (irrGuess <= 0.0) irrGuess = 0.5;
		}
		
		double irr = sumCashFlows < 0 ? -irrGuess : irrGuess;
		
		for (int i = 0; i <= MAX_ITERATION; i++) 
		{
			
			npv = getXirr(irr, values, dates);
			
			if (Math.abs(npv) < MIN_VALUE)
			{
				result = irr;
				break;
			}
			
			if (npv > 0.0) 
			{
				if (wasHi) irrGuess /= 2;
				irr += irrGuess;
				if (wasHi) 
				{
					irrGuess -= min_accuracy;
					wasHi = false;
				}
			} else 
			{
				irrGuess /= 2;
				irr -= irrGuess;
				wasHi = true;
			}

			if (irrGuess <= min_accuracy) 
			{
				result = irr;
				break;
			}
		}
		
		return result;
	}

	public double getXirr(final double guess, double [] values, Date [] dates)
	{
		double result = 0.0D;
		
		for (int i = 0; i < dates.length; i++) 
		{
			result += values[i] / Math.pow(1 + guess, DateUtil.getIntervalDays(dates[i], dates[0]) / FULL_YEAR_DAYS);
		}
		return result;
	}

2. Rewrite to PLSQL function

CREATE OR REPLACE FUNCTION "FIND_XIRR" (p_date_array   in p_date_array,
                                  p_amount_array in t_amount_array,
                                  p_guess        in number default 0)
  RETURN NUMBER IS

BEGIN
  declare
    rtn_err    number := -9999999;
    step number := 0;
    v_sum       number;
    guess  number := 0.5;
    wasHi      number := -1;
    d       number := 0;
    returnd number := 0;

  begin

    d := 0;
    v_sum := 0;
    for i in 1 .. p_amount_array.count loop
      v_sum := v_sum +
              p_amount_array(i) /
              power((1 + d), (p_date_array(i) - p_date_array(1)) / 365);
    end loop;

    if (v_sum > 0) then
      d := guess;
    else
      d := (-1) * guess;
    end if;

    step := 100;

    loop
      v_sum := 0;

      for i in 1 .. p_amount_array.count loop
        v_sum := v_sum +
                p_amount_array(i) /
                power((1 + d), (p_date_array(i) - p_date_array(1)) / 365);
      end loop;
      if (v_sum > 0) then
        if (wasHi > 0) then
          guess := guess / 2;
        end if;
        d := d + guess;
        if (wasHi > 0) then
          guess := guess - 0.000000000000001;
          wasHi     := -1;
        end if;
      else
        guess := guess / 2;
        d      := d - guess;
        wasHi     := 0;
      end if;

      if (abs(v_sum) < 0.000000000000001) then
        returnd := d;
        exit;
      end if;
      step := step - 1;
      if (step = 0) then
        returnd := d;
        exit;
      end if;
    end loop;

    return returnd;
  EXCEPTION
    WHEN OTHERS THEN
      return rtn_err;
  end;
END FIND_XIRR;

It should be noted that the input cash flow array must meet a positive cash flow and a negative cash flow. The date type is in YYYYMMDD format, which does not meet the need for type conversion. For cash flow, reasonable test data should be input, otherwise the XIRR value that meets the jump out cycle condition cannot be found.

Test data:

P_DATE_ARRAY[2016/9/26,2018/7/2,2020/3/31]

P_AMOUNT_ARRAY[1000000000,18500000,-1249940250]

RETURN_XIRR:0.06057268

There is almost no error with the calculated value in Excel.