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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 
Upvote 0
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
 
Upvote 0
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:
 
Upvote 0
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.
:)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
ABCDEFG
1-10002.57%ValueCountResult
2200-100012.57%
32002003
42001005
5100
6100
7100
8100
9100
Sheet1
Cell Formulas
RangeFormula
C1C1=IRR(A1:A9)
G2G2=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).
 
Upvote 0
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)

1632500598041.png


{=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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top