Dollar Weighted Average Sumproduct Formula:

**pipsturbo** · 12-01-2009, 09:55 AM

Good morning!

I've been trying to figure out a dollar weighted average formula in excel.

See attached file...

In sheet 1, I need to pull data from sheet 2 using a sumproduct formula to find the dollar weighted average maturity of a bond portfolio. Basically, I need to know how many days the portfolio has left to mature according to the portfolio's weighting by the amount in column H.

If anyone can point me in the right direction, I'd be much appreciative.

Thanks,
Mike

**NBVC** · 12-01-2009, 10:11 AM

Should the formula be:

=SUMPRODUCT(1*(Sheet2!B2:B3000=D4),1*(Sheet2!V2:V3000=B2))/COUNTIF(Sheet2!H2:H3000,">0")

**pipsturbo** · 12-01-2009, 10:28 AM

Hi NBVC,

Thanks for the post.

I'm using:

=SUMPRODUCT(1*('Raw_Data t'!$B$2:$B$3000=$D4),1*('Raw_Data t'!$V$2:$V$3000=$B$2))/COUNTIF('Raw_Data t'!H2:H3000,">0")

It still calculates as 0 still.

What am I doing wrong?

Thanks,
Mike

**NBVC** · 12-01-2009, 10:30 AM

I noticed the date in B2 is not in your list in v2:V3000.... Should it be? Or are you looking for dates less than or equal to?

**pipsturbo** · 12-01-2009, 11:41 AM

Hi,

I updated the formula to make it more simple:

=IF('Raw_Data t'!$B$2:$B$3000=D4,SUMPRODUCT('Raw_Data t'!$W$2:$W$3000)*('Raw_Data t'!$X$2:$X$3000),0)

I built 2 columns for the portfolio weightings. This is basically that each position value is divided by the complete value of the portfolio.

It still returns 0 for some reason though.

**NBVC** · 12-01-2009, 11:49 AM

Kind of confused without seeing what you did...

Maybe:

Sumproduct(--('Raw_Data t'!$B$2:$B$3000=D4),'Raw_Data t'!$W$2:$W$3000,'Raw_Data t'!$X$2:$X$3000))