Irr function

[confused IRR user]

I'm using Microsoft Excel 97 and I have a question
regarding the IRR function. I'm trying to do an IRR
calculation on a monthly cash flow stream and using the
formula the way it appears its seems to be calculating an
IRR as if the stream was an annual stream. Am I right, if
so, is there a way (other than simply aggregating the
monthly streams to a single annual stream) to get the IRR
function to recognize the time period?

 

[Kevin Stecyk]

Confused,

Irr just assumes equal periods. For example, if you have monthly data and
do your IRR calculation, you will get an IRR per month. If you want an
annual IRR, then you have to convert it from monthly to annual.

Different people convert month irr to annual irr differently. It depends on
their purpose. A couple of the most common are as follows:

Simple: annual_irr = monthly_irr * 12

Compounding: annual_irr = (1+monthly_irr)^12-1

You have look at the details of the calculation.

So again, IRR just looks at the data and assumes equal periods. It will
provide an IRR value per period. You must do the necessary conversion work.
So you ought to be okay using your IRR on your monthly cash flows.

Hope that helps.
Regards,
Kevin

 

[Guest]

Thank you very much!! Once I read your email the light
went on...

 

[Kevin Stecyk]

You are most welcome. Thank you for your comments.

Regards,
Kevin

 

[Bill]

The IRR function is blind to calendar time periods.
You have to multiply the IRR by some number to equal an
annualized IRR depending on the timing (ie: x12 if
monthly flows, x4 if quarterly flows). This simple
methodology does however tend to understate the actual
IRR. A more precise method is to use the following
formula: =((irr(range,quess)+1)^n)-1 where n=the number
of periodic cash flows within a year (ie: 12 for monthly,
4 for quarterly). An even better way to use the xirr
function which produces an annualized IRR regardless of
the timing and frequency of the cash flows. The xirr
function is an elusive animal. I have heard that it is
available in the "Analysis Tookpack Add-in" but I myself
have not been able to find it.