IRR Function

[CFO]

How do you use the Excel IRR function when using monthly
data ? It seems the formula is set to accept annual data
in its calculation.....

 

[J.E. McGimpsey]

IRR accepts data from any regular interval: minutes, days,
fortnights, months, years, decades...

The result you get is also a rate for that interval, so if you put
in monthly data, you'll get a monthly rate out.

 

[Ashish Mathur]

Hi,

The internal rate of return is the interest rate received
for an investment consisting of payments (negative values)
and income (positive values) that occur at regular
periods. The regular period may be monthly or yearly or
may follow any other periodicity. The formula for IRR
takes care of any periodicity. Hope this answers your
question.

Regards,

Ashish Mathur
Excel - MVP

Note: You must ensure that the periodic data entered is
consistent i.e. they ll are either monthly, yearly etc.

 

[cfo]

Got it, I think the difference is that the IRR result dor
a daily stream of data would need to be multiplied by 365
to adjust to an annualized result..... x 12 for monthly,
etc. Thanks for your assistance.

 

[Harlan Grove]

Thanks for the input, I think you hit the nail on the
head, I am looking at a monthly result, hence I just need
to multiply the answer by 12 to annualize. Thanks !

...

Wrong. You annualize a monthly *effective* interest rate R like so:

(1 + R)^12 - 1

For smaller interest rates that's approximately equal to 12*R, but the
approximation becomes progressively less accurate as R increases.

 

[J.E. McGimpsey]

NO. You can severely underestimate the annual rate that way:

say the Daily rate is 0.0274%

Multplying by 365 gives 10.00%

Using the correct formula (1+periodRate)^numPeriods - 1:

(1 + .0274%)^365 - 1 ===> 10.52%