I think this is the right formula for it. Hope it can be of help to you.
= IRR(values, guess)
Values : An array or references to cells containing the data needed for the actual yield calculation.
- Values must contain at least 1 negative value and 1 positive value.
- IRR() uses the order of values as the cash flow order. Care should therefore be taken to ensure that payment or income orders are always entered correctly.
- IRR() only calculates numeric values inside arrays or references of values; and empty cells, logical values, text or error values will be ignored.
Guess : An estimated % close to the result of IRR(). If omitted, guess = 10% by default.
- Excel uses the loop function in IRR calculations. Starting with guess, the IRR iterates until the result is correct within 0.00001%. If the IRR cannot produce a result after 20 iterations, the IRR will return the #NUM!
- In case the IRR returns the #NUM! error value, or if the result does not approximate the expected value, try again with a different guess value.