Calulating a IRR based on monthly cash flows

[Brad F]

Hey -

I am working on a real esate development/ waterfall model and need to calculate an IRR based on monthly cash flows. The cash flows are irregular and assumed to be recognized at the end of each month over a year term. Is the XIRR function the way to go? And do I need to divide my hurdle rate by 12? Thanks.

 

[xenou]

I'm getting rusty on this but I think you can use IRR if the cash flows are evenly spaced, at the end of each month. Since you are assuming them to be recognized monthly that should work. XIRR is what you would use if cash flows are truly uneven - some months yes, others no.

As far as the rate, the IRR will give you a rate that is in the same "time" as the periods - so if its monthly periods its a monthly rate. You probably would need to divide your hurdle rate by 12, or vice versa multiply the result from Excel by 12. XIRR (I think) gives an annualized rate.

 

[Brad F]

So essentially if have cash flows shown in 12 periods, regadless if it's 12 months or 12 years - the IRR is treated the same? Thanks.

 

[xenou]

Yes, that's it. You'll have to put the answer in context of the lengths of periods as defined by your input, I think.

 

[Brad F]

Thanks - I appreciate the help