# IRR function in PLSQL

We had an AskTOM question recently about how to calculate the IRR, ie, 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, ie, 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 parameterised the function to take a CURSOR variable so that any resultset can be passed to it. As long as the data is pass 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>
```