# Can I use the IRR formula without listing out each investment?

Let's say I've invested ₹1 in a mutual fund every month for the last 88 months, with a resulting corpus of ₹236.

I want to find the annualised rate of return using the IRR formula. To do so, I have to list 1 88 times, from A1 to A88, then enter -236 in AB9, and in A90 enter =IRR(A1:A89).

Is there a way I can tell the IRR formula to assume the same amount invested 88 times rather than listing it out 88 times? Or is there some other formula that is a special case of IRR and works simpler for this case?

To avoid listing `1` 88 times, you can use `1+0*row(1:88)` within arrayformula:

``````=IRR(arrayformula({1+0*row(1:88); -236}))
``````

I'm leaving aside the issue of how this aligns with IRR documentation (which says that the first amount must be negative), and the fact that in my spreadsheet the above formula returns an error "IRR attempted to compute the internal rate of return for a series of cash flows, but was unable to."

• RATE(88, 1, -236) returns -2, which doesn't make sense since the investment has had a positive return according to the IRR formula. Using -236, 1,1, 1, ... , 1 with the IRR function doesn't work either — it again gives me a negative number. Mar 6 '16 at 3:43
• The arrayformula with the negative number at the end does work, and gives me the correct answer. Mar 6 '16 at 3:54
• Okay, I edited the answer to reflect only one specific point.
– user79865
Mar 6 '16 at 4:08
• Thanks, Sally. The error you mentioned goes away when you give it a guess of 1% as a second argument. That's independent of whether you use arrayformula or list out the number 88 times. Mar 6 '16 at 4:14