Compound Rate (again!)

[Guest]

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

 

[JE McGimpsey]

One way:

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

 

[Guest]

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

 

[Bob Tarburton]

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

 

[Myrna Larson]

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%

 

[Bob Tarburton]

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)

 

[JE McGimpsey]

Oops - misedited without checking. Sorry!

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

 

[Myrna Larson]

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

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

 

[Guest]

THANX BOB, MYRNA & JEM

Oops - misedited without checking. Sorry!

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

 

[Bob Tarburton]

I think you have a typo there: 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