Compounded rate of return?

**existent80** · 10-28-2010, 03:29 PM

Hi, thanks to all for this super-handy forum. I've learned a lot and finally had a reason to post.

I have investments that give me a monthly return, say like a mutual fund. I want to get a yearly number based on this performance.

So for example
Jan. 0.43%
Feb. -0.83%
Mar. 1.76%
Apr. 1.44%
YTD 2.81%

I know it's not a straight sum... so... what is it?

**bentleybob** · 10-28-2010, 07:07 PM

It's the product of 1 + rate (month 1) times 1 + rate (month 2) etc.

So (1 + .0043) * (1 - .0081) * (1 + .0176) * (1 + .0144) equals 1.02808..., or an increase of 2.81% YTD.

Does this answer your question?

**Richard Buttrey** · 10-28-2010, 07:34 PM

Hi,

....or using an Excel function and assuming dates are month end dates, i.e. 1/31/10, 2/28/10, 3/31/10, 4/30/10
and the interest rates in B1:B4

Please Login or Register  to view this content.

Rgds

**existent80** · 10-29-2010, 06:01 PM

BBob & Richard, thanks!

I'm using these to try to replicate numbers I do have, that way I know I'm on the right track. I'll let you know how it's working out... I will certainly have follow-up questions!

Right now FVSCHEDULE is great and replicates correctly my numbers for a full year, but I'm not getting a match-up to my accountant's numbers for 2010 (partial year).

Also, not sure why the -1 is there?
=(FVSCHEDULE(1,B1:B4)-1)*100

Thanks again... you guys are awesome!

**shg** · 10-29-2010, 06:06 PM

What are the numbers and what is his result?

**Richard Buttrey** · 10-30-2010, 11:07 AM

Hi,

Although I said I was assuming month end dates, to be more accurate the formula assumes that all interest rates apply for exactly the same period of time. i.e. the periods might be one month, one day, one hour or whatever.
The formula is simply working out the compound interest rate for the schedule of periods and it implicitly assumes that each interest rate is in play for exactly the same length of time as any other.

The -1 is there because the FVSCHEDULE() function's primary purpose is to tell you what will be the future value of a sum of money invested at the various interest rates. That's why the first element in the formula is for the Principal investment value. In your case you just wanted the compound interest rate so I set the Principal value to be 1 and deducted this 1 from the FVSCHEDULE() result.

Differences to your accountants results may be because the time periods are in fact different which will be the case with interest rates applying for the different numbers of days in each month.
Regards

**existent80** · 01-05-2011, 03:42 PM

Hi again. Sorry to abandon the thread but I've had other priorities going on.
I think I have the formula right but something's still screwy:

My numbers:
2001 14.6%
2002 9.60%
2003 25.20%
2004 7.20%
2005 6.50%
2006 13.20%
2007 6.30%
2008 -9.50%
2009 50.20%
2010 5.50%
(they are annual numbers)
If I want the ten-year number I put in:
=(FVSCHEDULE(1,B3:B12-1))*100
and get 5.50
And if I want the five-year number (2006-2010) I put in:
=(FVSCHEDULE(1,B8:B12-1))*100
and get 5.50

What's wrong?