IRR calculations

[Guest]

How does one annualize a series of monthly cash flows (usually greater 12
months)using the irr calculation ? Do you multiply the result of the
calculation by 12 (i.e. 12 months per year) or do you multiply by the number
of months in the calculation period ?

 

[Fred Smith]

IRR, like all financial functions will give you the periodic interest rate.
That's because IRR doesn't know, nor care, what period you are using. It could
be months, years, days, etc.

To convert a monthly rate to an annual rate, there are standard formulas. But I
always like to ask the question, "If you invested $100 at x% per month, how much
would you owe at the end of the year?" That will tell you what the annual rate
is. This formula is:

=fv(IRR(...),12,0,-100)

If the result is, say, $114.50, your APR is 14.5%.

The full formula to calculate the APR would be:

=FV(IRR(...),12,0,-1)-1

 

[Ron Rosenfeld]

I
always like to ask the question, "If you invested $100 at x% per month, how much
would you owe at the end of the year?"

That's really a trick question, isn't it?
--ron

 

[Guest]

Fred

Thanks for the feedback.

I think your approach is more complicated than it needs to be for my
application. However what are the "standard formulae" to which you refer ?

 

[joeu2004]

How does one annualize a series of monthly cash flows (usually greater 12
months)using the irr calculation ? Do you multiply the result of the
calculation by 12 (i.e. 12 months per year) or do you multiply by the number
of months in the calculation period ?

Some people (including some academicians) do simply multiply by 12.

(You would never multiply by the number of periods in the IRR
computation.)

Other people (including some academicians) compound the monthly rate,
for example by using one of the following equivalent formulas:

=(1+IRR(...))^12 - 1

=fv(IRR(...), 12, 0, -1) - 1

I contend that the latter (compounding) is the "right" approach for
many reasons. Perhaps the simplest reason: that is what XIRR() would
yield, given equivalent parameters.

(Excel's XIRR() function always an annualized result.)

The bottom: the choice is up to you.

 

[Fred Smith]

I'm afraid I made it as simple as possible. As I and others have said, one
standard formula is:

=FV(IRR(...),12,0,-1)-1

Another is to use EFFECT from the Analysis Toolpak.