Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default How to calculate the RATE in annuity?

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

  #2   Report Post  
Gary L Brown
 
Posts: n/a
Default How to calculate the RATE in annuity?

Using the XIRR worksheet function that comes with the 'Analysis ToolPak' add-in
To activate, go to TOOLSADDINS
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

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


" wrote:

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


  #3   Report Post  
 
Posts: n/a
Default How to calculate the RATE in annuity?

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

Gary L Brown wrote:
Using the XIRR worksheet function that comes with the 'Analysis ToolPak' add-in
To activate, go to TOOLSADDINS
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

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


" wrote:

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
calculate sales run rate LoriM Excel Discussion (Misc queries) 2 May 4th 23 07:47 PM
Calculate Interest Rate when payment changes! huntermcg Excel Worksheet Functions 2 October 25th 05 08:32 AM
How to calculate the accumulated payments from an annuity at yr 10 jmuirman Excel Worksheet Functions 1 August 8th 05 08:05 PM
How do I calculate the accumulated payments from an annuity at yr jmuirman New Users to Excel 2 August 7th 05 04:29 PM
How do you calculate rate of return on monthly cash flows Philly Fan Excel Worksheet Functions 1 February 16th 05 05:24 AM


All times are GMT +1. The time now is 11:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"