IRR without listing values

[erjay]

Hi there,

I'm trying to simplify how I use the IRR formula when calculating the IRR of a fixed income investment over a given term. Rather than listing out each years cashflow in cells A1:A5 and then using =IRR(A1:A5), I'd like to be able to create a formula along the lines of =IRR((annual cashlow*term)). Any advice on how to achieve this would be very much appreciated.

Cheers,

erjay

 

[Eric W]

Welcome to the forum.

The IRR requires at least one negative number, generally the first value, and at least one positive number. So given this setup:

	A	B	C	D
IRR	Start
Annual
Term
IRR

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]-300[/TD]

[TD="align: right"]-300[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]13%[/TD]

[TD="align: right"]100[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"]13%[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IRR(A1:A5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]{=IRR(IF(ROW(INDIRECT("1:"&D3))=1,D1,D2))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



in A1:A5, You get the IRR calculated in B2. You could get the same result by using the parameters in D1:D3, and the array formula in D4. Change the value in D3 to increase the number of years.

Hope this helps.

 

[erjay]

Thanks Eric, that is perfect!

Too complicate matters further, would it be possible to build in an assumed annual growth rate over the term? Say for example the income was to grow each year by 2.5%?

Cheers,

erjay

 

[Eric W]

Try this one:

	A	B	C	D
IRR	Start
Annual
Growth Rate
Term
IRR

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]-300[/TD]

[TD="align: right"]-300[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]14.203%[/TD]

[TD="align: right"]100[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]102.5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.50%[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]105.0625[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]107.6891[/TD]
[TD="align: right"][/TD]

[TD="align: right"]14.203%[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D5[/TH]
[TD="align: left"]{=IRR(IF(ROW(INDIRECT("1:"&D4))=1,D1,D2*(1+D3)^(ROW(INDIRECT("1:"&D4))-2)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



I may be able to get it a tad shorter, but that should do the trick.

Happy to help!

 

[sdmooseman]

Thanks!!! I've been trying to figure this out for a long time. Would it be possible to add a second stream of fixed cashflows. Example: In the original scenario above assume there were 5 more value in values in D7 through D11 that were 125?

I know this is a very old thread but would be greatly appreciated if someone could help.

 

[sdmooseman]

Sorry - I meant A7 through A11 not D7 through D11

 

[Eric W]

If you're going to put the actual values in column A, you can just use the IRR formula directly:

=IRR(A1:A11)

Or are you looking for a closed form formula where you have a starting value, a range of increasing values, then another range of constant values? While possible, it may be more complicated than just using the basic formula and listing the values.

 

[sdmooseman]

Thanks Eric,
I never actually thought I would get a response on a 5-year old thread.

This request was in response to the original question and not the increasing values part.
I was actually looking for two ranges of constant values without listing all of the values.
Example the first range has 60 values of x and the second range has 300 values of y for a total of 360 values.
I was hoping not to have to list the individual values similar to the original question.

Thoughts?

 

[Eric W]

I never actually thought I would get a response on a 5-year old thread.

First, a belated "Welcome to the forum!" Second, you're right, it was kind of a fluke that I noticed your post. You would have been better off starting a new thread. More likely to have been seen.

Anyway, how about something like this:

Book1
	A	B	C	D	E	F	G
1	-1000		2.57%		Value	Count	Result
2	200				-1000	1	2.57%
3	200				200	3
4	200				100	5
5	100
6	100
7	100
8	100
9	100
Sheet1

Cell Formulas
Range	Formula
C1	C1	=IRR(A1:A9)
G2	G2	=IRR(LOOKUP(SEQUENCE(SUM(F2:F10),,0),SUBTOTAL(9,OFFSET(F1,0,0,SEQUENCE(COUNT(F2:F10)))),E2:E10))

In column E, put the values you want, and in column F, put how many times you want those values. You can put up to 9 pairs of numbers without changing the formula (which is easy enough to do).

 

[sdmooseman]

Thanks again Eric!
That works great.
I've literally been searching for years how to do this.

I asked my son last night to take a look a it as well - studying computer science - and he came up with this solution which works great too.
(12* for monthly payments)

{=12*IRR(IF(ROW(INDIRECT("361:1"))=1,C2,IF(ROW(INDIRECT("361:1"))<=A2+1,B2,B3)),0)}

Thanks again and I've learned that I need to reach out sooner rather than later.