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.
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.
Last edited: