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.

	Cost	of Capital	0.00%		Net:	7,370,000	31%
			Loaded		Outflows:	-24,100,000
Period	Date	Funds	Funds		Inflows:	31,470,000
1	1/1/2013	-100,000	??
2	2/1/3013	0
3	3/1/2013	-16,000,000
4	4/1/2013	-1,000,000
5	5/1/2013	-3,000,000
6	6/1/2013	-2,000,000
7	7/1/2013	-1,000,000
8	8/1/2013	-1,000,000
9	9/1/2013	55,000
10	10/1/2013	55,000
11	11/1/2013	120,000
12	12/1/2013	120,000
13	1/1/2014	120,000
14	2/1/2014	31,000,000

<tbody>
</tbody>

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