XIRR

[Josh Frankel]

I've studied carefully the XIRR function, and wonder if someone could
address the following:

XIRR clearly returns an *annualized* rate of return.

If I were interested in similar functionality (i.e. accounting for irregular
cash flows) on a *monthly* basis (vs. XIRR's annualized rate of return), is
there either another formula OR modification of XIRR that would do the
trick?

IOW, consider the following for example:

DATE VALUE
4/1/2003 1,000,000 Beginning Value
4/8/2003 -6,000 Withdrawal
4/16/2003 8,000 Deposit
4/24/2003 -6,000 Withdrawal
4/30/2003 1,025,000 Ending Value

XIRR will return an annualized rate of return of 0.43403.

What would be the appropriate methodology to calculate a return for the
month?

Thanks in advance.

 

[GB]

I've studied carefully the XIRR function, and wonder if someone could
address the following:

XIRR clearly returns an *annualized* rate of return.

If I were interested in similar functionality (i.e. accounting for irregular
cash flows) on a *monthly* basis (vs. XIRR's annualized rate of return), is
there either another formula OR modification of XIRR that would do the
trick?

IOW, consider the following for example:

DATE VALUE
4/1/2003 1,000,000 Beginning Value
4/8/2003 -6,000 Withdrawal
4/16/2003 8,000 Deposit
4/24/2003 -6,000 Withdrawal
4/30/2003 1,025,000 Ending Value

XIRR will return an annualized rate of return of 0.43403.

Josh - use the formula (1+monthly rate)^12 = (1+annual rate)

Or to put this another way, monthly rate = (1+annual rate)^(1/12) -1

In your case this gives a monthly rate of 3.0496%

Geoff

 

[GB]

I don't know whether your example is from real data, but a word of warning.
There may be more than one real solution to the discount rate in the example
you gave. XIRR allows you to give a guess value, and I suggest that you run
it with several different guess values, so that you get to see what the
different solutions are. Some of the solutions may not be real numbers,
though.

Geoff