Bond Maturity
Want to aggregate values by maturity bands, i.e.,
Value of Securities maturing less than 1 year. Value of Securities maturing 1 - 2 years Value of Securities Matruing 2 - 5 years etc.
|
A | B | C |
|---|
| Security | Value | Maturity |
|---|
| Security A | 200 | 6/30/2004 |
| Security B | 500 | 12/15/2006 |
| Security C | 400 | 3/15/2008 |
| Security D | 1000 | 5/15/2011 |
| Security E | 450 | 8/15/2003 | |
Less than 1 =SUM(IF(C2:C6<=TODAY()+365,B2:B6,0))
1-2 years =SUM(IF(C2:C6>TODAY()+365,IF(C2:C6
2-5 years =SUM(IF(C2:C6>=TODAY()+730,B2:B6,0))
365 = days in one year, 730 = days in 2 years
Remember, at the end of each formula, you need to hold Ctrl & Shift keys, while pressing the Enter key; to tell Excel you have entered an Array formula.
|