writer
6,694
edits
Changes
Jump to navigation
Jump to search
Line 1:
Line 1: − + − + − + − + − + + − + − + − + − + − XIRR shows the error value:+ − 1.whenever any number in dates precedes the starting date or values + − 2.<math>Dates</math> contain a different number of values.+ + + + + + + + + + + + + + + + + + + + + + + − Equation:+ − <math>\sum_{i=1}^N\dfrac{P_i}{(1+rate)^\tfrac{(date_i-date_0)}{365}}=0</math>+ − + − ==Example==+ − + − + − <font color="#000000"><font face="Arial, sans-serif"><font size="2">B C</font></font></font>+ + + + + + + + + + + + + + + + + + + − <font color="#000000"><font face="Arial, sans-serif"><font size="2">-10000 &!02-01-2009&!</font></font></font>+ + + + + − <font color="#000000"><font face="Arial, sans-serif"><font size="2">3000 04-01-2009</font></font></font> − − <font color="#000000"><font face="Arial, sans-serif"><font size="2">4300 11-30-2009</font></font></font> − − <font color="#000000"><font face="Arial, sans-serif"><font size="2">3250 03-15-2010</font></font></font> − − <font color="#000000"><font face="Arial, sans-serif"><font size="2">2200 05-01-2010</font></font></font> − − <font color="#000000"><font face="Arial, sans-serif"><font size="2">UNIQ2b4aa68f40f8e8e8-nowiki-00000002-QINU</font></font></font> − − </div> − ---- − <div id="10SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Syntax </div><div class="ZEditBox"><center></center></div></div> − ---- − <div id="4SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Remarks </div></div> − ---- − <div id="3SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Examples </div></div> − ---- − <div id="11SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Description </div></div> − ---- − <div id="2SpaceContent" class="zcontent" align="left"> − − {| id="TABLE3" class="SpreadSheet blue" − |- class="even" − | class=" " | − | Column1 − | class=" SelectTD SelectTD" | − <div id="2Space_Handle" title="Click and Drag to resize CALCI Column/Row/Cell. It is EZ!"></div><div id="2Space_Copy" title="Click and Drag over to AutoFill other cells."></div>Column2 − | Column3 − | Column4 − |- class="odd" − | class=" " | Row1 − | class="sshl_f " | -10000 − | class="sshl_f " | 02-01-2009 − | − | − |- class="even" − | class=" " | Row2 − | class="sshl_f" | 3000 − | class="sshl_f" | 04-01-2009 − | − | − |- class="odd" − | Row3 − | class="sshl_f " | 4300 − | class="sshl_f" | 11-30-2009 − | − | − |- class="even" − | Row4 − | class="sshl_f" | 3250 − | class="sshl_f" | 03-15-2010 − | − | − |- class="odd" − | class=" " | Row5 − | class=" " | 2200 − | class=" " | 05-01-2010 − | − | − |- class="even" − | Row6 − | class="sshl_f" | 0.3593 − | − | − | − |} − <div align="left">[[Image:calci1.gif]]</div></div>+ − ----
no edit summary
<div style="font-size:30px">'''XIRR(V, Dates, G)'''</div><br/>
<div style="font-size:30px">'''XIRR(v,d,g,ac,nt)'''</div><br/>
*<math>v</math> is the array of values.
*<math>V</math> is a series of cash flows which equivalent to a schedule of payments in dates.
*<math>d</math> is the date value.
*<math>Dates</math> is a schedule of payment dates which equivalent to the cash flow payments.
*<math>g</math> is the starting number is close to the result of XIRR.
*<math>G</math> is a number that you guess is close to the result of this function.
*<math>ac </math> is the accuracy value.
*<math>nt </math> is the number of iterations.
==Description==
==Description==
*This function computes the internal rate of return for a schedule of cash flows that is not essentially periodic.
*This function gives the internal rate of return for a series.
*This function suppose at least one positive cash flow and one negative cash flow.
*In this function the cash flows that occur at uniform intervals which means that is not necessarily periodic.
*The numbers in dates should be valid dates.
*To calculate the internal rate of return for a series in a periodic time ,we can use the IRR function.
*In most cases no need to provide guess
*<math>XIRR</math> is the <math>IRR</math> when the periodicity between cash flows is not equal.
*<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.
*In <math>XIRR(v,d,g,ac,nt)</math> ,<math>v</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>v</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.
*Then the first payment should be negative value.
*<math>d</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.
*And date should entered in [[DATE]] function format.
*Guess is a number that you guess is close to the result of <math>XIRR</math>.
*<math>ac</math> is the accuracy value .
*<math>nt</math> is the number of iterations.
*suppose when we are omitting the value of <math>nt</math>,by default it will consider as 10.
*The value of <math>v</math> can be array or reference argument contains text, logical values or empty cells, the values which are ignored.
*The <math>g</math> value is optional, when we are omitting the <math>g</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.
*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 <math>XIRR</math> is the interest rate corresponding to <math>XNPV = 0</math>.
*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 v and d is having different number of values.
==Examples==
{| class="wikitable"
|+Spreadsheet
|-
XIRR(V, dates, G)
! !! 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
==See Also==
*[[Manuals/calci/IRR | IRR ]]
*[[Manuals/calci/NPV | NPV ]]
*[[Manuals/calci/RATE | RATE ]]
*[[Manuals/calci/MIRR | MIRR ]]
==References==