rubymathfinance

Calculate cash flows given a target IRR


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:

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.


Solution

  • 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.