Return on Investment for Multiple Cash Flows -- NPV IRR

[jcorlando]

Team,

I'm trying to calculate the ROI where there are many outflows & inflows.

Since inflows > outflows; the investor makes $$
The return is 31% if the Cost of Capital is Zero.

The ROI is that Cost of Capital which yields Zero for the Net Return.

To solve this I need a column (or many columns) to calculate the cost/benefit of each outflow or inflow -- Loaded Fund value.

Then set the Cost of Capital (in Blue) such that Net Return (in Purple) is Zero.

-- Suggestions for the Loaded Funds formula?
-- Any other ideas??
-- There will probably be several solutions.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"]Cost
[/TD]
[TD="align: right"]of Capital
[/TD]
[TD="align: right"]0.00%
[/TD]
[TD][/TD]
[TD="align: right"] Net:
[/TD]
[TD="align: right"] 7,370,000
[/TD]
[TD="align: right"]31%
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]
[/TD]
[TD="align: center"]Loaded[/TD]
[TD][/TD]
[TD="align: right"]Outflows:[/TD]
[TD="align: right"]-24,100,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]Period
[/TD]
[TD="align: center"]Date
[/TD]
[TD="align: center"]Funds
[/TD]
[TD="align: center"]Funds[/TD]
[TD][/TD]
[TD="align: right"]Inflows:[/TD]
[TD="align: right"]31,470,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]1/1/2013[/TD]
[TD="align: right"]-100,000[/TD]
[TD="align: center"]??[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]2/1/3013[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]3/1/2013[/TD]
[TD="align: right"]-16,000,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]4/1/2013[/TD]
[TD="align: right"]-1,000,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]5/1/2013[/TD]
[TD="align: right"]-3,000,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]6/1/2013[/TD]
[TD="align: right"]-2,000,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]7/1/2013[/TD]
[TD="align: right"]-1,000,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]8/1/2013[/TD]
[TD="align: right"]-1,000,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]9/1/2013[/TD]
[TD="align: right"]55,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]10/1/2013[/TD]
[TD="align: right"]55,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]11/1/2013[/TD]
[TD="align: right"]120,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]12/1/2013[/TD]
[TD="align: right"]120,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]120,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"]2/1/2014[/TD]
[TD="align: right"]31,000,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks,

John
In Annapolis, MD

PS: The IRR() and NPV() formulas will fail.

 

[pplstuff]

What do you mean "cost/benefit of each outflow or inflow"?

Why won't npv work (have you accounted that npv discounts annually, not monthly - so you must adjust your discount to a monthly rate in this scenario)?

Also, there are many algebraically correct ways to calculate IRR for non-normal projects.

What the loaded funds formula to represent? Can you provide an example of the desired output?

 

[jcorlando]

Hi pplstuff,

1) The cost/benefit of each flow means the investor's outflow would have negative interest
and the inflows would have a positive inflow.

2) I haven't tried the NPV. But I'm really looking for the IRR and IRR will fail.
The actual spreadsheet had many interspersed inflows and outflows.

3 & 4) my desired results is that algebraic method to calculate the IRR.

I may have figured this out.
In the "Loaded Fund" column I'm using the formula : = D5 + ( D5 * PCOC ) * (Periods - B5)

"PCOC" is a named range with the periodic Cost of Capital.
"Periods" is a named range with the total number of periods:
Column "B" holds he current period. And,
Column "D" holds the funds (inflows & outflows).
​

So in Period 1) the "loaded" $100,00 is = 100000 + ( 100000 * PCOC) * (14 - 1) = $140,083
which is the interest on $100,000 x 13 periods.


Thoughts??

John,
In Annapolis

 

[pplstuff]

Well, if IRR is really what you're after, you might want to google "geometric mean" - and read its application to annual growth: Geometric mean - Wikipedia, the free encyclopedia

This is one approach to calculating IRR for non-normal projects (non-normal project meaning a project where there is more than one sign change in cash flows (+/-)). I believe the geometric mean will be what you're looking for.

Sidenote: there is the geomean() function is excel. It's been a while since I used it, so I can't offer any handy tips one that one. I have generally created matrices to calculate my geo means in the past.

 

[jcorlando]

PPLStuff,

Thank you for the suggestion. I'll review that
And after some other tests, I'll postback my final solution and thoughts.

John,
In Annapolis, MD
www.Excel-Access.com