Solved

Dynamically updating ranges in Excel - dlmille please update my spreadsheet (2)

Posted on 2011-03-08
12
290 Views
Last Modified: 2012-05-11
Hi Dave (dlmille),

Further to my last question, could you please update the latest previous spreadsheet you created with the following:

1.  New column K in Fund Data worksheet, called Annualised Volatility which is simply the annualised standard deviation of monthly returns which is recorded on a monthly basis for all inclusive monthly data.  For example, in your last spreadsheet this would fill every cell between K152 and K227 (but will work on the whole column when data is added).  See the formula in cell C24 if necessary in worksheet 3.

2. New column L titled "12-month Annualised Volatility" which does the same as (1) above, except that the standard deviation of monthly returns is based on that year's returns only and is calculated and displayed in the December row

3. For the summary table, can we:

a) insert a new row 24 criteria titled "12-month Annualised Volatility for [XXXX]" in which take the latest annual annualised volatility figure the latest year and [XXXX] is dynamically replaced with the last year.  Where it is a new fund or no previous year exists, then a "-" can be added

b) have the last 3-years annual returns dynamically update in the table under the respective cell (where there are 3-years of returns) otherwise "n/a" to be inserted in the cell and also the table automatically updates so that if there was e.g. 8 years of data, only the last 3 years would show

***********

Given that this is a multiple question set, if you are able to post one spreadsheet reflecting all the above, and when verified, I will open 3 new EE questions to reflect questions 2, 3a and 3b as individual solutions.


In answer to your previous questions:

1. Yes, sounds about right, but difficult to verify without the updated spreadsheet
2. Sounds right
3. Again sounds right, but easier for me to verify once I can see the spreadsheet

Thanks again.


0
Comment
Question by:hedgeselect
  • 7
  • 5
12 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 35085053
Its ok to give me a heads up that a new question is coming, but you might want to make your questions a bit less directed as other E-E experts may want to contribute.  And by clicking ASK RELATED QUESTION you give everyone that heads up - Just Click ASK A RELATED QUESTION at the bottom of a current question and I and any E-E experts that are following will be directed there.

On to the question at hand...

1.  I assume the standard deviation includes all entries that have data - e.g., from when you started tracking fund data to that point in time, correct?  I created the range fundStart to know what row the actual tracking begins on and STDEV(fundStart:currentRow) is the way to go.

2.  12 month annualized volatility is the same, except for partial year at the beginning - and I use fundStart to get that.  If current beginning of year > fundstart, just use last 12 months, otherwise fundstart:current row as with above.

3.  I created a new range called lastFundDEC for the last valid datapoint and lastFundDateDec for the corresponding date (you're really testing my offset function skills - this is great - I need to use this more!) which captures the last valid entry of December.


for example - lastFundDec = =OFFSET('Fund Data'!$L$1,MATCH(99^99,'Fund Data'!$L:$L)-1,0)

and lastFundDateDec = same formula, but looking at the Column B range.  Its important to use absolute addressing for these types of formulas or things go haywire.  E.g., $L:$L or $L$1 as opposed to L:L or L1.

Finally, I created a named range called lastMonthlyYTDReturn -to pick that up for partial years.  I check the year label and if its not a number, I'm assuming you're wanting the monthly YTD number for the last return


Please see attached and advise,

Dave
Master-Statistics-template-1-v5.xls
0
 

Author Comment

by:hedgeselect
ID: 35085674
Sure, will do going forward.  

I just wiped all the monthly fund data and replaced it with the following monthly returns which I placed in different time periods in column C, and I get the #REF! error msg.  
1.03%
0.89%
0.22%
0.96%
1.36%
2.11%
-0.09%
1.58%
0.33%
-0.20%
0.91%
0.09%

Does that happen to you?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35085689
How did you wipe the data?  Did you delete rows, or just erase the data with the delete key or CLEAR?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35085693
Where did you place (what rows) the data?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35085792
I just cleared the monthly data and pasted it to the end for the latest dates...

see attached...

Master-Statistics-template-1-v5a.xls
0
 

Author Comment

by:hedgeselect
ID: 35086650
I just erased the data with the delete key.  Ok I think I have found the issue.  It seems to happen when I copy and paste numbers into the cell, but if I paste it as VALUES, its ok.

Use the same new numbers I pasted above, the yearly return column E does not seem to compute?  Could you check the yearly return when the data is pasted into 31 Jan 2011.  Its showing the yearly return as 0%.

Also just a thought, but where there is only fresh 12 months of data, shouldn't the monthly YTD returns match the fund cumulative performance of $1 ivnested at inception?  It shows 1.084 i.e. 8.4% performance for the year v 9.56% for the monthly YTD?  Same with annualised return, shouldn't the Dec figure match the monthly YTD return as the final figure for the year should close everything off and make all yearly figures more conclusive if you know what I mean?

Thx.



0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 41

Expert Comment

by:dlmille
ID: 35086737
Ill check later this PM and do some testing so we can make this more bulletproof


Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35089487
Ahhh yes - if there are prior returns, I use the previous december as a calibration point to set back to $1 by dividing by that column J value.  But, if there are not, I don't need to do that.  Corrected...  I updated Quarterly returns as well along the same lines...

As far as the column F I think we have a begining of period versus end of period issue with the disconnect.  At some point I started thinking end of period.

So the January $1 value is not $1, its $1 at the cumulative return for January (since we're testing on Jan 31st).  I'm not sure I ever played with Column F, but reverting back to the original original question, January's $1 value is end of month, not beginning of month figure, so I changed that :)

see attached.

Dave
Master-Statistics-template-1-v5b.xls
0
 

Author Comment

by:hedgeselect
ID: 35091817
Hi

I think we're slowly getting there.  Yes all these values at end of month values, not beginning of month.  I should made that clear at the beginning.  

The first 12-months of rolling returns (column I) need to show the rolling % as the single digit i.e. 1.03% rather than 101.03%.

The formulas have begun to get too complicated for me to follow so I will need to check sample data that I can compare against results from other fund performance templates or magazines etc.  This might take a few days to dig out.  

I need to remind myself and check whether annualised returns (col F) should be the same as yearly returns (col E) when end of December comes.  Could you just confirm whether the annualised returns formula you are using is based on the calculation of "all the monthly returns for that year, (including if there is only a partially completed year), then averaged and then multiplied by 12" to get annualised or is it based on something else?  Because if you had all 12-months figures, divided by the number of months of data for that year i.e. 12, and then multiplied it by 12 again this should match the yearly return?

I think col C in the fund summary table (worksheet 3) needs to be checked C21, C22, C25 as col D&E on the fund summary table contains a % return when there is no monthly data for that period yet.  In fact, can we show it so that no rows show data until the appropiate condition i.e. monthly data is present?

I've just added some more  monthly returns for an example.   Thanks again.  

 Master-Statistics-template-1-v5b.xls
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 35097783
I corrected column I

I've not looked at Annualised return - not my formula.  

Take $1 invested in January with 10% return Jan thru Dec.  Your calculation for Annualized return shows 120% in each month.  However, it does not account for compounding.

With compounding, if I have $1 invested with these returns, by December, I'll have $3.14 by the end of the year - so the return for the year would be 213%.

Annualized return should take the compounded return on a YTD basis, then divide by # months to that point and multiply by 12. See revised formula in column M orange shaded.

If you want calcs in the table to work correctly, you need to ensure your data entry is up to date.  I hit delete key on future data and the numbers in the summary table come back in line (re: dynamic ranges based on last entries!).

Cheers,

Dave


Master-Statistics-template-1-v6.xls
0
 

Author Comment

by:hedgeselect
ID: 35098110
Hi Dave,

I've posted the question again (to repair the file) on http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26878220.html

I'll close this one as you've done a fair bit.  Please feel free to help correct the various errors including Annualised Return.  I just realised the other person who did this on EE has cocked up a few of the columns and I didnt check his work.

0
 

Author Closing Comment

by:hedgeselect
ID: 35098677
Thanks, please use latest EE question to update anything from this q.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
macro for active word document 4 38
Macro 6 45
Excel for Mac - How make those Tabs larger? 2 30
Copy value from a certain cell 5 24
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now