You will need Excel 7 or higher, due to the use of a “table” in this spreadsheet. This post is aimed at smaller credit unions and banks that don’t have a staff of financial analysts. You do need access to four pieces of information about each of your certificates. You need the current balance, maturity, original term, and the rate. The fifth column is a helper column that multiplies the rate times the balance of each certificate. We will use this column to create a formula in the pivot table, so that each time we show a balance, the rate will be an average weighted rate. I have almost 30,000 certificates for my example, so when you dump your data into the table, don’t forget to clear my old certificates, if you have less than 30,000. I used Excel’s random functions to create the dates and rates, so longer certificate rates will not necessarily be higher than shorter maturates.
The pivot table is organized as below. The field “AWR” is the formula I mentioned earlier.
You get to the pivot table formula (in Excel 2013) from here:
I made it a little more confusing than necessary when I later changed the name of the field “AWR” to “Ave_Rate”.
I used slicers to control the chart. There are three slicers that filter the chart data; Original Term, Years, and Months:
When you select any of the slicer data, it turns green. The funnel object in the upper right hand corner of each slicer will select all buttons that have data in that slicer. Based on the above choices, the chart would look like this:
The bars represent the certificate balances and the red dots are the average weighted rates. The actual balances and rates are shown in the table below the chart. There are numerous ways to break down the certificates. I won’t try and show all the combinations. Leave a comment or question if you need more explanation.