Calculating a dollar-weighted return

[Petrak]

I am trying to calculate a dollar-weighted investment return for a month
period with some cash flows on some days in the month,
For example:

Jan 1: $100 Beginning market value
Jan 5: $5 added
Jan 10: $10 taken out
Jan 15: $3 taken out.
Jan 31: Ending market value is $110.

I assume I use XIRR to get the return for the month. For some reason I am
getting a nonsensical answer. Could I get some advise on how to set it up
and let me know what the correct answer (return) is?

 

[JoeU2004]

For some reason I am getting a nonsensical answer.

I have no idea why, since you do not even tell us what answer you got, much
less how you are using XIRR. For future reference, it would be prudent to
provide that information with any question about usage.

XIRR is notorious for returning nonsensical results under some conditions.
But apparently, this is not one of those times.

Also, Excel can yield confusing results because of its attempts to intuit
the right format or because of a pre-existing cell format. When in doubt,
explicitly choose an appropriate format.

Could I get some advise on how to set it up
and let me know what the correct answer (return) is?

I put the dates (e.g. 1/1/2009, 1/5/2009) in A1:A5, and I put the signed
cash flows in B1:B5, namely: -100, -5, 10, 3 and 110. Then I use the
following formula in B6:

=XIRR(B1:B5,A1:A5)

formatted as Percentage with 2 decimal places. The result is about 710.32%.
As a double-check, I confirm that the NPV is about zero with the formula:

=XNPV(B6,B1:B5,A1:A5)

formatted as number with 2 decimal places.

(Note: This double-check is important to do with XIRR since it will return
nonsensical numbers instead of the documented #NUM or #DIV/0 error when it
is unable to compute the rate of return within limits. XIRR should do the
double-check; but it doesn't (sigh).)

The key with most (but not all !) Excel financial functions is to be sure
that inflows and outflows have opposite signs. Although it does not matter
whether inflow or outflow is positive, it is conventional to show inflows as
positive numbers. Note that an initial investment or beginning balance is
treated as an outflow, and an ending balance is treated as an inflow.

If an annualized return of 710% seems nonsensical to you (as it should), it
is because it is unrealistic and usually misleading to annualize returns
over very short time periods, unless you are dealing with a constant or
nearly-constant return rate (e.g. fixed interest rate).

Nonetheless, 710% is the correct annualized returned for your cash flows.


----- original message -----

 

[JoeU2004]

Minor clarification....

Note that an initial investment or beginning balance is treated
as an outflow, and an ending balance is treated as an inflow.

.... From the point of view of the investor.


----- original message -----

 

[Petrak]

Actually I think we are on the right track. I got the same results as you
when i did it initially. Since that return of 710.032 is an annualized
return, I had to DE-ANNUALIZE it to get the return for the month., Which I
did as follows:

(710.32/100+1)^(30/365) = 1.1876, 0r 18.76% (when you take it out of its
compound form)

This is the right answer. I checked this result with the answer I got from
using another method, which I know to be right.