+ Reply to Thread
Results 1 to 2 of 2

monthly return,average return,variance of returns,Standard deviation of returns

  1. #1
    Registered User
    Join Date
    07-18-2018
    Location
    Sydney,Australia
    MS-Off Ver
    2016
    Posts
    1

    monthly return,average return,variance of returns,Standard deviation of returns

    Hi,

    How do i calculate monthly return,average return,variance of returns,Standard deviation of returns?
    Below is an extract
    Thanks



    Date AORD CBA myer BHP TLSFLT WBC
    4/01/2010 4596.90 29.02 1.41 25.21 1.65 13.26 12.76
    1/02/2010 4651.10 30.40 1.47 26.30 1.57 12.59 13.97
    1/03/2010 4893.10 31.73 1.55 28.38 1.58 14.26 14.89
    1/04/2010 4833.90 32.98 1.50 26.53 1.68 13.50 14.55
    3/05/2010 4453.60 28.96 1.46 24.90 1.55 11.37 12.75
    1/06/2010 4324.80 27.42 1.46 24.52 1.72 11.31 11.80
    1/07/2010 4507.40 29.63 1.59 26.12 1.70 12.62 13.33
    2/08/2010 4438.80 29.77 1.70 24.13 1.56 13.35 12.06
    1/09/2010 4636.90 30.28 1.82 25.84 1.48 15.74 12.91
    1/10/2010 4733.40 28.94 1.86 27.84 1.51 15.97 12.61
    1/11/2010 4676.40 28.65 1.76 28.38 1.59 15.92 12.48
    1/12/2010 4846.90 30.05 1.72 30.05 1.58 17.34 12.93
    4/01/2011 4850.00 31.03 1.76 29.38 1.59 16.41 13.40
    1/02/2011 4923.60 32.55 1.58 30.62 1.69 15.46 13.70
    1/03/2011 4928.60 32.11 1.64 31.38 1.71 15.95 14.17
    1/04/2011 4899.00 32.92 1.61 30.89 1.77 16.83 14.46
    2/05/2011 4788.90 31.02 1.46 29.90 1.83 15.81 13.51
    1/06/2011 4659.80 32.05 1.35 29.52 1.76 15.48 13.59
    1/07/2011 4500.50 30.20 1.18 27.92 1.82 15.40 12.46
    1/08/2011 4369.90 31.29 1.09 26.79 1.97 14.21 12.57
    1/09/2011 4070.10 29.56 1.13 24.10 2.01 12.62 12.41
    3/10/2011 4360.50 31.97 1.45 26.01 2.01 14.75 13.62
    1/11/2011 4184.70 30.76 1.32 24.03 2.05 14.01 13.24
    1/12/2011 4111.00 31.94 1.08 23.68 2.16 11.96 12.88
    3/01/2012 4325.70 32.88 1.19 25.79 2.16 14.32 13.62
    1/02/2012 4388.10 33.40 1.25 25.38 2.28 16.58 13.46
    1/03/2012 4420.00 33.85 1.39 24.33 2.27 16.78 14.09
    2/04/2012 4467.20 35.12 1.40 24.99 2.44 16.11 14.64
    1/05/2012 4133.70 33.38 1.15 22.48 2.45 13.88 13.78
    1/06/2012 4135.50 35.88 0.96 22.11 2.55 14.44 14.35
    2/07/2012 4289.40 38.87 1.09 22.45 2.76 16.19 15.76
    1/08/2012 4339.00 38.91 1.19 22.35 2.80 18.23 16.82
    3/09/2012 4406.30 39.64 1.12 23.86 2.85 18.75 16.88
    1/10/2012 4535.40 41.05 1.26 24.74 3.01 21.15 17.32
    1/11/2012 4518.00 42.43 1.39 24.84 3.14 21.46 18.16
    3/12/2012 4664.60 44.20 1.39 26.79 3.18 21.46 18.55
    2/01/2013 4901.00 45.81 1.60 27.07 3.35 24.13 19.97
    1/02/2013 5120.40 49.56 1.79 26.77 3.49 25.84 21.92
    1/03/2013 4979.90 50.10 1.99 24.22 3.43 27.25 21.91
    2/04/2013 5168.60 54.11 2.17 24.17 3.79 30.95 24.08
    1/05/2013 4914.00 49.25 1.66 25.79 3.60 31.84 21.14
    3/06/2013 4775.40 50.96 1.61 23.19 3.63 31.89 21.47
    1/07/2013 5035.70 54.67 1.81 25.61 3.79 36.78 22.97
    1/08/2013 5125.30 55.85 1.86 26.43 3.88 38.11 23.34
    2/09/2013 5217.70 54.60 1.85 27.17 3.93 40.22 24.33
    1/10/2013 5420.30 58.33 1.77 28.63 4.10 43.23 25.49
    1/11/2013 5314.30 59.67 2.02 28.42 4.00 40.55 25.50
    2/12/2013 5353.10 59.65 1.95 28.88 4.15 39.60 25.11
    2/01/2014 5205.10 56.91 1.79 27.80 4.07 39.53 23.94
    3/02/2014 5415.40 59.30 1.85 29.18 4.16 43.18 25.95
    3/03/2014 5403.00 61.50 1.67 28.46 4.19 44.37 26.80
    1/04/2014 5470.80 62.66 1.66 29.46 4.30 45.44 27.23
    1/05/2014 5473.80 64.80 1.61 28.88 4.40 42.08 27.71
    2/06/2014 5382.00 64.24 1.59 28.01 4.29 37.57 27.28
    1/07/2014 5623.10 66.51 1.71 30.18 4.52 40.15 27.86
    1/08/2014 5624.60 67.17 1.81 28.61 4.76 39.64 28.21
    1/09/2014 5296.80 62.19 1.53 27.17 4.54 37.25 25.87
    1/10/2014 5505.00 66.47 1.52 27.25 4.82 36.53 28.00
    3/11/2014 5298.10 66.67 1.27 24.81 4.87 35.23 27.24
    1/12/2014 5388.60 70.75 1.10 23.56 5.11 28.42 27.75
    2/01/2015 5551.60 73.78 1.22 23.48 5.56 32.94 28.84
    2/02/2015 5898.50 78.31 1.45 27.00 5.63 35.95 31.80
    2/03/2015 5861.90 79.57 1.15 25.90 5.58 35.23 32.96
    1/04/2015 5773.70 75.71 1.17 26.68 5.51 38.59 30.51
    1/05/2015 5774.90 72.49 1.27 26.43 5.50 41.36 29.23
    1/06/2015 5451.20 72.52 1.04 24.16 5.43 30.31 28.00
    1/07/2015 5681.70 74.59 1.07 23.62 5.74 31.65 30.37
    3/08/2015 5222.10 66.92 1.02 22.49 5.30 32.88 27.09
    1/09/2015 5058.60 64.82 0.80 20.91 5.16 33.35 25.87
    1/10/2015 5288.60 68.39 0.87 21.66 4.96 35.06 27.52
    2/11/2015 5218.20 70.80 1.00 17.02 4.93 33.24 29.48
    1/12/2015 5344.60 76.23 1.08 16.81 5.16 36.85 30.77
    1/01/2016 5056.60 70.12 0.94 14.44 5.18 36.10 28.28
    1/02/2016 4947.90 64.99 1.04 14.65 4.83 38.24 26.36
    1/03/2016 5151.80 69.42 1.09 16.14 5.12 40.74 27.83
    1/04/2016 5316.00 68.46 0.96 19.80 5.14 37.05 28.47
    2/05/2016 5447.80 71.74 1.04 18.27 5.36 29.79 29.41
    1/06/2016 5310.40 68.91 1.04 17.85 5.34 29.76 28.16
    1/07/2016 5644.00 71.67 1.24 18.69 5.54 30.34 29.78
    1/08/2016 5529.40 69.43 1.26 19.56 5.26 34.73 28.22
    1/09/2016 5525.20 70.01 1.12 21.71 5.18 35.57 28.27
    3/10/2016 5402.40 70.96 1.13 22.38 4.98 33.12 29.19
    1/11/2016 5502.40 76.05 1.22 23.67 5.05 32.66 31.27
    1/12/2016 5719.10 79.68 1.32 24.31 5.10 30.60 32.60

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: monthly return,average return,variance of returns,Standard deviation of returns

    Hi ncha2721. Welcome to the forum.

    In the attached find:
    1. I designated "OTHER?" for the dept in the last column.
    2. A helper column in column I that returns the month number from each date.
    3. 4 sections where the monthly returns, average returns, variance and standard deviation of returns.
    4. In L2 filled down and across returns the monthly returns.
      Formula: copy to clipboard
      Please Login or Register  to view this content.
    5. In L16 down and across returns the average returns.
      Formula: copy to clipboard
      Please Login or Register  to view this content.
    6. Array entered in L30 returns the variances. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
      Formula: copy to clipboard
      Please Login or Register  to view this content.
    7. And array entered in L44 down and across.
      Formula: copy to clipboard
      Please Login or Register  to view this content.
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Standard Deviation of Returns Help
    By Excel15 in forum Excel General
    Replies: 1
    Last Post: 10-26-2016, 01:03 PM
  2. Calculating Returns and Standard Deviation from a data sample of share prices.
    By ofhani in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-15-2016, 11:51 AM
  3. Standard Deviation, Portfolio Returns Shortcuts
    By davidvanpatten in forum Excel General
    Replies: 2
    Last Post: 06-16-2015, 03:49 AM
  4. Teasing Monthly Returns from intra month returns
    By spcw in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-14-2013, 05:14 PM
  5. calculating annualised returns + Standard deviation of returns
    By Jaspal in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-01-2013, 09:22 AM
  6. Replies: 4
    Last Post: 01-22-2012, 09:31 PM
  7. Variance and Standard Deviation
    By shaunburke in forum Excel General
    Replies: 3
    Last Post: 04-15-2010, 06:16 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1