By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,655 Members | 1,860 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,655 IT Pros & Developers. It's quick & easy.

Net IRR formula

P: 1
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
Sep 19 '07 #1
Share this Question
Share on Google+
1 Reply


kadghar
Expert 100+
P: 1,295
...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
Sep 19 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.