One problem with budgeting interest expense, is that the typically credit union will have a majority of their CDs maturing during the next 12 months. The reinvestment rates for those CDs can make a significant difference to overall interest expense. This spreadsheet uses a data table of all CDs at the end of the current period. The table includes the original term, the balance, maturity, and APR.
The assumption for this model is that all CDs maturing in the next 12 months will roll over into the same term. The interest rate (reinvestment rate) for these CDs will is determined by a lookup table of interest rates. The rates in the table are the organizations forecast of interest rates on CDs over the next 12 months.
The model will then take all CDs that mature in the next 12 months and do a lookup from this interest rate table. For example, a 24 month CD that mature in March will be reinvested at 2.35% for the remaining time of the budget period. CDs that do not mature over the next 12 months are also calculated, so interest rate expense for all CDs is given.
The example below shows the value of each CD that matures during the next 12 months, including interest. Then that balance is reinvested until the end of the period at the new reinvestment rate:
Because the model is limited to one rollover during the budget year, 3-month and some 6-month CDs will mature and be reinvested at a new rate once, and will assume to earn that rate until the remainder of the year.