Calculating IRR in Excel

[aggarwalcma]

I have the same cash flows repeated for 10 years. Instead of listing them in a separate cells to calculate IRR is there a way were I can indicate the cash flow and then place the number of times to determine the IRR?

 

[joeu2004]

Please provide a concrete example. It is not clear what you mean by "the same cash flows" (plural).

If you mean that the periodic "payment" is the same, use the RATE function.

For example, with a loan of $100,000, ending balance of $20,000 and monthly payments of $1350,
=RATE(12*10, -1350, 100000, -20000) returns a monthly IRR of about 1.00% (1.00295619813352%).

 

[aggarwalcma]

Hi There, is there a better way to calculate the IRR when you have the following data:

IRR.xlsx
	A	B	C	D	E	F
3	IRR	15.03%				-200,000
4						40,000
5						40,000
6						40,000
7						40,000
8						30,000
9						40,000
10						40,000
11						40,000
12						40,000
13						58,000
IRR

Cell Formulas
Range	Formula
B3	B3	=IRR(F3:F13)

 

[joeu2004]

The frequency of the irregular cash flows starting in F4 is unclear.

Are those the 10 "repeated" annual cash flows "repeated"?

Or are those 10 monthly cash flows repeated 12 times for a total of 120 monthly cash flows?

The more complete and descriptive you are with your example, the sooner you might get a usable solution.

PS.... Your IRR formula suggests that the ending balance is zero after the last cash flow of 58,000. I suspect, instead, that you mean only that the last cash flow is 58,000, and you omitted or did not account for the ending balance. Am I right? If so, what is the ending balance after 10 years of "repeated" cash flows?

 

[aggarwalcma]

The frequency of the irregular cash flows starting in F4 is unclear.

Are those the 10 "repeated" annual cash flows "repeated"?

Or are those 10 monthly cash flows repeated 12 times for a total of 120 monthly cash flows?

The more complete and descriptive you are with your example, the sooner you might get a usable solution.

PS.... Your IRR formula suggests that the ending balance is zero after the last cash flow of 58,000. I suspect, instead, that you mean only that the last cash flow is 58,000, and you omitted or did not account for the ending balance. Am I right? If so, what is the ending balance after 10 years of "repeated" cash flows?

 

[aggarwalcma]

Hi There, here are some additional descriptions:

Chapter 13 In-Class Problems and Solutions.xlsx
	E	F	G
17	Now	-200,000
18	Year 1	40,000
19	Year 2	40,000
20	Year 3	40,000
21	Year 4	40,000
22	Year 5	30,000
23	Year 6	40,000
24	Year 7	40,000
25	Year 8	40,000
26	Year 9	40,000
27	Year 10	58,000	Last
Problem 2

Cell Formulas
Range	Formula
F17	F17	=C10
F22	F22	=C12+C11
F27	F27	=C12+C13

 

[joeu2004]

Originally, you wrote: ``is there a way [where] I can indicate the cash flow and then place the number of times to determine the IRR``.

I demonstrated that with the RATE function.

In your last example, I see nothing "repeated" for 10 years. Instead, I see 10 unequal annual cash flows.

(Moreover, there is no "additional description". And your formulas are useless. They refer to column C, but you do not show us what is in column C. In the previous example, column C is blank.)

Perhaps you simply want:

=IRR(-200000, 40000, 40000, 40000, 40000, 30000, 40000, 40000, 40000, 40000, 58000)

I doubt it; but that is the best I can infer from your "examples". I have to rush out. Good luck!

 

[joeu2004]

Errata, too late to edit (sigh)....

there is no "additional description"

Okay, "year 1", "year 2", etc are indeed additional "description". I was hoping for more words -- a more complete description. No matter. I'm done.

 

[joeu2004]

Arrgghh! Another dreaded typo, which we cannot edit after 10 min (heavy sigh)....

=IRR(-200000, 40000, 40000, 40000, 40000, 30000, 40000, 40000, 40000, 40000, 58000)

I meant: =IRR( { -200000, 40000, 40000, 40000, 40000, 30000, 40000, 40000, 40000, 40000, 58000 } )