How do I calculate IRR using monthly cash flows, not annual?

[Guest]

As I understand it, Excel assumes that for purposes of calculating IRR, each
consecutive value is an annual value. If I have a short lived project, say
16 months, Excel assumes that the cash flow in the 16th cell is the 16th
year, thus the IRR is dramatically understated. How do you adjust for this
effect in Excel?

 

[Guest]

Try looking at the XIRR function which measures the IRR between two specific
dates, eg btween 28/02/01 to 05/06/05. You may have to got to Tools - Add ins
- Analysis toolpack.

Paul

 

[Ron Rosenfeld]

On Wed, 26 Oct 2005 06:08:04 -0700, "Mike Knoxville" <Mike

As I understand it, Excel assumes that for purposes of calculating IRR, each
consecutive value is an annual value.

That is incorrect. Excel requires that the cash flows occur at regular
intervals, but makes no assumptions about the duration of each interval.

f I have a short lived project, say
16 months, Excel assumes that the cash flow in the 16th cell is the 16th
year, thus the IRR is dramatically understated. How do you adjust for this
effect in Excel?

If your cash flows are monthly, multiply by 12 and use the EFFECT function to
convert to the effective rate.

This will give a slightly different result than the XIRR function, which is a
different option, since that function assumes a 365 day year, rather than the
regular interval assumption of IRR.


--ron