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!