{Non-array} formula way to calculate Total Returns

[Silverjman]

Particularly "since inception" Total Returns where (1+ x.x%) each individual quarters return, for compounding, would be too onerous.
I've seen User Defined functions on other message boards but they don't give me the same returns and don't seem very "robust", or I botched them despite the amount of time I threw at them.


Quarterly Returns Q62:X62
<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;"> Q ---------R------ S----T------ U--- ---V -----W ---X

</code>0.0032 -0.00670.01850.0009 -0.00550.02000.05060.0742

------0.32% -0.67% 1.85% 0.09% -0.55% 2.00% 5.06% 7.42%------

Total Return = 7.8397% whether it's calculated with

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">{=PRODUCT(1+$Q62:X62)^(4/COUNT($Q62:X62))-1}
</code>
or

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">=(SUMPRODUCT(1+Q62,1+R62,1+S62,1+T62,1+U62,1+V62,1+W62,1+X62)^(4/8))-1
</code>
Is there a simple way I can adjust the SUMPRODUCT calculation or does this require a User Defined function? Over 40 quarters the above SUMPRODUCT method becomes untenable. THANKS!!!

 

[Aladin Akyurek]

Although not happy with this...

Just enter:

=INDEX(PRODUCT(1+$Q62:X62)^(4/COUNT($Q62:X62))-1,1,1)

 

[Silverjman]

Although not happy with this...

Just enter:

=INDEX(PRODUCT(1+$Q62:X62)^(4/COUNT($Q62:X62))-1,1,1)

Wow THANKS, this really helps me out, I knew it had to be possible but my pea brain what racked and still empty. Also as a Excel is Fun fan and first time poster it's exciting to be helped by THE Aladin!!!!!!!

 

[Aladin Akyurek]

Wow THANKS, this really helps me out, I knew it had to be possible but my pea brain what racked and still empty. Also as a Excel is Fun fan and first time poster it's exciting to be helped by THE Aladin!!!!!!!

You are welcome. Thanks for the kind feedback.

 

[Silverjman]

Can you convert this one as well? {=(PRODUCT(IF(MONTH(L$4:AV$4)=MONTH(AV$4), 1+L66:AV66)))^(1/10)-1} I tried to just array out the MONTHS and/or the 1+RANGE to no avail...

 

[Silverjman]

Could you convert this one as well? {=(PRODUCT(IF(MONTH(L$4:T$4)=MONTH(T$4), 1+L56:T56)))^(1/3)-1} I tried to just INDEXarray out the MONTHS and/or the 1+RANGE to no avail. [TABLE="width: 69"]
<tbody>[TR]
[TD]
[TABLE="width: 621"]
<tbody>[TR]
[TD]9.9%
[/TD]
[TD]9.9%
[/TD]
[TD]8.4%
[/TD]
[TD]7.3%
[/TD]
[TD]6.8%
[/TD]
[TD]5.3%
[/TD]
[TD]4.0%
[/TD]
[TD]5.0%
[/TD]
[TD]4.5%
[/TD]
[/TR]
</tbody>[/TABLE]
= 7.0127%[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I would be using this for 10yr return not 3yr as obviously putting the bolds into SUMPRODUCT or normal operators would work fine for that, But a 3yr example is more succinct.

 

[Aladin Akyurek]

Can you convert this one as well? {=(PRODUCT(IF(MONTH(L$4:AV$4)=MONTH(AV$4), 1+L66:AV66)))^(1/10)-1} I tried to just array out the MONTHS and/or the 1+RANGE to no avail...

Could you convert this one as well? {=(PRODUCT(IF(MONTH(L$4:T$4)=MONTH(T$4), 1+L56:T56)))^(1/3)-1} I tried to just INDEXarray out the MONTHS and/or the 1+RANGE to no avail. [TABLE="width: 69"]
<tbody>[TR]
[TD] [TABLE="width: 621"]
<tbody>[TR]
[TD]9.9%[/TD]
[TD]9.9%[/TD]
[TD]8.4%[/TD]
[TD]7.3%[/TD]
[TD]6.8%[/TD]
[TD]5.3%[/TD]
[TD]4.0%[/TD]
[TD]5.0%[/TD]
[TD]4.5%[/TD]
[/TR]
</tbody>[/TABLE]
= 7.0127%[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I would be using this for 10yr return not 3yr as obviously putting the bolds into SUMPRODUCT or normal operators would work fine for that, But a 3yr example is more succinct.

Not sure, but does this satisfy?

=PRODUCT(INDEX((MONTH(L$4:AV$4)=MONTH(AV$4))*(1+L66:AV66),1,0))^(1/10)-1

 

[pgc01]

Another option:

=PRODUCT(INDEX(1+((MONTH(L$4:AV$4)=MONTH(AV$4))*L66:AV66),))^(1/10)-1