Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2007
    Location
    India
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Help for FV and XIRR (2003)

    Problem 1:
    Rate of Interest (ROI): 7.50% - Compounded Quarterly
    Annual Rate Compounded: =FV(7.50%/4,4,0,-1)-1 ie 0.0771

    Minimum Investment=Rs. 10/- per month
    Term = 5 years

    What will be FV formula to calculate the Net Return?

    Net Return should be: Rs. 728.9, Since XIRR showing 7.71% for Rs. 10/- pm


    Problem 2:

    I would like to know whether the xirr calculation showing correct result.

    First a person invest Rs. 100000 in a MIS (Monthly Income Scheme) for 6 years from which he will get Rs. 666 per month as return. At the end of the scheme the Capital will be refunded to the investor.
    Second the aforesaid Rs. 666 will reinvest into a Recurring Deposit (mentioned above) automatically.
    What will be Internal Rate of Return?
    In my calculation it is showing Less than the First calculation. In my opinion it should be more than First XIRR calculaton.

    Example:


    MIS RD MIS+RD

    XIRR= 8.19% 7.71% 8.11%

    1 -100000 -100000 01/04/2007
    2 666 -666 0 01/05/2007
    3 666 -666 0 01/06/2007
    4 666 -666 0 01/07/2007
    5 666 -666 0 01/08/2007
    6 666 -666 0 01/09/2007
    7 666 -666 0 01/10/2007
    8 666 -666 0 01/11/2007
    9 666 -666 0 01/12/2007
    10 666 -666 0 01/01/2008
    11 666 -666 0 01/02/2008
    12 666 -666 0 01/03/2008 50
    13 666 -666 0 01/04/2008
    14 666 -666 0 01/05/2008
    15 666 -666 0 01/06/2008
    16 666 -666 0 01/07/2008
    17 666 -666 0 01/08/2008
    18 666 -666 0 01/09/2008
    19 666 -666 0 01/10/2008
    20 666 -666 0 01/11/2008
    21 666 -666 0 01/12/2008
    22 666 -666 0 01/01/2009
    23 666 -666 0 01/02/2009
    24 666 -666 0 01/03/2009 145
    25 666 -666 0 01/04/2009
    26 666 -666 0 01/05/2009
    27 666 -666 0 01/06/2009
    28 666 -666 0 01/07/2009
    29 666 -666 0 01/08/2009
    30 666 -666 0 01/09/2009
    31 666 -666 0 01/10/2009
    32 666 -666 0 01/11/2009
    33 666 -666 0 01/12/2009
    34 666 -666 0 01/01/2010
    35 666 -666 0 01/02/2010
    36 666 -666 0 01/03/2010
    37 666 -666 0 01/04/2010
    38 666 -666 0 01/05/2010
    39 666 -666 0 01/06/2010
    40 666 -666 0 01/07/2010
    41 666 -666 0 01/08/2010
    42 666 -666 0 01/09/2010
    43 666 -666 0 01/10/2010
    44 666 -666 0 01/11/2010
    45 666 -666 0 01/12/2010
    46 666 -666 0 01/01/2011
    47 666 -666 0 01/02/2011
    48 666 -666 0 01/03/2011
    49 666 -666 0 01/04/2011
    50 666 -666 0 01/05/2011
    51 666 -666 0 01/06/2011
    52 666 -666 0 01/07/2011
    53 666 -666 0 01/08/2011
    54 666 -666 0 01/09/2011
    55 666 -666 0 01/10/2011
    56 666 -666 0 01/11/2011
    57 666 -666 0 01/12/2011
    58 666 -666 0 01/01/2012
    59 666 -666 0 01/02/2012
    60 666 -666 0 01/03/2012
    61 666 -666 0 01/04/2012
    62 666 48544.74 49210.74 01/05/2012
    63 666 666 01/06/2012
    64 666 666 01/07/2012
    65 666 666 01/08/2012
    66 666 666 01/09/2012
    67 666 666 01/10/2012
    68 666 666 01/11/2012
    69 666 666 01/12/2012
    70 666 666 01/01/2013
    71 666 666 01/02/2013
    72 666 666 01/03/2013
    73 100000 100000 01/04/2013
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Need Help for FV and XIRR (2003)

    Dear Rushatiindia:

    Welcome to the Lounge.

    I think I have an answer to Problem 1

    =FV(0.075/4,60,10,,0) which 1,092.43
    Less Amounts Invested 600 gives net return of 492.43
    I check the result using a book of compound and annuities tables.

    As to problem 2, I leave that to someone else in the Lounge.

    Tom Duthie

  3. #3
    New Lounger
    Join Date
    Apr 2007
    Location
    India
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help for FV and XIRR (2003)

    At the outset I thank you for your reply. Please check the following:

    <hr>XIRR= 7.71%
    1 -100.000 01/04/2007
    2 -100.000 01/05/2007
    3 -100.000 01/06/2007
    4 -100.000 01/07/2007
    5 -100.000 01/08/2007
    6 -100.000 01/09/2007
    7 -100.000 01/10/2007
    8 -100.000 01/11/2007
    9 -100.000 01/12/2007
    10 -100.000 01/01/2008
    11 -100.000 01/02/2008
    12 -100.000 01/03/2008
    13 -100.000 01/04/2008
    14 -100.000 01/05/2008
    15 -100.000 01/06/2008
    16 -100.000 01/07/2008
    17 -100.000 01/08/2008
    18 -100.000 01/09/2008
    19 -100.000 01/10/2008
    20 -100.000 01/11/2008
    21 -100.000 01/12/2008
    22 -100.000 01/01/2009
    23 -100.000 01/02/2009
    24 -100.000 01/03/2009
    25 -100.000 01/04/2009
    26 -100.000 01/05/2009
    27 -100.000 01/06/2009
    28 -100.000 01/07/2009
    29 -100.000 01/08/2009
    30 -100.000 01/09/2009
    31 -100.000 01/10/2009
    32 -100.000 01/11/2009
    33 -100.000 01/12/2009
    34 -100.000 01/01/2010
    35 -100.000 01/02/2010
    36 -100.000 01/03/2010
    37 -100.000 01/04/2010
    38 -100.000 01/05/2010
    39 -100.000 01/06/2010
    40 -100.000 01/07/2010
    41 -100.000 01/08/2010
    42 -100.000 01/09/2010
    43 -100.000 01/10/2010
    44 -100.000 01/11/2010
    45 -100.000 01/12/2010
    46 -100.000 01/01/2011
    47 -100.000 01/02/2011
    48 -100.000 01/03/2011
    49 -100.000 01/04/2011
    50 -100.000 01/05/2011
    51 -100.000 01/06/2011
    52 -100.000 01/07/2011
    53 -100.000 01/08/2011
    54 -100.000 01/09/2011
    55 -100.000 01/10/2011
    56 -100.000 01/11/2011
    57 -100.000 01/12/2011
    58 -100.000 01/01/2012
    59 -100.000 01/02/2012
    60 -100.000 01/03/2012
    61 7289.000 01/04/2012<hr>

    In case of investment of Rs. 10/- the Net Return is Rs. 728.9/- which does not match with your answer.

    The Internal Rate of Return through XIRR function is matching with the FV function ie 0.0771 or 7.71%. Then the Net Return Value should be same through FV function ie Rs. 7289/- (for investment of Rs. 100/- pm).

    In your FV function you have mentioned the type is 0 which should be 1, since the investment is being occured at the begining of the month.

    Please check the the attached file (finsave.xls)

    Regards,
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts

    Re: Need Help for FV and XIRR (2003)

    Rushatiindia:
    After review I beleive your problem is one of decimal places. The acutal annual rate for 7.50% compounded quarterly is not .0771 rather it is .0771358568. When you use this in the formula
    FV(.0771358568/12,60,10,1) The result is 730.78. To have an exact match to the XIRR computation you would need to use the same decimal percision in both formulas. What that percision is would be up to the user.

    I think the same would be true to you second question.

    Regards,

    Tom Duthie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •