Creating A Maturity Spread

[wblewis935]

Hello!

I am trying to create a maturity spread for bonds. I have included the maturity dates for all of the bonds listed. The categories I need to break the balances down in are as follows:

Under 3 Months
3 months to 1 year
1 Year to 3 Years
3 Years to 5 Years
5 Years to 15 Years
Over 15 Years

I have tried multiple formulas, but I am just not finding the right one. Any help would be greatly appreciated!
Thank you!

 

[Scott R]

Date math can be tricky w/bonds so I'll keep this generic in terms of months.

A1 has 0, B1 has 3, A2 has =B1+0.001, B2 has 12, etc. to represent the ranges of months to maturity that you gave.

F1:F10 contains Months to Maturity--values between 0 and 180+
G1:G10 contains $ Balance.

C1 has formula =SUMIFS($G$1:$G$10, $F$1:$F$10, ">=" & A1, $F$1:$F$10, "<=" & B1). Copy down thru C6.