Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
compound rate | Excel Discussion (Misc queries) | |||
compound interest | Excel Worksheet Functions | |||
Annual Percentage Rate | Excel Discussion (Misc queries) | |||
Are financial functions calculated based on compound interest? | Excel Worksheet Functions | |||
Excel function for FV of daily investment at compound rates | Excel Worksheet Functions |