Thread: returns calculation using sumproduct

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%

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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Stan Altshuller" <ctaca@hotmail.com> wrote in message
news:eeq0wxP%23EHA.3988@TK2MSFTNGP11.phx.gbl...
>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%
>
>

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

"Bernard Liengme" <bliengme@stfx.TRUENORTH.ca> wrote in message
news:OWFqD7P%23EHA.2568@TK2MSFTNGP10.phx.gbl...
> This is an Excel site; you need to spell out business specific terms in
> layman's terms
>
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Stan Altshuller" <ctaca@hotmail.com> wrote in message
> news:eeq0wxP%23EHA.3988@TK2MSFTNGP11.phx.gbl...
> >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%
> >
> >
>
>

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(B2:D2,B$45:D$45)/SUMIF(B2:D2,"<>",B$45:D$45)

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

<hrlngrv@aol.com> wrote in message
news:1105573775.263055.216160@z14g2000cwz.googlegroups.com...
> 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(B2:D2,B$45:D$45)/SUMIF(B2:D2,"<>",B$45:D$45)
>

Stan Altshuller wrote...
>When I use that formula it gives me also -.51%
>unfortunately is still counts fund 1 as 0 (33%)
>
><hrlngrv@aol.com> wrote in message
....
>>=SUMPRODUCT(B2:D2,B$45:D$45)/SUMIF(B2:D2,"<>",B$45:D$45)

With B2 *blank*, C2 containing -1.88%, D2 containing 0.36% and each
cell in B45:D45 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(B2:D2,B$45:D$45)/SUMPRODUCT(--ISNUMBER(B2:D2),B$45:D$45)

wow it works. exactly what i need. you are an excel genious. thank you!
<hrlngrv@aol.com> wrote in message
news:1105575477.475178.159130@c13g2000cwb.googlegroups.com...
> Stan Altshuller wrote...
> >When I use that formula it gives me also -.51%
> >unfortunately is still counts fund 1 as 0 (33%)
> >
> ><hrlngrv@aol.com> wrote in message
> ...
> >>=SUMPRODUCT(B2:D2,B$45:D$45)/SUMIF(B2:D2,"<>",B$45:D$45)
>
> With B2 *blank*, C2 containing -1.88%, D2 containing 0.36% and each
> cell in B45:D45 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(B2:D2,B$45:D$45)/SUMPRODUCT(--ISNUMBER(B2:D2),B$45:D$45)
>