On Nov 6, 10:00 am, monkeytrader
wrote:
i wanted to know if there is a function in excel that can calculate an
annual rate of return on reinvestments.
specifically: over a 1 year (12month) timeline. i need to figure out what
the annual return rate is if i start with $100 and make $10 every month,
but withdrawl the $10 in profit every month.

Ostensibly, you could simply use RATE(12,10,-100) in this case because
both the cash flows and the frequencies are equal. That returns the
__periodic__ (monthly) rate of return. Unfortunately, academics and
financial professionals are splity evenly on how to annualize that.
One of the following should work for you:

=12*rate(12,10,-100)

=(1+rate(12,10,-100))^12 - 1

Remember to format the cell as Percentage with the desired number of
decimal places.

PS: IRR() should give you the same result as RATE(); use IRR() when
the amount of the cash flows are unequal. XIRR() gives a different
result for several reasons: (1) XIRR() results in the compounded
annualized result, similar to the second formula above; and (2) XIRR()
is sensitive to the exact number of days between payments, so it
differs even from the result of the second formula above. Use XIRR()
when the frequency of the cash flows are unequal; for example, if you
make the payments on the same day of each month, not every 30 days.