Net IRR formula

[JHaworth]

Hi,

Please could someone point me in the right direction with a formula I am trying to create which will enable me to calculate a Net IRR for a series of data?

My Problem:
•My formula in Excel is currently too long, complicated and slow
•I am currently using the IRR function on a series of cashflows. eg =IRR(cashflow,0.01)
•I am trying to create a formula called NetIRR(cashflow, fee, hurdle, compound) which will ultimately perform an IRR calculation on the range {cashflow}, after the formula has stepped through each cell in the range and amended the value of the monthly cashflow using the three other fixed variables {fee, hurdle, compound}.

I think I will be able to write the code to amend each monthly cashflow (formula below is just as an example), but I am struggling on how to structure and step through this new function.

I guess that the code might follow the form of (in theory – not VB!)

Function NetIRR(Cashflow, fee, hurdle, compound)
month=1
Start loop
NetCashflow(month) = Cashflow(month) * fee – hurdle * compound
End loop (when last month in range cashflow has been reached)

NetIRR = IRR(NetCashflow,0.01)

End function


If anyone can help me in any way I'd very much appreciate it!!
Thanks

James

[kadghar]

...I guess that the code might follow the form of (in theory – not VB!)

Function NetIRR(Cashflow, fee, hurdle, compound)
month=1
Start loop
NetCashflow(month) = Cashflow(month) * fee – hurdle * compound
End loop (when last month in range cashflow has been reached)

NetIRR = IRR(NetCashflow,0.01)

James

if you create a public function, you'll be able to use it in an excel worksheet, but it wont be much faster than the one you already have, but you can put it into a procedure to create an array of results and then just put them into excel.

i think your function should look like this in vb:

public function NetIRR( byval CFlow as double, Fee as double, Hurdle as single, Cmp as single) as single
dim month as integer
dim NCFlow() as double
redim NCFlow(lbound(cflow) to ubound(cflow))
for month = lbound(cflow) to ubound(cflow)
netcashflow(m) = cashflow(m)*fee - hurdle*cmp
next
NetIRR =IRR(ncflow,0.01)
end function

now you only need a function called IRR that gives you the internal rate of return

Please note that cFlow must be an array