Hi Corey,
B1:B60 houses your cashflows
in A1 put 1/1/02 (US date convention)
in A2 put =DATE(YEAR(A1),MONTH(A1)+1,1)
copy down to A60
Wherever you want your result, place the following formula:
=XIRR(B1:B60,A1:A60,SIGN(SUM(B1:B60))*0.1)
=47.1899%
XIRR returns an annualized result, whereas IRR returns the period result and you must annualize the values yourself.
Slight differences you'll notice here is that I chose the dates to fall at the beginning of each month, so there are 28, 29, 30, or 31 days between cashflows. XIRR is sensitive to that, so be aware.
Suggestions for use...
1. Always give a guess. I chose to use +/- 10% depending on the siummation of the total cashflows.
2. If you have non-normal cashflows, where the sign changes more than once, you have multiple IRRs and the initial guess becomes exceedingly important. Even more important is that the result is almost meaningless in that case. It is a probelm with the concept more than the Excel function.
HTH,
Jay
HTH,
Jay
On 2002-03-15 14:22, Corey wrote:
OK, say I have the following stream of monthly cash flows:
months 1-12 -125,000 each month
months 13-24 +75,000 each month
months 25-36 +75,000 each month
months 37-48 +75,000 each month
months 49-60 +75,000 each month
The annual IRR should be somewhere around 50%. Excel calculates a monthly IRR of around 3.3%. If I multiply that by 12 to annualize it, the result would be 39.6%, which is too low. Is there a formula that will take monthly cash flow and calculate an annual rate of return?
Thanks.