Calculate cash flows given a target IRR

Question

I apologize if the answer for this is somewhere already, I've been searching for a couple of hours now and I can't find what I'm looking for.

I'm building a simple financial calculator to calculate the cash flows given the target IRR. For example:

• I have an asset worth $18,000,000 (which depreciates at $1,000,000/year)
• I have a target IRR of 10% after 5 years
• This means that the initial investment is $18,000,000, and in year 5, I will sell this asset for $13,000,000
• To reach my target IRR of 10%, the annual cash flows have to be $2,618,875. Right now, I calculate this by hand in an Excel sheet through guess-and-check.

There's other variables and functionality, but they're not important for what I'm trying to do here. I've found plenty of libraries and functions that can calculate the IRR for a given number of cash flows, but nothing comes up when I try to get the cash flow for a given IRR.

At this point, I think the only solution is to basically run a loop to plug in the values, check to see if the IRR is higher or lower than the target IRR, and keep calculating the IRR until I get the cash flow that I want.

Is this the best way to approach this particular problem? Or is there a better way to tackle it that I'm missing? Help greatly appreciated!

Also, as an FYI, I'm building this in Ruby on Rails.

EDIT:

IRR Function:

NPV = -(I) + CF[1]/(1 + R)^1 + CF[2]/(1 + R)^2 + ... + CF[n]/(1 + R)^n

NPV = the Net Present Value (this value needs to be as close to 0 as possible)

I = Initial investment (in this example, $18,000,000)

CF = Cash Flow (this is the value I'm trying to calculate - it would end up being $2,618,875 if I calculated it by hand. In my financial calculator, all of the cash flows would be the same since I'm solving for them.)

R = Target rate of return (10%)

n = the year (so this example would end at 5)

I'm trying to calculate the Cash Flows to within a .005% margin of error, since the numbers we're working with are in the hundreds of millions.

Answer 1

Let

v0 = initial value
vn = value after n periods
n  = number of periods
r  = annual rate of return
y  = required annual net income

The one period discount factor is:

j = 1/(1+r)

The present value of the investment is:

pv = - v0 + j*y  + j^2*y + j^3*y +..+ j^n*y + j^n*vn
   = - v0 + y*(j + j^2   + j^3   +..+ j^n)  + j^n*vn
   = - v0 + y*sn + j^n*vn

where

sn = j + j^2 + j^3 + j^4 +..+ j^n

We can calulate sn as follows:

sn       = j + j^2 + j^3 + j^4 +..+ j^n
j*sn     =     j^2 + j^3 + j^4 +..+ j^n + j^(n+1)
sn -j*sn = j*(1 - j^n)
sn       = j*(1 - j^n)/(1-j)
         = (1 - j^n)/[(1+r)(r/(1+r)]
         = (1 - j^n)/r  

Set pv = 0 and solve for y:

y*sn =  v0 - vn * j^n   
y    = (v0 - vn * j^n)/sn
     =  r * (v0 - vn * j^n)/(1 - j^n)

Our Ruby method:

def ann_ret(v0, vn, n, r)
  j = 1/(1+r)
  (r * (v0 - vn * j**n)/(1 - j**n)).round(2)
end

With annual compounding:

ann_ret(18000000, 13000000, 5, 0.1)       # => 2618987.4

With semi-annual compounding:

2 * ann_ret(18000000, 13000000, 10, 0.05) # => 2595045.75

With daily compounding:

365 * ann_ret(18000000, 13000000, 5*365, 0.10/365) # => 2570881.20 

These values differ slightly from the required annual return you calculate. You should be able to explain the difference by comparing present value formulae.

Answer 2

There's a module called Newton in Ruby... it uses the Newton Raphson method.

I've been using this module to implement the IRR function into this library:

https://github.com/Noverde/exonio

If you need the IRR, you can use like this:

Exonio.irr([-100, 39, 59, 55, 20]) # ==> 0.28095