I need assistance in creating a CAGR array from periodic returns vector.

I wish to create a Dynamic Array holding only calculated CAGR (compound annual growth rate) values derived from periodical return on investment vector.

This is easily performed in standard Excel but requires tons of space (for each instrument: a 20-period vector will require 400 cells). Hence a dynamic array might be preferable.

A side note: The basic calculation is done by the following math. equation:

[(Final Value) / (Initial Value)] ^ (1/n) – 1.

Is there any way to perform such a vector to array conversion using MAP-LAMBDA, SCAN-LAMBDA, or AGGREGATE function?

THX!

P.S.,

A sample mini sheet FYI

CAGR Question.xlsx | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | G | H | I | J | K | |||

1 | CAGR (Single Step) | -85% | -41% | 18% | 74% | -86% | -35% | 71% | 21% | 66% | 8% | ||

2 | -85% | -85% | -70% | -53% | -35% | -52% | -50% | -40% | -35% | -28% | -25% | ||

3 | -41% | -41% | -16% | 7% | -36% | -36% | -24% | -19% | -12% | -10% | |||

4 | 18% | 18% | 44% | -34% | -35% | -21% | -15% | -6% | -5% | ||||

5 | 74% | 74% | -51% | -46% | -28% | -20% | -10% | -8% | |||||

6 | -86% | -86% | -70% | -47% | -35% | -21% | -17% | ||||||

7 | -35% | -35% | 5% | 10% | 22% | 19% | |||||||

8 | 71% | 71% | 43% | 51% | 38% | ||||||||

9 | 21% | 21% | 41% | 29% | |||||||||

10 | 66% | 66% | 34% | ||||||||||

11 | 8% | 8% | |||||||||||

CAGR in Single Step |

Cell Formulas | ||
---|---|---|

Range | Formula | |

A2:A11 | A2 | =TRANSPOSE(B1:K1) |

B2:K11 | B2 | =LET( rel_r,ROW()-1, rel_c,COLUMN()-1, periods,rel_c-rel_r, previous,POWER(1+A2,periods), current,1+B$1, root,1/(1+periods), let,CHOOSE(2+SIGN(periods),0,B$1,POWER(previous*current,root)-1), let ) |

Dynamic array formulas. |