There are all kinds of calculators you can find to “optimize” your rate of return by laddering certificates. Of course the term optimize is a misnomer because you don’t know what the reinvestment rate will be on the shorter term CDs. There are some advantages to laddering such as, if you need the funds earlier than expected, you can wait until a maturity, or if you need it quickly, shorter-term certificates tend to have a lower penalty for early withdrawal. Some general assumptions about this workbook:
1. The CDs are compounded daily with a 30.41666 / 365 day count for month and year. I am not using dates, which would be necessary for actual / actual or actual/365 calculations.
2. The total return will be calculated over 5 years (60 months), regardless of if you pick the 60-month certificate as one of your investments or not. I would recommend that you do pick the 60-month as one of your terms.
3. The light yellow cells are all input cells. Enter the APR not the APY, which is calculated for you assuming daily compounding. Don’t bother filling in the dollar amounts, that will be done when you optimize. The “Terms” are drop down boxes, so you can include any term, out to 60 months.
4. The user of this spreadsheet will need to make a best guess as to what the average reinvestment rate will be over the 5 year holding period. The one guess is used for the average reinvestment rate after each certificate matures.
5. The workbook uses the Excel Solver function to optimize the amounts that should be invested in each maturity. I have run the program on Excel 13 and Excel 10, but it has not been test on any other versions.
You will need to enter the minimum amount you will invest in each maturity and the maximum. Enter the total dollars to invest and then click the green button.
6. You will see something like below after the Solver runs. Pick OK.
7. The table below is only the first 20 months of a total of a 60 month holding period. These are monthly rates.
8. In addition, you get the optimized APY, the final dollar value, and a chart: