How to calculate the RATE in annuity?

[dstdst123]

Hi there,

Need your help.

On 1 Nov 2005, I bought a unit trust, with initial payment US$5000.
Subsequently, each month-end (starting 31 Nov 05), I put in US$200 for
a period of two years in a row.

On 1 Dec 2007, I will get back US$ 11,000 (including the initial
payment)

Question:
1) What is the interest rate throughout annuity period?

2) How to use the Microsoft Excel to calculate?

Thanks. Please e-mail me directly!

cheers
dstdst

 

[Guest]

Using the XIRR worksheet function that comes with the 'Analysis ToolPak' add-in
To activate, go to TOOLS>ADDINS
Check the 'Analysis ToolPak' box and select OK.

Assumptions -
List of Dates: B3:B28
List of Values: A3:A28
25 payments out and 1 payment in

Internal Rate of Return is: 7.544%
=XIRR(B3:B28,A3:A28,7.5)

11/01/2005 -5000
12/01/2005 -200
01/01/2006 -200
02/01/2006 -200
03/01/2006 -200
04/01/2006 -200
05/01/2006 -200
06/01/2006 -200
07/01/2006 -200
08/01/2006 -200
09/01/2006 -200
10/01/2006 -200
11/01/2006 -200
12/01/2006 -200
01/01/2007 -200
02/01/2007 -200
03/01/2007 -200
04/01/2007 -200
05/01/2007 -200
06/01/2007 -200
07/01/2007 -200
08/01/2007 -200
09/01/2007 -200
10/01/2007 -200
11/01/2007 -200
12/01/2007 11000

HTH,

 

[dstdst123]

Thanks a lot, Gary. That's help!!

Using the XIRR worksheet function that comes with the 'Analysis ToolPak' add-in
To activate, go to TOOLS>ADDINS
Check the 'Analysis ToolPak' box and select OK.

Assumptions -
List of Dates: B3:B28
List of Values: A3:A28
25 payments out and 1 payment in

Internal Rate of Return is: 7.544%
=XIRR(B3:B28,A3:A28,7.5)

11/01/2005 -5000
12/01/2005 -200
01/01/2006 -200
02/01/2006 -200
03/01/2006 -200
04/01/2006 -200
05/01/2006 -200
06/01/2006 -200
07/01/2006 -200
08/01/2006 -200
09/01/2006 -200
10/01/2006 -200
11/01/2006 -200
12/01/2006 -200
01/01/2007 -200
02/01/2007 -200
03/01/2007 -200
04/01/2007 -200
05/01/2007 -200
06/01/2007 -200
07/01/2007 -200
08/01/2007 -200
09/01/2007 -200
10/01/2007 -200
11/01/2007 -200
12/01/2007 11000

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".