IRR without listing values

erjay

New Member
Joined
Mar 6, 2017
Messages
2
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
Joined
Aug 18, 2015
Messages
8,996
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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet6

Worksheet Formulas
CellFormula
B2=IRR(A1:A5)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

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

<thead>
</thead><tbody>
</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
Joined
Mar 6, 2017
Messages
2
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
Joined
Aug 18, 2015
Messages
8,996
Try this one:

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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet6

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

<thead>
</thead><tbody>
</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! :cool:
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top