# Utilizing Dynamic Arrays for Compound Annual Growth Rate

#### mookyon

##### New Member
Dear Members,

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
Cell Formulas
RangeFormula
A2:A11A2=TRANSPOSE(B1:K1)
B2:K11B2=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.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi

Cell B2 Drag the formula to the right

Excel Formula:
``````=LET(
rel_r,SEQUENCE(ROWS(A2#),
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)``````

Last edited:
Same formula on B2:K11

Hi

Maybe you can copy columns with vba!
I don't think you can put it in MAP PER.COL Hi

Vba
VBA Code:
``````Option Explicit

Sub FormulaVba()
[A2].Formula2 = "=TRANSPOSE(B1:INDEX(1:1,,COUNTA(1:1)))"
[B2:K2].Formula2 = "=LET(rel_r,SEQUENCE(ROWS(A2#)),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)"
End Sub``````

Hi

Vba
VBA Code:
``````Option Explicit

Sub FormulaVba()
[A2].Formula2 = "=TRANSPOSE(B1:INDEX(1:1,,COUNTA(1:1)))"
[B2:K2].Formula2 = "=LET(rel_r,SEQUENCE(ROWS(A2#)),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)"
End Sub``````
The entire 10x10 block (B2:K11) is already populated with the same formula. I was not looking for a method to populate it.
ISY: Thank you anyhow.

What I am actually looking for is a Dynamic Array solution for creating this square/triangle of values, either by MAP, SCAN, or RECURSIVE LAMBDA.
Rgds,

Hi

Formula =INDEX(1:20,SEQUENCE(ROWS(A2#)+1),SEQUENCE(,COUNTA(1:1)))

I don't think there is a solution with the new Excel 365 Beta formulas

=LET(C,A7:A11,R,CONCATENATE(B7:F11),A,FILTER(R,C,""),B,FILTER(R,C=0,""),K,INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",0,A,IFERROR(PERCOL(--A,LAMBDA(Y,SUM(Y))),"????????"),B,IFERROR(PERCOL(--B,LAMBDA(Y,SUM(Y))),"????????"),IFERROR(PERCOL(--R,LAMBDA(Y,SUM(Y))),"?????"))&"</s></t>","//s"),SEQUENCE(ROWS(C)+3,5)),K) Members,

I am very thankful to Mourad Louha (Excel MVP) who contributed this fabulous code and provided a solution.

You may try it by typing something like

=CAGR({0.16,-0.43,0.73,-0.6,0.1})

In Name Manager CAGR is defined as:

=LAMBDA(Input,LET(fnCalculator,LAMBDA(Previous,Current,Periods,IFERROR((Current*Previous^Periods)^(1/(1+Periods))-1,0)),fnIteratorItem,LAMBDA(Data,Matrix,Item,LET(N,ROWS(Matrix),MAP(Matrix,SEQUENCE(N,N),LAMBDA(A,B,LET(X,ROUNDUP(B/N,0),Y,IF(MOD(B,N)>0,MOD(B,N),N),IF((X<Y)*(B=Item),fnCalculator(1+INDEX(Matrix,X,Y-1),1+INDEX(Data,1,Y),Y-X),A)))))),fnIterator,LAMBDA(This,Data,Matrix,Iteration,IF(Iteration>ROWS(Matrix)*COLUMNS(Matrix),Matrix,This(This,Data,fnIteratorItem(Data,Matrix,Iteration),Iteration+1))),fnIterator(fnIterator,INDEX(Input,1,0),MAKEARRAY(COLUMNS(INDEX(Input,1,0)),COLUMNS(INDEX(Input,1,0)),LAMBDA(X,Y,IF(X=Y,INDEX(INDEX(Input,1,0),1,Y),IF(X>Y,"",0)))),1)))

If anybody out there can shed some light and decipher this, I would be grateful.

THX again to Mourad Louha (Excel MVP) for his effort on this matter.

Replies
4
Views
188
Replies
3
Views
299
Replies
1
Views
481
Replies
4
Views
2K
Replies
14
Views
391

### Forum statistics

1,206,713
Messages
6,074,482
Members
446,071
Latest member
gaborfreeman ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back