IRR Function in PL/SQL

We had an AskTOM question recently about how to calculate the IRR, i.e., the Internal Rate of Return. To be honest, I had not really heard of the function, but readers were quick to point out to me that it was a commonly used function in the most "popular" database on earth, namely Microsoft Excel.

It turns out that it is an iterative function, i.e., there is no equation that calculates it directly — you start with an opening guess and then iterate until each guess gets closer to the answer within an accepted tolerance. A little research took me on a nice trip down memory lane to my university (college) days of a maths major where we learned the Newton Raphson method (although apparently it is more commonly referred to as Newton's method).

So a little PL/SQL later, I've re-embraced my youthful mathematical roots. If only we could iterate like this function back to our youth as well.

Here is some test data that we can use

SQL> create table test_tbl
  2  (
  3  id number,
  4  cash_flow number
  5  );

Table created.

SQL>
SQL> insert into test_tbl (id,cash_flow) values (0,-6000000);
SQL> insert into test_tbl (id,cash_flow) values (1,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (2,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (3,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (4,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (5,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (6,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (7,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (8,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (9,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (10,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (11,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (12,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (13,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (14,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (15,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (16,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (17,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (18,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (19,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (20,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (21,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (22,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (23,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (24,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (25,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (26,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (27,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (28,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (29,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (30,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (31,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (32,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (33,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (34,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (35,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (36,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (37,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (38,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (39,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (40,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (41,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (42,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (43,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (44,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (45,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (46,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (47,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (48,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (49,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (50,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (51,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (52,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (53,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (54,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (55,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (56,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (57,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (58,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (59,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (60,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (61,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (62,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (63,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (64,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (65,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (66,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (67,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (68,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (69,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (70,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (71,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (72,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (73,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (74,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (75,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (76,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (77,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (78,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (79,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (80,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (81,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (82,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (83,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (84,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (85,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (86,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (87,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (88,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (89,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (90,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (91,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (92,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (93,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (94,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (95,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (96,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (97,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (98,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (99,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (100,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (101,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (102,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (103,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (104,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (105,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (106,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (107,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (108,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (109,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (110,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (111,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (112,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (113,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (114,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (115,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (116,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (117,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (118,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (119,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (120,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (121,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (122,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (123,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (124,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (125,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (126,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (127,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (128,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (129,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (130,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (131,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (132,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (133,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (134,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (135,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (136,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (137,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (138,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (139,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (140,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (141,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (142,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (143,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (144,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (145,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (146,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (147,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (148,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (149,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (150,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (151,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (152,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (153,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (154,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (155,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (156,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (157,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (158,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (159,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (160,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (161,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (162,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (163,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (164,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (165,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (166,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (167,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (168,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (169,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (170,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (171,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (172,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (173,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (174,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (175,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (176,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (177,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (178,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (179,50243.25);
SQL> insert into test_tbl (id,cash_flow) values (180,54802.38);

And here is the function using Newton's method. You can see in line 15, that we iterate until we get within a nominated threshold. I could code the function just to read my test data above, but that does not make it very practical for an arbitrary set of data. So I've parameterized the function to take a CURSOR variable so that any result set can be passed to it. As long as the data is passed in the correct order, the function should operate just fine.

Warning: Because this is a function that iterates until a threshold is reached if you send in garbage data it is quite possible that the function will either never return, or crash with a numeric overflow error. If you are planning on using the code below, you may want to add some sanity checks to "harden" it for production usage.

SQL> create or replace
  2  function irr(rc sys_refcursor) return number is
  3    type nlist is table of number index by pls_integer;
  4    l_values nlist;
  5
  6    l_threshold number := 0.005;
  7    l_guess number := l_threshold + 1;
  8    l_next_guess number := 2;
  9    l_irr number := 1;
 10
 11  begin
 12    fetch rc bulk collect into l_values;
 13    close rc;
 14
 15    while abs(l_guess) > l_threshold
 16    loop
 17      l_guess := 0;
 18      l_next_guess := 0;
 19      for i in 1 .. l_values.count
 20      loop
 21        l_guess := l_guess + l_values(i)/power(1+l_irr/100, i-1);
 22        l_next_guess := l_next_guess + -i*l_values(i)/power(1+l_irr/100, i-1);
 23      end loop;
 24      l_irr := l_irr - l_guess/l_next_guess;
 25
 26      --dbms_output.put_line('l_irr='||l_irr);
 27      --dbms_output.put_line('l_guess='||l_guess);
 28      --dbms_output.put_line('l_next_guess='||l_next_guess);
 29    end loop;
 30    return l_irr;
 31  end;
 32  /

Function created.

And let’s give it a test.

SQL>
SQL> select irr(cursor(select cash_flow from test_tbl order by id asc )) irr
  2  from dual;

       IRR
----------
.490408759

1 row selected.

SQL>