# Thread: Need Help for FV and XIRR (2003)

1. ## 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

2. ## 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. ## Re: Need Help for FV and XIRR (2003)

<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,

4. ## 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
•