Manuals/calci/XIRR

XIRR (CashFlowEvents,Dates,StartGuess,Accuracy,NoOfIterations)

• $CashFlowEvents$ is the array of values.
• $Dates$ is the date value.
• $StartGuess$ is the starting number is close to the result of XIRR.
• $Accuracy$ is the accuracy value.
• $NoOfIterations$ is the number of iterations.
  • XIRR(), returns the internal rate of return for a schedule of cash flows that is not necessarily periodic

Description

• This function gives the internal rate of return for a series.
• In this function the cash flows that occur at uniform intervals which means that is not necessarily periodic.
• To calculate the internal rate of return for a series in a periodic time ,we can use the IRR function.
• $XIRR$ is the $IRR$ when the periodicity between cash flows is not equal.
• $XIRR$ function uses the iterative process to calculate the returns, so it is not possible to trial by hand.
• So this is most powerful function in CALCI.
• In $XIRR(CashFlowEvents,Dates,StartGuess,Accuracy,NoOfIterations)$ ,$CashFlowEvents$ is an values which is the array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.
• Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle CashFlowEvents} must have atleast one positive and one negative value to find the internal rate of return.
• Normally the first payment is optional,so the corresponding payment that occurs at the beginning of the investment.
• Then the first payment should be negative value.
• Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle Dates} is the date value which is indicating the schedule of the payment.
• Also the first payment date is the beginning of the schedule of payments.
• And date should entered in DATE function format.
• Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle StartGuess} is the guess which is indicating the number that you guess is close to the result of Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle XIRR} .
• Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle Accuracy} is the accuracy value .
• Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle NoOfIterations} is the number of iterations.
• suppose when we are omitting the value of $NoOfIterations$,by default it will consider as 10.
• The value of Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle CashFlowEvents} can be array or reference argument contains text, logical values or empty cells, the values which are ignored.
• The Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle StartGuess} value is optional, when we are omitting the Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle StartGuess} value,by default it will consider the value as 10%(0.1).
• This function is using iterative technique, so we can't find a result after 100 iterations.
• So the rate is changed until:

    di=the ith, or last,payment date.
    d1=the 0th payment date.
    Pi=the ith or last payment.

• The rate of return calculated by Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle XIRR} is the interest rate corresponding to Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\displaystyle XNPV = 0} .
• This function will give the result as error when

  1. Any date is not a valid date or not in a date function format.
  2. Any date is precedes the starting date.
  3. Also CashFlowEvents and Dates is having different number of values.

Examples

Spreadsheet

	A	B
1	-25000	2/1/2012
2	5700	5/12/2012
3	4900	10/28/2012
4	13500	1/16/2013
5	10000	4/3/2013

1. =XIRR(A1:A5,B1:B5,0,0.1)=0.43578123326
2. =XIRR(A1:A5,B1:B5,0,0,2) = 0.43572667130
3. =XIRR(A1:A5,B1:B5,1,5,0) =0.18336350930

Related Videos

XIRR

See Also

• IRR
• NPV
• RATE
• MIRR

References

• Xirr

• List of Main Z Functions

• Z3 home