Dynamically updating ranges in Excel - dlmille please update my spreadsheet (2)
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
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
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%
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?
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 :)
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.
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!).
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
hedgeselectAuthor Commented:
Thanks, please use latest EE question to update anything from this q.
0
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
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
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,'Fu
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