IRR with monthly cash flows

[brejohns]

I have monthly cash flows than I need to use to calculate the IRR for the period. Can anyone help me with the formula??

[jcvoth]

You can use the following formula:

=IRR(range of values)

but I highly recommend you read

http://invest-faq.com/articles/analy...te-return.html

first. This gives you the actual calculation of

F = -P(1+i)^n - [p(1+i)((1+i)^n - 1)/i].

The latter formula is much better if you really want to understand what you are calculating.

-Jarrod

[brejohns]

I understand this formula =IRR(range), but I am looking for the formula that calculates based on diferent MONTHLY cash flows

[jcvoth]

I'm not sure I understand the question.

IRR calculates the internal rate of return for any payment of regular intervals. The interval could be yearly, monthly, quarterly, etc. As long as your intervals are regular, the IRR function will work.

For example, in the following series:

Period 0 = -5,000 <----This is the initial outlay
Period 1 = 1500
Period 2 = 1500
Period 3 = 1500
Period 4 = 1500
IRR of the range = 8%.

Period could be yearly, quarterly, monthly, or daily.

However, Period 1 was for 1/1/4, Period 2 was 1/5/4, and Period 3 was 4/1/4, the IRR function will not work.

Is this the problem you are having?

-Jarrod

[brejohns]

These periods assume one cash flow per year. I am assuming 12 seperate cash flows in one year. 1/1/04, 2/1/04, 3/1/04 and so on.

[jcvoth]

It's not pretty, but I got the following method to work:

I converted the vertical years into horizontal years. i.e.

0
1
2
3
4

to

0 1 2 3 4

Under the horizontal periods, i used the following formula:

=VLOOKUP(I$16,$D$17:$E$20,2,FALSE)/12 which pulls over the yearly pmt and converts it into monthly format.

Then I used IRR(entire range)

The only thing to remember is to include the initial outlay in the 0 column.

In the example I used, a $1000 outlay was returned with 4 equal annual payments of 120. The IRR was -24.03%.

When converted to an initial outlay of $1000 and 48 equal monthly payments of $10, the IRR returns as -2.68%.

I know this isn't pretty, but it does get the job done.

-Jarrod

[brejohns]

Great, thanks for all the help.

[NateO]

Hello, while this is dated,

Originally Posted by brejohns

I understand this formula =IRR(range), but I am looking for the formula that calculates based on diferent MONTHLY cash flows

Sure, the IRR function does this, e.g.,

******** ******************** ************************************************************************>

Microsoft Excel - More IRR.xls ___Running: xl2000 : OS = Windows Windows 2000
File Edit View Insert Options Tools Data Window Help About
B1C1D1E1F1A5B5A6B6 =
	A	B	C	D	E	F
1	01/01/2004	02/01/2004	03/01/2004	04/01/2004	05/01/2004	06/01/2004
2	-1000	100	100	100	100	100
3
4	Effective Annual Return	Monthly Return
5	21.31%	1.62%
6	21.31%	1.62%
Sheet1

[HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Taking Annual amounts and splitting them equally into monthly components. I can't be sure that I would do this, but the following seems to fire:

******** ******************** ************************************************************************>

Microsoft Excel - More IRR.xls ___Running: xl2000 : OS = Windows Windows 2000
File Edit View Insert Options Tools Data Window Help About
B1C1C2A4B4 =
	A	B	C	D
1	01/01/2001	01/01/2002	01/01/2003	*
2	-122	51	82	*
3	Effective*Monthly*IRR	Annual*IRR	*	*
4	0.45%	5.51%	*	*
Sheet3 *

[HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

As does:

******** ******************** ************************************************************************>

Microsoft Excel - More IRR.xls ___Running: xl2000 : OS = Windows Windows 2000
File Edit View Insert Options Tools Data Window Help About
A2A3B3A6B6 =
	A	B	C	D
1	01/01/2001	-122
2	01/01/2002	51
3	01/01/2003	82
4
5	Effective Monthly IRR	Annual IRR
6	0.45%	5.51%
Sheet4

[HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

You could change the monthly weighting by adjusting the 1 in the correct element position, e.g., 1 becomes .8 or 1.2, etc... As long as the array still added to 12.