2
$\begingroup$

I have a series of rolling annual returns and would like to convert these back to quarterly returns, which have not been provided. Is this possible formulaically, or is something like Excel's solver required?

Sample data below, quarterly returns are provided (still need to calculate column four):

+---------+-----------------------+------------------------+--------------------------------+
| quarter | quarterly returns (%) | rolling 1y returns (%) | back out quarterly returns (%) |
+---------+-----------------------+------------------------+--------------------------------+
| 1       | 5                     |                        |                                |
+---------+-----------------------+------------------------+--------------------------------+
| 2       | 4                     |                        |                                |
+---------+-----------------------+------------------------+--------------------------------+
| 3       | 3                     |                        |                                |
+---------+-----------------------+------------------------+--------------------------------+
| 4       | 2                     | 14.7                   |                                |
+---------+-----------------------+------------------------+--------------------------------+
| 5       | 1                     | 10.36                  |                                |
+---------+-----------------------+------------------------+--------------------------------+
| 6       | 2                     | 8.23                   |                                |
+---------+-----------------------+------------------------+--------------------------------+
| 7       | 3                     | 8.23                   | ?                              |
+---------+-----------------------+------------------------+--------------------------------+
| 8       | 4                     | 10.36                  | ?                              |
+---------+-----------------------+------------------------+--------------------------------+
| 9       | 5                     | 14.73                  | ?                              |
+---------+-----------------------+------------------------+--------------------------------+
| 10      | 6                     | 19.22                  | ?                              |
+---------+-----------------------+------------------------+--------------------------------+

Thanks!

$\endgroup$
5
  • $\begingroup$ I had a comment here, but its content was wrong. $\endgroup$ – Kermittfrog May 3 '20 at 17:46
  • $\begingroup$ Could you expand a bit more on how you have derived rolling 1Y return from quarterly returns? Simple sum? Rolling 4-period compound return? The question would be more clear if both columns were carried out to the same decimal) $\endgroup$ – Thomas Boyd May 3 '20 at 18:58
  • $\begingroup$ Your sample data doesn't make sense. You ask about backing out quarterly returns but you include quarterly returns (making backing them out unnecessary). Practically, if all you have is rolling annual returns at quarterly intervals, you can get quarterly return by taking a ratio of successive rolling annual values. $\endgroup$ – Chris May 4 '20 at 6:17
  • $\begingroup$ In my understanding, column 2 is there for our understanding, only. Also, we use geometric returns, i.e. R4=(1+r1)(1+r2)(1+r3)(1+r4)-1 $\endgroup$ – Kermittfrog May 4 '20 at 6:37
  • $\begingroup$ Hi, sorry for the misunderstanding. Kermittfrog is correct. Column Two was only there for clarity, and I used geometric returns. I've accepted his answer. Thanks all! $\endgroup$ – sb1 May 4 '20 at 21:19
2
$\begingroup$

Generally speaking, you pose a system of linear equations that is undetermined. If you provided the initial 3 quarterly returns together with the cumulative returns then yes, all other quarterly returns can be backed out. Else, you could try to approximate the other returns using the pseudo-inverse. This answer here has a very nice example on that topic. https://stats.stackexchange.com/questions/67907/extract-data-points-from-moving-average

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.