Calculate cash flows given a target IRR

22 viewsfinancemathruby
0

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.