returns calculation using sumproduct

[Stan Altshuller]

I have return streams going down colums B through I
Dates are down column A
I want colum J to give me the weighted return for the streams of returns.
But it must re-allocate to funds that have performance and not count funds
that do not. So the Dec-01 portfolio return should be 50% fund 2, and 50%
Fund 3, and 0% fund 1
the allocations are in row 45 and should be flexible to be adjusted as
needed.
THANKS!

Fund1 Fund2 Fund 3 Portfolio
Dec-01 -1.88% 0.36% -0.51%
Jan-02 1.51% 1.85% 1.12%
Feb-02 0.06% 0.16% 0.07%
Mar-02 -0.66% 0.63% -0.01%
Apr-02 1.00% 1.20% 0.73%
May-02 0.99% 1.40% 0.80%
Jun-02 1.27% 1.20% 0.82%
Jul-02 1.22% 0.06% 0.43%
Aug-02 1.17% 1.02% 0.73%
Sep-02 3.27% 1.70% 1.66%
Oct-02 4.51% 1.03% 1.85%
Nov-02 2.28% 1.50% 1.26%
Dec-02 1.38% 0.79% 0.72%
Jan-03 2.80% 1.58% 1.46%
Feb-03 1.31% 1.00% 0.77%
Mar-03 1.05% 0.18% 0.41%
Apr-03 3.20% 0.71% 1.30%
May-03 3.13% 0.58% 1.24%
Jun-03 2.13% -1.32% -2.54% -0.58%
Jul-03 3.33% -0.79% -1.94% 0.20%
Aug-03 1.79% -0.40% -0.73% 0.22%
Sep-03 2.98% 2.16% 3.12% 2.75%
Oct-03 1.35% 3.83% 1.38% 2.19%
Nov-03 2.27% 1.92% 1.12% 1.77%
Dec-03 1.05% 1.36% 1.04% 1.15%
Jan-04 1.32% 2.21% 0.88% 1.47%
Feb-04 0.20% 0.07% -0.18% 0.03%
Mar-04 1.19% 0.61% 0.77% 0.86%
Apr-04 1.15% 0.55% -0.10% 0.53%
May-04 -0.85% -0.97% -1.82% -1.21%
Jun-04 -0.76% 0.03% -0.74% -0.49%
Jul-04 0.92% 0.74% 1.25% 0.97%
Aug-04 0.65% 0.59% 1.32% 0.85%
Sep-04 2.87% 0.41% 0.20% 1.16%
Oct-04 1.00% 2.11% -0.42% 0.90%
Nov-04 1.65% 2.15% 0.85% 1.55%
Dec-04 1.79% 1.40% 0.91% 1.37%






Allocations 33.3% 33.3% 33.3% 100.00%

 

[Bernard Liengme]

This is an Excel site; you need to spell out business specific terms in
layman's terms

 

[Stan Altshuller]

OK, sorry I will try

I need to use sumproduct but when I use this formula:
=SUMPRODUCT(B2:I2,B$46:I$46)
it will equally weight funds that have missing performance. I.E
lets look at Dec 01.
Fund 1 was not in existance so it has a blank cell in B1
Fund 2 returned -1.88%
Fund 3 returned .36%

The portfolio (combination of funds) for Dec 01 should return: 50% * -1.88%
+ 50% * .36%

sorry if I am not being clear

 

[hrlngrv]

Stan Altshuller wrote...

I have return streams going down colums B through I
Dates are down column A
I want colum J to give me the weighted return for the streams of returns.
But it must re-allocate to funds that have performance and not count funds
that do not. So the Dec-01 portfolio return should be 50% fund 2, and 50%
Fund 3, and 0% fund 1
the allocations are in row 45 and should be flexible to be adjusted as
needed.

Fund1 Fund2 Fund 3 Portfolio
Dec-01 -1.88% 0.36% -0.51% ....
Nov-04 1.65% 2.15% 0.85% 1.55%
Dec-04 1.79% 1.40% 0.91% 1.37% ....
Allocations 33.3% 33.3% 33.3% 100.00%

Avoid using proportional typefaces in newsreaders.

Given the allocations of 33.3% for all funds, so 50/50 for Dec-01, how
do you get a portfolio return of -0.51% from fund returns of -1.88% and
0.36%? Simple average could be -0.76%. To get that, I could have used
the formula

=SUMPRODUCT(B22,B$45$45)/SUMIF(B22,"<>",B$45$45)

 

[Stan Altshuller]

When I use that formula it gives me also -.51%
unfortunately is still counts fund 1 as 0 (33%)

 

[hrlngrv]

Stan Altshuller wrote...

When I use that formula it gives me also -.51%
unfortunately is still counts fund 1 as 0 (33%)

With B2 *blank*, C2 containing -1.88%, D2 containing 0.36% and each
cell in B4545 containing =1/3, the formula above returns -0.76% on my
system. However, if B2 contains a zero-length string like ="", then the
formula above evaluates to -0.51%. So I guess you're using strings of
zero or more spaces to represent no allocation in particular funds
rather than blanking those cells. Change the formula to
=SUMPRODUCT(B22,B$45$45)/SUMPRODUCT(--ISNUMBER(B22),B$45$45)

 

[Stan Altshuller]

wow it works. exactly what i need. you are an excel genious. thank you!