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?

This is in Google Sheets.


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. – Kartick Vaddadi Mar 6 '16 at 3:43
  • The arrayformula with the negative number at the end does work, and gives me the correct answer. – Kartick Vaddadi 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. – Kartick Vaddadi Mar 6 '16 at 4:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.