Google Sheets – Use IRR Formula Without Listing Each Investment

formulasgoogle sheets

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.

Best Answer

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."

Related Topic