Compound Rate (again!)
 

I deposit Rs. 2574 each year for 21 years

At the end of 21 years I get Rs. 129635

How do I understand the Compound Rate thats' been applied

Regards & Thanx
--
Dr. Sachin Wagh
MBBS, DHA, DPH

One way:

=RATE(21,-2574,1,129635)

In article ,
Dr. Sachin Wagh wrote:

I deposit Rs. 2574 each year for 21 years

At the end of 21 years I get Rs. 129635

How do I understand the Compound Rate thats' been applied

Regards & Thanx

SORRY, Not been able to get the desired - it's giving the wrong result

let me give another example
Deposit 1000 every year for 5 years
@ 5%
Year 1: 1000 1050
Year 2: 2050 2153
Year 3: 3153 3310
Year 4: 4310 4526
Year 5: 5526 5802

=RATE(5,-1000,1,5802) yeilds 7.46% instead of 5%

Please help





"JE McGimpsey" wrote:

One way:

=RATE(21,-2574,1,129635)

In article ,
Dr. Sachin Wagh wrote:

I deposit Rs. 2574 each year for 21 years

At the end of 21 years I get Rs. 129635

How do I understand the Compound Rate thats' been applied

Regards & Thanx

Hi
compound_return=(total_return)^(1/((date_final-date_initial)/365))-1
Where total_return=final_value/intitial_investment
Final value=5802
Initial investment=5000
final date =today() or your sell date

the trick is that the date_initial is a weighted average of the
investment dates. I
n this case where you deposit = amounts each time, it's a straight
average.

Remember, Excel treats your dates as a real number, so it's
straightforward:
date1*investment1
+date2*investment2
....
+date(n)*investment(n)
divided by
sum of investments



On Wed, 2 Mar 2005 10:53:04 -0800, Dr. Sachin Wagh
wrote:

SORRY, Not been able to get the desired - it's giving the wrong result

let me give another example
Deposit 1000 every year for 5 years
@ 5%
Year 1: 1000 1050
Year 2: 2050 2153
Year 3: 3153 3310
Year 4: 4310 4526
Year 5: 5526 5802

=RATE(5,-1000,1,5802) yeilds 7.46% instead of 5%

Please help





"JE McGimpsey" wrote:

One way:

=RATE(21,-2574,1,129635)

In article ,
Dr. Sachin Wagh wrote:

I deposit Rs. 2574 each year for 21 years

At the end of 21 years I get Rs. 129635

How do I understand the Compound Rate thats' been applied

Regards & Thanx

The formula you were given isn't quite right. You need to use the 5th argument
to indicate the payment is made at the beginning of the year rather than the
end, i.e.

=RATE(5,-1000,0,5802,1) = 5%

On Wed, 2 Mar 2005 10:53:04 -0800, Dr. Sachin Wagh
wrote:

SORRY, Not been able to get the desired - it's giving the wrong result

let me give another example
Deposit 1000 every year for 5 years
@ 5%
Year 1: 1000 1050
Year 2: 2050 2153
Year 3: 3153 3310
Year 4: 4310 4526
Year 5: 5526 5802

=RATE(5,-1000,1,5802) yeilds 7.46% instead of 5%

Please help





"JE McGimpsey" wrote:

One way:

=RATE(21,-2574,1,129635)

In article ,
Dr. Sachin Wagh wrote:

I deposit Rs. 2574 each year for 21 years

At the end of 21 years I get Rs. 129635

How do I understand the Compound Rate thats' been applied

Regards & Thanx

Myrna got it.
Just wanted to note that all that jazz about weighted average in my
answer can be expressed as
date_initial=SUMPRODUCT(range_of_investment_dates: range_of_investment_values)/SUM(range_of_investment_values)

On Wed, 02 Mar 2005 15:03:52 -0500, Bob Tarburton
wrote:


Hi
compound_return=(total_return)^(1/((date_final-date_initial)/365))-1
Where total_return=final_value/intitial_investment
Final value=5802
Initial investment=5000
final date =today() or your sell date

the trick is that the date_initial is a weighted average of the
investment dates. I
n this case where you deposit = amounts each time, it's a straight
average.

Remember, Excel treats your dates as a real number, so it's
straightforward:
date1*investment1
+date2*investment2
...
+date(n)*investment(n)
divided by
sum of investments



On Wed, 2 Mar 2005 10:53:04 -0800, Dr. Sachin Wagh
wrote:

SORRY, Not been able to get the desired - it's giving the wrong result

let me give another example
Deposit 1000 every year for 5 years
@ 5%
Year 1: 1000 1050
Year 2: 2050 2153
Year 3: 3153 3310
Year 4: 4310 4526
Year 5: 5526 5802

=RATE(5,-1000,1,5802) yeilds 7.46% instead of 5%

Please help





"JE McGimpsey" wrote:

One way:

=RATE(21,-2574,1,129635)

In article ,
Dr. Sachin Wagh wrote:

I deposit Rs. 2574 each year for 21 years

At the end of 21 years I get Rs. 129635

How do I understand the Compound Rate thats' been applied

Regards & Thanx

Oops - misedited without checking. Sorry!

=RATE(21,-2574,0,129635,1)




In article ,
Dr. Sachin Wagh wrote:

SORRY, Not been able to get the desired - it's giving the wrong result

let me give another example
Deposit 1000 every year for 5 years

I think you have a typo the colon instead of comma, i.e. should be

date_initial=SUMPRODUCT(range_of_investment_dates, range_of_investment_values)/SUM(range_of_investment_values)

On Wed, 02 Mar 2005 15:44:42 -0500, Bob Tarburton
wrote:

Myrna got it.
Just wanted to note that all that jazz about weighted average in my
answer can be expressed as
date_initial=SUMPRODUCT(range_of_investment_dates :range_of_investment_values)/SUM(range_of_investment_values)

On Wed, 02 Mar 2005 15:03:52 -0500, Bob Tarburton
wrote:


Hi
compound_return=(total_return)^(1/((date_final-date_initial)/365))-1
Where total_return=final_value/intitial_investment
Final value=5802
Initial investment=5000
final date =today() or your sell date

the trick is that the date_initial is a weighted average of the
investment dates. I
n this case where you deposit = amounts each time, it's a straight
average.

Remember, Excel treats your dates as a real number, so it's
straightforward:
date1*investment1
+date2*investment2
...
+date(n)*investment(n)
divided by
sum of investments



On Wed, 2 Mar 2005 10:53:04 -0800, Dr. Sachin Wagh
wrote:

SORRY, Not been able to get the desired - it's giving the wrong result

let me give another example
Deposit 1000 every year for 5 years
@ 5%
Year 1: 1000 1050
Year 2: 2050 2153
Year 3: 3153 3310
Year 4: 4310 4526
Year 5: 5526 5802

=RATE(5,-1000,1,5802) yeilds 7.46% instead of 5%

Please help





"JE McGimpsey" wrote:

One way:

=RATE(21,-2574,1,129635)

In article ,
Dr. Sachin Wagh wrote:

I deposit Rs. 2574 each year for 21 years

At the end of 21 years I get Rs. 129635

How do I understand the Compound Rate thats' been applied

Regards & Thanx

THANX BOB, MYRNA & JEM

"JE McGimpsey" wrote:

Oops - misedited without checking. Sorry!

=RATE(21,-2574,0,129635,1)




In article ,
Dr. Sachin Wagh wrote:

SORRY, Not been able to get the desired - it's giving the wrong result

let me give another example
Deposit 1000 every year for 5 years

On Wed, 02 Mar 2005 20:16:10 -0600, Myrna Larson
wrote:

I think you have a typo the colon instead of comma, i.e. should be

date_initial=SUMPRODUCT(range_of_investment_dates ,range_of_investment_values)/SUM(range_of_investment_values)

It's a good thing you're watching the rest of us on this thread ;)
Thanks

On Wed, 02 Mar 2005 15:44:42 -0500, Bob Tarburton
wrote:

Myrna got it.
Just wanted to note that all that jazz about weighted average in my
answer can be expressed as
date_initial=SUMPRODUCT(range_of_investment_date s:range_of_investment_values)/SUM(range_of_investment_values)

On Wed, 02 Mar 2005 15:03:52 -0500, Bob Tarburton
wrote:


Hi
compound_return=(total_return)^(1/((date_final-date_initial)/365))-1
Where total_return=final_value/intitial_investment
Final value=5802
Initial investment=5000
final date =today() or your sell date

the trick is that the date_initial is a weighted average of the
investment dates. I
n this case where you deposit = amounts each time, it's a straight
average.

Remember, Excel treats your dates as a real number, so it's
straightforward:
date1*investment1
+date2*investment2
...
+date(n)*investment(n)
divided by
sum of investments



On Wed, 2 Mar 2005 10:53:04 -0800, Dr. Sachin Wagh
wrote:

SORRY, Not been able to get the desired - it's giving the wrong result

let me give another example
Deposit 1000 every year for 5 years
@ 5%
Year 1: 1000 1050
Year 2: 2050 2153
Year 3: 3153 3310
Year 4: 4310 4526
Year 5: 5526 5802

=RATE(5,-1000,1,5802) yeilds 7.46% instead of 5%

Please help





"JE McGimpsey" wrote:

One way:

=RATE(21,-2574,1,129635)

In article ,
Dr. Sachin Wagh wrote:

I deposit Rs. 2574 each year for 21 years

At the end of 21 years I get Rs. 129635

How do I understand the Compound Rate thats' been applied

Regards & Thanx