XIRR Problem

[Dkline]

I need to go month to month with a value at the beginning of the month, a
cash flow at some point the middle of the month, and a value at the end of
the month. I'm trying to get the ROR for that month.

For example:
$67,626.52 2/29/2008 known starting value
-$3,921.48 3/6/2008 cash flow
$72,906.91 3/31/2008 known value at end of month

If I set this up in XIRR, I get 0.000..

If I build out using a row for each day of the month "from known starting
value" through "known value at end of month" (thus including the cash flow as
a positive number since I'm putting monto into the account) and use Goal Seek
to find an answer, I get 0.0613%

What is the right answer and how do I get there from here?

 

[Ron Rosenfeld]

I need to go month to month with a value at the beginning of the month, a
cash flow at some point the middle of the month, and a value at the end of
the month. I'm trying to get the ROR for that month.

For example:
$67,626.52 2/29/2008 known starting value
-$3,921.48 3/6/2008 cash flow
$72,906.91 3/31/2008 known value at end of month

If I set this up in XIRR, I get 0.000..

If I build out using a row for each day of the month "from known starting
value" through "known value at end of month" (thus including the cash flow as
a positive number since I'm putting monto into the account) and use Goal Seek
to find an answer, I get 0.0613%

What is the right answer and how do I get there from here?

I don't know what the "right" answer.

But a few pointers.

Your "signs" are inconsistent.

Cash flow FROM you should be represented by negative numbers, and TO you as
positive numbers (or vice versa). ALL of the values are interpreted as cash
flows.

So whichever convention you chose, your starting value and ending value should
have opposite signs.

In your case, it would seem that the starting value should also be negative,
since you are considering that money you put "into the account" is negative.
(That starting value had to get into the account somehow).

Also, XIRR gives a annual return (based on a 365 day year).

If you plug the properly signed values into the XIRR formula, I get a result of
25.0945%, which is the annualized return you are getting.
--ron

 

[Dkline]

I match what you have in your example. To convert to a monthly term is it as
simple as dividing by 12?

 

[joeu2004]

I match what you have in your example. To convert to a monthly
term is it as simple as dividing by 12?

There are two schools of thought on the subject, pretty much split
50-50. Many people do just divide by 12. Others compute the
compounded monthly rate. In Excel, this can be done either of the
following ways:

=(1+r)^(1/12) - 1

=rate(12,0,-1,1+r)

where "r" is the annual rate.

 

[Ron Rosenfeld]

I match what you have in your example. To convert to a monthly term is it as
simple as dividing by 12?

Probably not.

I always get confused when trying to use the terms nominal and effective. So I
won't use them in this discussion <g>.

The XIRR result - 25.0947% - means that if you invested $1 at the beginning of
the year, at the end of a 365-day year you should have $1.250947. In other
words, no compounding of the interest.

I assume you are in a compound interest situation, and interested in monthly
returns. If you merely took the XIRR result, divided it by 12, and then
compounded that return 12 times, you would calculate a higher ending value than
is implied by the XIRR result (specifically, you would calculate $1.28192)

There are a variety of ways to make the adjustment to come up with an interest
rate which, when compounded monthly, will give you the correct annual result.

joeu2004 gave you two methods.

Another method is to use the Excel NOMINAL function and divide that result by
12:

=NOMINAL(your_xirr_formula,12) / 12

It's about 1.8834%

That number, applied to $1 and compounded 12 times, will result in an ending
value of $1.250947 as also predicted by the XIRR function.
--ron