# IRR without listing values

#### erjay

##### New Member
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

##### MrExcel MVP
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:

ABCD
1-300IRRStart-300
210013%Annual100
3100Term5
4100IRR13%
5100
6100

</tbody>
Sheet6

Worksheet Formulas
CellFormula
B2=IRR(A1:A5)

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
D4{=IRR(IF(ROW(INDIRECT("1:"&D3))=1,D1,D2))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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

##### New Member
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

##### MrExcel MVP
Try this one:

ABCD
1-300IRRStart-300
210014.203%Annual100
3102.5Growth Rate2.50%
4105.0625Term5
5107.6891IRR14.203%

</tbody>
Sheet6

Array Formulas
CellFormula
D5{=IRR(IF(ROW(INDIRECT("1:"&D4))=1,D1,D2*(1+D3)^(ROW(INDIRECT("1:"&D4))-2)))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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

Happy to help!

1,082,335
Messages
5,364,686
Members
400,810
Latest member
elbashka

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...