Utilizing Dynamic Arrays for Compound Annual Growth Rate

[mookyon]

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

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.

 

[ISY]

Hi

Cell B2 Drag the formula to the right

=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)

 

[mookyon]

Same formula on B2:K11

 

[ISY]

Hi

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

 

[ISY]

Hi

Vba

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

 

[mookyon]

Hi

Vba

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,

 

[ISY]

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)

 

[mookyon]

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.