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$ Commented May 3, 2020 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$ Commented May 3, 2020 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
    Commented May 4, 2020 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$ Commented May 4, 2020 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
    Commented May 4, 2020 at 21:19

1 Answer 1

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 and acknowledge you have read our privacy policy.

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