Difference between revisions of "Manuals/calci/XIRR"
Jump to navigation
Jump to search
| (5 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | <div style="font-size:30px">'''XIRR( | + | <div style="font-size:30px">'''XIRR (CashFlowEvents,Dates,StartGuess,Accuracy,NoOfIterations)'''</div><br/> |
| − | *<math> | + | |
| − | *<math> | + | *<math>CashFlowEvents</math> is the array of values. |
| − | *<math> | + | *<math>Dates</math> is the date value. |
| − | *<math> | + | *<math>StartGuess</math> is the starting number is close to the result of XIRR. |
| − | *<math> | + | *<math>Accuracy</math> is the accuracy value. |
| + | *<math>NoOfIterations </math> is the number of iterations. | ||
| + | **XIRR(), returns the internal rate of return for a schedule of cash flows that is not necessarily periodic | ||
==Description== | ==Description== | ||
| Line 13: | Line 15: | ||
*<math>XIRR</math> function uses the iterative process to calculate the returns, so it is not possible to trial by hand. | *<math>XIRR</math> 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. | *So this is most powerful function in CALCI. | ||
| − | *In <math>XIRR( | + | *In <math>XIRR (CashFlowEvents,Dates,StartGuess,Accuracy,NoOfIterations)</math> ,<math>CashFlowEvents</math> 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. |
| − | *<math> | + | *<math>CashFlowEvents</math> 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. | *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. | *Then the first payment should be negative value. | ||
| − | *<math> | + | *<math>Dates</math> 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. | *Also the first payment date is the beginning of the schedule of payments. | ||
*And date should entered in [[Manuals/calci/DATE | DATE ]] function format. | *And date should entered in [[Manuals/calci/DATE | DATE ]] function format. | ||
| − | *<math> | + | *<math>StartGuess</math> is the guess which is indicating the number that you guess is close to the result of <math>XIRR</math>. |
| − | *<math> | + | *<math>Accuracy</math> is the accuracy value . |
| − | *<math> | + | *<math>NoOfIterations</math> is the number of iterations. |
| − | *suppose when we are omitting the value of <math> | + | *suppose when we are omitting the value of <math>NoOfIterations</math>,by default it will consider as 10. |
| − | *The value of <math> | + | *The value of <math>CashFlowEvents</math> can be array or reference argument contains text, logical values or empty cells, the values which are ignored. |
| − | *The <math> | + | *The <math>StartGuess</math> value is optional, when we are omitting the <math>StartGuess</math> 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. | *This function is using iterative technique, so we can't find a result after 100 iterations. | ||
*So the rate is changed until: | *So the rate is changed until: | ||
| Line 35: | Line 37: | ||
1. Any date is not a valid date or not in a date function format. | 1. Any date is not a valid date or not in a date function format. | ||
2. Any date is precedes the starting date. | 2. Any date is precedes the starting date. | ||
| − | 3. Also | + | 3. Also CashFlowEvents and Dates is having different number of values. |
==Examples== | ==Examples== | ||
| Line 61: | Line 63: | ||
#=XIRR(A1:A5,B1:B5,0,0,2) = 0.43572667130 | #=XIRR(A1:A5,B1:B5,0,0,2) = 0.43572667130 | ||
#=XIRR(A1:A5,B1:B5,1,5,0) =0.18336350930 | #=XIRR(A1:A5,B1:B5,1,5,0) =0.18336350930 | ||
| + | |||
| + | ==Related Videos== | ||
| + | |||
| + | {{#ev:youtube|f0KUuHVscaI|280|center|XIRR}} | ||
==See Also== | ==See Also== | ||
| Line 70: | Line 76: | ||
==References== | ==References== | ||
*[https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_XIRR_function Xirr] | *[https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_XIRR_function Xirr] | ||
| + | |||
| + | |||
| + | |||
| + | |||
| + | *[[Z_API_Functions | List of Main Z Functions]] | ||
| + | |||
| + | *[[ Z3 | Z3 home ]] | ||
Latest revision as of 17:15, 30 July 2018
XIRR (CashFlowEvents,Dates,StartGuess,Accuracy,NoOfIterations)
- 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} is the array of values.
- 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.
- 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 starting number is close to the result of 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.
- 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.
- 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 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 IRR} when the periodicity between cash flows is not equal.
- 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} 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 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 (CashFlowEvents,Dates,StartGuess,Accuracy,NoOfIterations)} ,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} 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 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} ,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
| 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 |
- =XIRR(A1:A5,B1:B5,0,0.1)=0.43578123326
- =XIRR(A1:A5,B1:B5,0,0,2) = 0.43572667130
- =XIRR(A1:A5,B1:B5,1,5,0) =0.18336350930
Related Videos
See Also
References