Posted by We had a request on AskTOM a few days ago asking for an implementation of the XIRR function in PL/SQL.
I didn’t really know much about what that function was, or what it did, but a quick web search yielded plenty of examples in Excel, where it is a pre-delivered function, as described here:
“Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.”
That explains what the function does, but doesn’t explain how the function result is derived. So I pushed back to the author of the question saying that if they could give us an algorithm for the function, then we could take a look at implementing the algorithm in PL/SQL. After some research they came back with the following diagram taken from: http://maestro.ipcc.ca/files/rate_of_return/XIRR_ROR.pdf
The formula with some cool mathematical symbols on the page looks like an algorithm but it is not – the real nuts and bolts is in bullet point “3”. There is no “formula” as such for XIRR – we need an iterative approach, namely, try an initial set of conditions that yields an answer “close” to the desired result, but if not, iterate continuously using previous results to slowly “narrow in” on the result until we meet some nominated tolerance value. This was starting to sound complicated 
Luckily, our author also posted a link to a solution that has been written in C##. So I posed myself the question – “How hard is it to port from C##…” (a language I have never used professionally as a developer) “… run in the database as PL/SQL?”. After all, often we hear that database-centric code cannot be tackled in an organization because the task of understanding PL/SQL as a non-PL/SQL developer is simply “too onerous”.
Here is the original C## code.
using System;
using System.Collections.Generic;
using System.Linq;
namespace Xirr
{
public class Program
{
private const Double DaysPerYear = 365.0;
private const int MaxIterations = 100;
private const double DefaultTolerance = 1E-6;
private const double DefaultGuess = 0.1;
private static readonly Func, Double> NewthonsMethod =
cf => NewtonsMethodImplementation(cf, Xnpv, XnpvPrime);
private static readonly Func, Double> BisectionMethod =
cf => BisectionMethodImplementation(cf, Xnpv);
public static void Main(string[] args)
{
RunScenario(new[]
{
// this scenario fails with Newton's but succeeds with slower Bisection
new CashItem(new DateTime(2012, 6, 1), 0.01),
new CashItem(new DateTime(2012, 7, 23), 3042626.18),
new CashItem(new DateTime(2012, 11, 7), -491356.62),
new CashItem(new DateTime(2012, 11, 30), 631579.92),
new CashItem(new DateTime(2012, 12, 1), 19769.5),
new CashItem(new DateTime(2013, 1, 16), 1551771.47),
new CashItem(new DateTime(2013, 2, 8), -304595),
new CashItem(new DateTime(2013, 3, 26), 3880609.64),
new CashItem(new DateTime(2013, 3, 31), -4331949.61)
});
RunScenario(new[]
{
new CashItem(new DateTime(2001, 5, 1), 10000),
new CashItem(new DateTime(2002, 3, 1), 2000),
new CashItem(new DateTime(2002, 5, 1), -5500),
new CashItem(new DateTime(2002, 9, 1), 3000),
new CashItem(new DateTime(2003, 2, 1), 3500),
new CashItem(new DateTime(2003, 5, 1), -15000)
});
}
private static void RunScenario(IEnumerable cashFlow)
{
try
{
try
{
var result = CalcXirr(cashFlow, NewthonsMethod);
Console.WriteLine("XIRR [Newton's] value is {0}", result);
}
catch (InvalidOperationException)
{
// Failed: try another algorithm
var result = CalcXirr(cashFlow, BisectionMethod);
Console.WriteLine("XIRR [Bisection] (Newton's failed) value is {0}", result);
}
}
catch (ArgumentException e)
{
Console.WriteLine(e.Message);
}
catch (InvalidOperationException exception)
{
Console.WriteLine(exception.Message);
}
}
private static double CalcXirr(IEnumerable cashFlow, Func, double> method)
{
if (cashFlow.Count(cf => cf.Amount > 0) == 0)
throw new ArgumentException("Add at least one positive item");
if (cashFlow.Count(c => c.Amount < 0) == 0)
throw new ArgumentException("Add at least one negative item");
var result = method(cashFlow);
if (Double.IsInfinity(result))
throw new InvalidOperationException("Could not calculate: Infinity");
if (Double.IsNaN(result))
throw new InvalidOperationException("Could not calculate: Not a number");
return result;
}
private static Double NewtonsMethodImplementation(IEnumerable cashFlow,
Func, Double, Double> f,
Func, Double, Double> df,
Double guess = DefaultGuess,
Double tolerance = DefaultTolerance,
int maxIterations = MaxIterations)
{
var x0 = guess;
var i = 0;
Double error;
do
{
var dfx0 = df(cashFlow, x0);
if (Math.Abs(dfx0 - 0) < Double.Epsilon)
throw new InvalidOperationException("Could not calculate: No solution found. df(x) = 0");
var fx0 = f(cashFlow, x0);
var x1 = x0 - fx0/dfx0;
error = Math.Abs(x1 - x0);
x0 = x1;
} while (error > tolerance && ++i < maxIterations);
if (i == maxIterations)
throw new InvalidOperationException("Could not calculate: No solution found. Max iterations reached.");
return x0;
}
internal static Double BisectionMethodImplementation(IEnumerable cashFlow,
Func, Double, Double> f,
Double tolerance = DefaultTolerance,
int maxIterations = MaxIterations)
{
// From "Applied Numerical Analysis" by Gerald
var brackets = Brackets.Find(Xnpv, cashFlow);
if (Math.Abs(brackets.First - brackets.Second) < Double.Epsilon)
throw new ArgumentException("Could not calculate: bracket failed");
Double f3;
Double result;
var x1 = brackets.First;
var x2 = brackets.Second;
var i = 0;
do
{
var f1 = f(cashFlow, x1);
var f2 = f(cashFlow, x2);
if (Math.Abs(f1) < Double.Epsilon && Math.Abs(f2) < Double.Epsilon)
throw new InvalidOperationException("Could not calculate: No solution found");
if (f1*f2 > 0)
throw new ArgumentException("Could not calculate: bracket failed for x1, x2");
result = (x1 + x2)/2;
f3 = f(cashFlow, result);
if (f3*f1 < 0)
x2 = result;
else
x1 = result;
} while (Math.Abs(x1 - x2)/2 > tolerance && Math.Abs(f3) > Double.Epsilon && ++i < maxIterations);
if (i == maxIterations)
throw new InvalidOperationException("Could not calculate: No solution found");
return result;
}
private static Double Xnpv(IEnumerable cashFlow, Double rate)
{
if (rate <= -1)
rate = -1 + 1E-10; // Very funky ... Better check what an IRR <= -100% means
var startDate = cashFlow.OrderBy(i => i.Date).First().Date;
return
(from item in cashFlow
let days = -(item.Date - startDate).Days
select item.Amount*Math.Pow(1 + rate, days/DaysPerYear)).Sum();
}
private static Double XnpvPrime(IEnumerable cashFlow, Double rate)
{
var startDate = cashFlow.OrderBy(i => i.Date).First().Date;
return (from item in cashFlow
let daysRatio = -(item.Date - startDate).Days/DaysPerYear
select item.Amount*daysRatio*Math.Pow(1.0 + rate, daysRatio - 1)).Sum();
}
public struct Brackets
{
public readonly Double First;
public readonly Double Second;
public Brackets(Double first, Double second)
{
First = first;
Second = second;
}
internal static Brackets Find(Func, Double, Double> f,
IEnumerable cashFlow,
Double guess = DefaultGuess,
int maxIterations = MaxIterations)
{
const Double bracketStep = 0.5;
var leftBracket = guess - bracketStep;
var rightBracket = guess + bracketStep;
var i = 0;
while (f(cashFlow, leftBracket)*f(cashFlow, rightBracket) > 0 && i++ < maxIterations)
{
leftBracket -= bracketStep;
rightBracket += bracketStep;
}
return i >= maxIterations
? new Brackets(0, 0)
: new Brackets(leftBracket, rightBracket);
}
}
public struct CashItem
{
public DateTime Date;
public Double Amount;
public CashItem(DateTime date, Double amount)
{
Date = date;
Amount = amount;
}
}
}
And after …maybe…30 minutes, I got a PL/SQL version up and running.
SQL> set serverout on
SQL> declare
2
3 cDaysPerYear number := 365.0;
4 cMaxIterations number := 100;
5 cDefaultTolerance number := 1E-6;
6 cDefaultGuess number := 0.1;
7 cDouble_Epsilon number := 0.00000000001;
8
9 type cash_obj is record ( dte date, amount number );
10 type cash_list is table of cash_obj index by pls_integer;
11 cash_item cash_list;
12
13 type bracket_rec is record ( frst number, second number );
14
15 function Xnpv(cashFlow cash_list, prate number) return number is
16 startdate date := cashFlow(1).dte;
17 dys number;
18 tot number := 0;
19 rate number := prate;
20 begin
21 if (rate <= -1) then rate := -1 + 1E-10; end if;
22
23 for item in 1 .. cashFlow.count loop
24 dys := -(cashFlow(item).Dte - startDate);
25 tot := tot + cashFlow(item).Amount*Power(1 + rate, dys/cDaysPerYear);
26 end loop;
27 return tot;
28 end;
29
30 function XnpvPrime(cashFlow cash_list, prate number) return number is
31 startdate date := cashFlow(1).dte;
32 daysRatio number;
33 tot number := 0;
34 rate number := prate;
35 begin
36 for item in 1 .. cashFlow.count loop
37 daysRatio := -(cashFlow(item).Dte - startDate)/cDaysPerYear;
38 tot := tot + cashFlow(item).Amount*daysRatio*Power(1 + rate, daysRatio - 1);
39 end loop;
40 return tot;
41 end;
42
43 function BracketsFind(cashFlow cash_list,guess number := cDefaultGuess) return bracket_rec is
44 x bracket_rec;
45 bracketStep number := 0.5;
46 leftBracket number := guess - bracketStep;
47 rightBracket number := guess + bracketStep;
48 i int := 0;
49 begin
50 while Xnpv(cashFlow, leftBracket)*Xnpv(cashFlow, rightBracket) > 0
51 loop
52 leftBracket := leftBracket - bracketStep;
53 rightBracket:= rightBracket + bracketStep;
54 i := i + 1;
55 end loop;
56
57 if i >= cmaxIterations then
58 x.frst := 0;
59 x.second := 0;
60 return x;
61 else
62 x.frst := leftBracket;
63 x.second := rightBracket;
64 return x;
65 end if;
66 end;
67
68 function BisectionMethodImplementation(cashFlow cash_list,tolerance number := cDefaultTolerance, maxIterations number := cMaxIterations) return number is
69
70 brackets bracket_rec;
71 f3 number;
72 result number;
73 x1 number;
74 x2 number;
75 i int := 0;
76 f1 number;
77 f2 number;
78 begin
79 brackets := BracketsFind(cashFlow);
80 if Abs(brackets.Frst - brackets.Second) < cDouble_Epsilon then
81 raise_application_error(-20000,'Could not calculate: bracket failed');
82 end if;
83 x1 := brackets.Frst;
84 x2 := brackets.Second;
85
86 loop
87
88 f1 := Xnpv(cashFlow, x1);
89 f2 := Xnpv(cashFlow, x2);
90
91 if Abs(f1) < cDouble_Epsilon and Abs(f2) < cDouble_Epsilon then
92 raise_application_error(-20000,'Could not calculate: No solution found');
93 end if;
94
95 if (f1*f2 > 0) then
96 raise_application_error(-20000,'Could not calculate: bracket failed for x1, x2');
97 end if;
98
99 result := (x1 + x2)/2;
100 f3 := Xnpv(cashFlow, result);
101
102 if (f3*f1 < 0) then
103 x2 := result;
104 else
105 x1 := result;
106 end if;
107 i := i + 1;
108 exit when not
109 (Abs(x1 - x2)/2 > tolerance and Abs(f3) > cDouble_Epsilon and i < cmaxIterations);
110 end loop;
111
112 if (i = cmaxIterations) then
113 raise_application_error(-20000,'Could not calculate: No solution found');
114 end if;
115
116 return result;
117 end;
118
119 function NewtonsMethodImplementation(cashFlow cash_list,tolerance number := cDefaultTolerance, maxIterations number := cMaxIterations,guess number := cDefaultGuess) return number is
120 x0 number := guess;
121 i int := 0;
122 error number;
123 fx0 number;
124 x1 number;
125 dfx0 number;
126 begin
127 loop
128 dfx0 := XnpvPrime(cashFlow, x0);
129 if (Abs(dfx0 - 0) < cDouble_Epsilon) then
130 raise_application_error(-20000,'Could not calculate: No solution found. df(x) = 0');
131 end if;
132
133 fx0 := Xnpv(cashFlow, x0);
134 x1 := x0 - fx0/dfx0;
135 error := Abs(x1 - x0);
136
137 x0 := x1;
138 i := i + 1;
139 exit when not (error > tolerance and i < cmaxIterations );
140 end loop;
141
142 if (i = maxIterations) then
143 raise_application_error(-20000,'Could not calculate: No solution found. Max iterations reached.');
144 end if;
145 return x0;
146 end;
147
148 function CalcXirr(cashFlow cash_list) return number is
149 ok boolean := false;
150 begin
151 for i in 1 .. cashFlow.count loop
152 ok := ok or cashFlow(i).amount > 0;
153 end loop;
154 if not ok then raise_application_error(-20000,'Add at least one positive item'); end if;
155
156 ok := false;
157 for i in 1 .. cashFlow.count loop
158 ok := ok or cashFlow(i).amount < 0;
159 end loop;
160 if not ok then raise_application_error(-20000,'Add at least one negative item'); end if;
161
162 -- return BisectionMethodImplementation(cashFlow);
163 return NewtonsMethodImplementation(cashFlow);
164
165 end;
166
167 begin
168
169 cash_item(1).dte := date '2008-01-01';
170 cash_item(2).dte := date '2008-03-01';
171 cash_item(3).dte := date '2008-10-30';
172 cash_item(4).dte := date '2009-02-15';
173 cash_item(5).dte := date '2009-04-01';
174
175 cash_item(1).Amount := -10000;
176 cash_item(2).Amount := 2750;
177 cash_item(3).Amount := 4250;
178 cash_item(4).Amount := 3250;
179 cash_item(5).Amount := 2750;
180
181 dbms_output.put_line(CalcXirr(cash_item));
182 end;
183 /
.3733625335188315103083924482521883278399
PL/SQL procedure successfully completed.
I validated the results against various data examples I found on the web. The example above maps to the example in the Excel link previous mentioned.
Let me assure you, I am in no way claiming that this was some sort of incredible feat on my part. It was simply taking each function in turn, making some assumptions about the variables and the coding constructs and iteratively converting the code over, and then testing it for validity within an anonymous block. And similarly, the PL/SQL code is not a shining example of quality coding standards – quite the opposite. It deliberately is trying to stay close to the naming standards and conventions that were in the C## source. Besides a couple of web searches (for example, to examine what Double.Epilson represented) the process was relatively straightforward.
You may be rolling your eyes and thinking to yourself: “Here we go….he’s going to tell us that C# is junk and that we should refactor our C# applications into the database”, but that is definitely not the case. My point is – if you have some data-intensive code in the application tier that will get benefits from locating that code closer to the database, you might surprise yourself by discovering how easy it is to port the logic from one coding language to another, in this case, PL/SQL. And for your Oracle databases, there is no simply better data-centric language than PL/SQL.
Learning is not a spectator sport 
Cool post. Not to distract from your message about moving from one coding language to another, but here are a couple of blog posts (from 2012) that describe how we used Oracle R Enterprise running in the database to compute IRRs at scale:
https://blogs.oracle.com/r/solving-big-problems-with-oracle-r-enterprise,-part-i
https://blogs.oracle.com/r/solving-big-problems-with-oracle-r-enterprise,-part-ii
Just food for thought if someone is really really looking for IRR options inside the Oracle database. R brings to the table some pretty strong and optimized math libraries that are good at iterative optimization solution problems like IRR.
Hi David,
Thanks for stopping by. Definitely – for heavy mathematical and statistical analysis, you can’t go past R…and its sitting there right in the database !
Cheers,
Connor
This is pretty neat. Now I don’t know Oracle databases very well (I’m learning though! 😉 ), but I’ve heard rumours that you can use Java in the database, and Java has almost the exact same syntax as C#.
If that is the case, it may be even easier to put stuff in the database. However, I am not saying that this is as simple/easy as it may sound like.
There’s also R, as someone else already pointed out.
And taking into account that we can natively compile pl/sql procedure into C, where the performance effect is markable especially for the code with lots of mathematical operation, I really opt to to write the code in the simplest language, i,e, PL/SQL.