IRR(Internal Rate of Return) Calculator
A pragmatic approach by Durga
Prerequisite
Before proceeding please get familiar with the Internal Rate of Return(IRR), Future Value(FV), Net Future Value(NFV), Present Value(PV), Net Present Value(NPV), Compound Interest, Simple Interest, and various algorithms used to calculate IRR and their limitations.
Background
As an individual, I like to know the actual rate of return that I earned with investments or the actual rate of the cost that I incurred with borrowings, particularly with uneven cash flows over regular intervals of time. I am fine with 2 decimals accuracy, but I need an algorithm that does not fail for my practical cash flows.
There are multiple ways to get a practical rate of return and IRR is the foundation for the majority of approaches to find the rate of return for given cash flows.
IRR
IRR is defined as the rate at which the sum of all future values or the sum of all prevent values of the cash flows is zero. In other words, the rate at which NPV(Net Present Value) or NFV(Net Future Value) of all cash flows is zero.
Given cash flows as (C₀, C₁, C₂ …Cn), and each interval represented with i, and NPV is net present value then r (IRR) shall be estimated by using the formula
Similarly, for NFV(Net Future value), IRR shall be estimated using
I prefer the NFV approach and shall be using the same in the solution.
What’s wrong with existing IRR calculators?
I tried multiple libraries, algorithms, Google docs, Microsoft excel. None of them addressed all my needs. The following are some issues with popular IRR calculators.
Apache POI popular Java API for Microsoft documents. It has an IRR calculator that uses Newton’s method but fails for many cash flows. Notice NaN in the below screenshot.
In the below screenshot from google docs
#Scenario 1 gives zero but the actual value is approximately (-0.4962626)*
#Scenario 2 fails but its IRR is approximately (-283.9287)
The majority of algorithms use the root-finding algorithm which is boundary less , needs intial guess and has limitations.
Durga’s pragmatic approach
Before jumping into formulas or algorithms let us take the following scenario.
Take annual cash flows are [-100, -100,-100, -100, 1000] assuming negative cash flow as an outflow of money and positive as inflow of money, means every year $100 has been invested and at the end of 4 years got back $1000. Overall $400 went out of my pocket and got back $1000, made 600$ profit for $400 investment in the span of 4 years.
If I had invested all $400 in 0th year and got back $1000 in 1st year one would have made max return, as the return is in shortest interval.
Let us change cash flows to [1000, -100, -100, -100, -100 ]. So, brought $1000 and paid back $100 for 4 years. Here also made 600$ profit for $400 investment in the span of 4 years.
Again, If I had borrowed all $1000 in 0th year and paid back $400 in 1st year one would have made max gain.
By minimizing intervals between total positive cash flows and negative cash flows to one year, the rate of return can be calculated simply for 1000 -> 400 in one interval or 400 ->1000 in one interval. Converging shall give a positive rate and expanding gives a negative rate.
So using this logic I can derive boundaries for IRR.
Let us represent the absolute value of total positive cash flows TPCF, and the absolute value of total negative cash flows as TNCF, then
IRR can be re-defined as the rate(r) at which net future value(NFV) of all cash flows is zero provided
For the above example (400/1000 -1)*100 ≤ r ≤ (1000/400–1)*100
which comes to -60 ≤ r ≤ 150.
Consider IRRmin represents lower boundary and IRRmax represents upper bounder, for any cash flows if NFV of IRRmin is positive then NFV for IRRmax will be negative and vice versa. (Refer below images)
Now the problem has boundaries, and it shall be easy to find IRR using solutions similar to that of binary search. There are multiple checkpoints like TPCF and TNCF that are far apart, zero return, x-axis cut of min-max, tangential cut of min, tangential cut of max, that can be considered to narrow down boundaries in each interaction. Those are covered in the in-depth section.
Is this hypothesis working?
This hypothesis is working for cash flows only when the direction of cash flow is changing once(‘first negative, then positive’ or ‘first positive, then negative’). If the boundaries are not identified in this approach, a smart brute force approach is used from -10k to +10k to form initial boundaries.
Implementation
Link: Visualize NFV for various rates
By entering comma-separated cash flows we can visualize graph plotting NFV of given cash flows at various rates. For all the points that curve cuts x-axis NFV shall be zero.
Link: Java library source code and maven repo information
A Java library with an IRR/PIRR calculator and some scripts to analyze the performance and quality of results.
Note
I am not completely satisfied with my explanation, but I tried what I can. If there is interest from readers, I shall explain in-depth on implementation(zero return, x-axis cuts, tangential cuts) that are used to narrow down boundaries. IRR is the foundation but not the exact solution to calculate the return for given cash flows.
My sincere thanks to my friend Vishnu for reviewing the solution and made me realize that IRR itself is not enough to find the practical rate of return.
* Scenario 1 is not an error, it is formatting miss. I wanted to see whether anybody actually validating my claims and I got response ☺
