XIRR(values, dates [,guess])

Returns the interest rate for a series of unequal cash flows at irregular intervals.

valuesA series of unequal cash flows.
datesA series of payment dates that correspond to the cash flows.
guess(Optional) An estimate as to what the rate will be.

Remarks

* This function uses an iterative approach to find a solution.
* If the successive results of this function do not converge to within 2.98 E-09 after 100 iterations, then #NUM! is returned.
* This function assumes all payments are at the end of each period.
* A negative number represents cash that you pay out.
* A positive number represents cash that you receive (start with or end with).
* All succeeding payments are discounted based on a 365-day year.
* The series of values must contain at least one positive and one negative value.
* The "dates" must all be later than the first date, but they may occur in any order.
* If "dates" contain any numbers, they are truncated to integers.
* If any number in "dates" is not a valid date, then #NUM! is returned.
* If any number in "dates" precedes the starting date, then #NUM! is returned.
* If "values" and "dates" contain a different number of values, then #NUM! is returned.
* If "guess" is left blank, then 0.1 (or 10%) is used.
* Dates should be entered using the DATE function as problems can occur when dates are entered as text.
* This function can be used to calculate the Compound Annual Growth Rate (CAGR).
* You can use the RATE function to return the interest rate for a series of equal cash flows at regular intervals.
* You can use the IRR function to return the interest rate for a series of unequal cash flows at regular intervals (implicit reinvestment rate).
* You can use the MIRR function to return the interest rate for a series of unequal cash flows at regular intervals (explicit reinvestment rate)
* You can use the XNPV function to return the present value for a series of unequal cash flows at irregular intervals.
* In Excel 2010 the accuracy of this function was improved.
* In Excel 2003 this function was only available when you had the Analysis ToolPak add-in loaded.
* For the Microsoft documentation refer to support.office.com

 A
1=XIRR({-10000,2750,4250,3250,2750},{"1/1/1998","3/1/1998","10/3/1998","2/5/1999","4/1/2000"}) = 44.90%
2=XIRR({-10000,2750,4250,3250,2750},{"1/1/2008","3/1/2008","30/10/2009","15/2/2009","1/4/2009"},0.1) = 27.17%
3=XIRR({-10000,2750},{"1/1/2008"},0.1) = #NUM!


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top