srisonti - 1 year ago 63
Ruby Question

# 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:

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

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.