srisonti - 1 year ago 63

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

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 Source

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.