| CD Ladder Spreadsheet |
|
|
|
|
|
| Copyright: Explore Bonds |
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| This spreadsheet calculates the weighted average yield and duration of a CD ladder. |
|
|
|
| For new issue CDs, enter 100 as the price and 0 in the commission fields. If you are using fewer than 5 CDs, |
|
|
|
| enter 0.000001 in the Number of CDs field. For more information, see |
|
|
|
|
|
| Building a CD Ladder |
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
| Assumptions |
|
|
|
|
|
|
|
| |
CD #1 |
CD #2 |
CD #3 |
CD #4 |
CD #5 |
|
|
| Purchase Date |
11/17/2009 |
11/17/2009 |
11/17/2009 |
11/17/2009 |
11/17/2009 |
|
|
| Maturity Date |
09/29/2010 |
12/01/2011 |
09/28/2012 |
10/20/2013 |
10/20/2014 |
|
|
| Interest Rate |
1.35% |
4.05% |
4.85% |
2.85% |
3.25% |
|
|
| Price without commission |
100.000 |
103.374 |
105.250 |
100.000 |
100.000 |
|
|
| Commission per CD |
$0.00 |
$1.00 |
$1.00 |
$1.00 |
$0.00 |
|
|
| Minimum commission per order |
$0.00 |
$8.00 |
$8.00 |
$8.00 |
$0.00 |
|
|
| Number of CDs (1 CD = $1,000 principal) |
0 |
20 |
10 |
0 |
0 |
|
|
| |
|
|
|
|
|
|
|
| Calculations |
|
|
|
|
|
|
|
| |
CD #1 |
CD #2 |
CD #3 |
CD #4 |
CD #5 |
Total/Average |
|
| Price with commission |
100.000 |
103.474 |
105.350 |
100.100 |
100.000 |
|
|
| Total amount invested |
$0.00 |
$20,694.80 |
$10,535.00 |
$0.00 |
$0.00 |
$31,229.80 |
|
| Yield after commission |
1.35% |
2.29% |
2.88% |
2.82% |
3.25% |
2.49% |
|
| Maturity |
0.87 |
2.04 |
2.86 |
3.92 |
4.92 |
2.32 |
|
| Duration (years) |
0.86 |
1.91 |
2.65 |
3.68 |
4.51 |
2.16 |
|
| |
|
|
|
|
|
|
|
|