I am trying to calculate returns in excel for our fund from external system generated data as per the above. I want to show QTR / YTD / 1 / 3 / 5 / SI.
It’s been a while since I have had to do this, so was hoping that someone who does this regularly can steer me in the right direction.
Many thanks
VI
Just noticed that the data format went wonky, but think it is fairly clear what is required.
Is that a sample of the data you’re using, or is that the entire data. If the latter, there’s no way to do the 3 and 5 year numbers.
This calculation is so basic that I find it hard to believe that “it’s been a while since you’ve done it,” though TTM does bring up a good point about whether there are cash flows to incorporate and whether you are looking to compute price returns or total returns. Cash flows (and deposits/withdrawals) do make the calculation more complex, especially if the numbers in the series reflect deposits/withdrawals.
If no deposits or withdrawals are present, then each month’s return is [(Final Px + Income)/(Init Px) - 1], then you chain link them together for whatever time periods you want (final return) = [(1+r1)(1+r2)…(1+rN)] - 1
It is a sample and I accept it is sooo very basic. Sorry I am just a pleb that doesn’t have to do these calculations in my day job. I haven’t had to do this at all actually other than in the exam a loooong time ago. I just want to get the numbers right and move on in life.
So then, what figures do you get for YTD, 1 Yr and SI…?
Much appreciated.
PS I thought it was chain linked - at least I didn’t try to add them up…
YTD is Dec 31 to the most recent data point. 1y might mean take the the last month’s close value and compute from that date one year ago. However, if you are mid month and have an interim value, you may need to check with your boss as to what he/she wants. i.e. 1 year of monthly returns + Month-to-date, or maybe 11 months + Month-to-date. If you have daily values, you can also do 1-year trailing daily values. But it doesn’t look like you have that.
Check with your boss on how they want to handle partial months. There is no industry-wide standard as far as I know (but I might be wrong on that).
SI = Since Inception, so you take the first month or data point that you have. The way the chain-linking works, it doesn’t end up mattering if that’s a full month or not, for this figure. If you don’t have any income or cash flows, you can just do (final Px)/(init Px) - 1, and skip the whole chain-linking thing. If you have cash flows in the middle (very likely), then you have to do the chain linking.
You can save a step by skipping subtracting 1 for interim month. So a monthly “return factor” (my term) would be (Final Px + cash flows or income) / (Start Px) = RF. Return factors are just RF = (1+ Return), but often save you time when you are chain linking and don’t really need to print out interim months.
Then multiply all the return factors together: (Cumulative Return) = (RF1 * RF2 * RF3 * etc) - 1. Or if you want to be really fancy, you can use up logarithms, which have the advantage that adding up logartithms of return factors is equivalent to multiplying the return factors. Just at the end, when you sum up the logarithms, you need to undo the log transformation by EXP(), so Return = EXP( SUM( LN( RF1) + LN(RF2) +… ) ) - 1
The log transformation is probably overkill for you now, but there are times when it is a more useful way to approach these things (generally in simulations, where you start adding logs to represent % deviations from observed data).
Think you went into overdrive there… these are monthly figures and I just want to get a number to confirm my work. Lets just narrow it down to YTD and 12 months then.
If you are not worrying about partial months (i.e. ignoring Nov 1-Nov 20th), why is it so hard to understand what Year-To-Date means? Start at last year’s close (i.e. Dec 31) and go to the most recent figure you have.
And 1 year… Hmmm, I wonder what percent higher the last close was from one year before then???
My mistake. I thought those were price levels, and - looking closer - your column is returns. My advice above was correct if those had been end-of-month prices rather than end-of-month returns.
So yes, you add 1 to each return figure (divided by 100 to turn percents to decimal equivalents) and multiply them together, then subtract 1 from the product. Multiply by 100 to get back to percent figures.
For YTD, choose Jan->present month.
For 1y, take most recent month plus the preceding 11 (11+current month=12=1y). For SI, do the whole series.
I’m not going to do the calculation for you until you at least attempt to do it for yourself. As it was, until your last post, you just gave some numbers and said “tell me the answer,” which isn’t a popular way to ask questions around here.
And yes, the control shift enter combo is special in excel: it alerts excel to treat the data as a connected whole (a vector or matrix). I don’t like that method much, because I find it a pain if you need to resize the dimensions of your data or something like that, but sometimes it is useful, as it is here. (Actually, I think newer versions of Excel are smarter about this, so maybe I should start looking at it again)
Now that you’ve tried, I’ll tell you what I got:
The last 1y returns are reflect the topmost 12 months of data that you posted. Converting these to Return factors (i.e. 1 + percent figure / 100 ), I get the following series for the last 12 months (most recent to least recent)
1.0095 - Oct '14 0.9911 1.0229 0.9975 1.0063 1.0368 0.9756 0.963 1.0509 0.9917 - Jan '14 1.0131 0.9969 - Nov '13 Using PRODUCT to multiply these most recent 12 return factors, I get 1.053261086 Which if you subtract 1 and multiply by 100 gives you 5.33% or roughly within rounding errror of your answer. The most recent month was October (month 10), so YTD figures use only the most recent 10 months of data PRODUCT = 1.04287469 --> 4.29%, also within rounding error of your answer using my method. 3y would involve the last 36 months of data, 5y would use 60.
Note that this method assumes that the return figures include any interest and/or dividend payments and that these payments are reinvested. If the figures don’t, then the final answer still reflects the price return (how much higher the final price of something is than the initial price) but not the total return (which would included cash flows and generally assumes that they are reinvested).
