financial function / annual rate of return

[monkeytrader]

thanks in advance for your help.
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.

ex) january - $100 initial investment with $10 profit
withdrawl the $10 in profit so february - $100 initial investment and
$10 profit.
etc, etc.

end of year i have $120 in profit plus the $100 inital investment.

is there a function in excel that i can use to calculate my annual
reinvested rate of return?

thanks.

 

[Don Guillett]

Look in the help index for XIRR and MIRR

 

[ryguy7272]

Assume dates in Column A:
1-Jan-07
1-Feb-07
1-Mar-07
1-Apr-07
1-May-07
1-Jun-07
1-Jul-07
1-Aug-07
1-Sep-07
1-Oct-07
1-Nov-07
1-Dec-07

Assume Dollars in Column B:
-100
10
10
10
10
10
10
10
10
10
10
10


=XIRR(B1:B12,A1:A12,0.1)
=21.43%

Regards,
Ryan--

 

[joeu2004]

[Sigh, Google Groups is having another "bad hair" day. I'll try reposting to
MS Newsgroups.]

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 split 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, and you want that to be treated as unequal periods. (Most
people treat "same day of each month" as __equal__ periods.)

 

[joeu2004]

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.