AnsweredAssumed Answered

Average of annual averages (Moving Average)

Question asked by MauriceG on Sep 20, 2014
Latest reply on Jun 2, 2015 by CalvinCassens

Title

Average of annual averages (Moving Average)

Post

Hello All,

I have built a database to manage some investments. Included in the database is a summary layout that looks like this:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
               

 

               
               

Change from previous year

               
               

Total at Dec. 31

               
               

Annual Return

               
               

3-year average return

               
               

5-year average return

               
               

10-year average return

               
               

2004

               
               

3

               
               

33

               
               

10%

               
               

 

               
               

 

               
               

 

               
               

2005

               
               

5

               
               

38

               
               

15%

               
               

 

               
               

 

               
               

 

               
               

2006

               
               

2

               
               

40

               
               

5.2%

               
               

 

               
               

 

               
               

 

               
               

2007

               
               

4

               
               

44

               
               

10%

               
               

 

               
               

 

               
               

 

               
               

2008

               
               

6

               
               

50

               
               

13.6%

               
               

 

               
               

 

               
               

 

               
               

2009

               
               

5

               
               

55

               
               

10%

               
               

 

               
               

 

               
               

 

               
               

2010

               
               

5

               
               

60

               
               

8.3%

               
               

 

               
               

 

               
               

 

               
               

2011

               
               

6

               
               

66

               
               

10%

               
               

 

               
               

 

               
               

 

               
               

2012

               
               

4

               
               

70

               
               

6%

               
               

 

               
               

 

               
               

 

               
               

2013

               
               

7

               
               

77

               
               

10%

               
               

 

               
               

 

               
               

 

               
               

2014

               
               

3

               
               

80

               
               

3.75%

               
               

 

               
               

 

               
               

 

               

 

The Annual Return field is a calculation field in which the info comes from each year's record. I would like to create a 3, 5 and 10 year average return. For instance, the 3-year average return in 2014 would be the average of the annual returns for 2014, 2013 and 2012. In 2015, it would be the average of the annual returns for 2015, 2014 and 2013, etc. Same thing for the 5-year and 10-year average returns.

Does anyone know how to do this? Do I need new tables? Would the fields for the 3-year, 5-year and 10-year average returns be calculation fields or statistics fields? New script?

Thanks.

Yvan

Outcomes