To understand this next spreadsheet, you have to take your accounting hat off and put your financing hat on. Consider all your sources of funding; savings accounts, money market accounts, CDs, borrowing, and checking accounts. There may be more, but considering the above, checking accounts will always be the cheapest source of funds. The reason for that becomes obvious when you consider the fees that are possible from checking accounts. That is why I said don’t think like an accountant about fees. I look at these fees as a reduction of the cost of funds in this spreadsheet.
The checking account makes possible all the fees like NSF (non-sufficient funds), overdraft transfer fees, returned check fees, monthly service fees, and many more. A virtual cornucopia of fees. Overdrawing an account alone costs customers $225 in fees each year on average, according to a study by the Consumer Financial Protection Bureau. Just do a Google search for “bonus checking” and you will get virtually every major bank, most medium size banks, and almost every credit union offering you a bonus from $50 to $250 to open a checking account. There are all sorts of conditions tied to the bonus, all designed to make the new checking account your primary account.
There are two sheets in the workbook. First we will consider the sheet called “Cost of Funds”. This is by no means a complete analysis of the benefits of a checking account, but it might get you thinking about just how profitable checking accounts can be.
Follow my assumptions in the image below. All yellow cells are input cells. In this example, a $500 initial deposit is expected to stay in the account, on average, for the whole year. The promotional amount is $100 that will be paid out after 3 month. Next is a guess as to how many NSF fees will be charged during the year (3) and the amount of the fee ($37). The spreadsheet assumes the NFS fees will come in the middle of the year. The limit for this analysis is one a month (12). Next, I assumed a cost of maintaining the account was $2.50 a month. I assumed it would cost around $5.00 to set up the account. I also assumed a $5.00 monthly service fee. I assumed you incentivized the branch personal to open these accounts with a $10.00 one-time incentive. I also assumed that the account would pay interest of .10%.
All of the cash flows are totaled for the first years and the second year. Then an internal rate of return is run on the cash flows to determine the “Cost of Funds APY” (in red). In this example, the customer paid the bank 5.205% (negative cost of funds) for the privilege of holding his/her $500 in a checking account. Comparing that with the cost of a one-year CD at .75%, the dollar advantage to the checking account over the CD for the first year was almost $30. The second year, and thereafter, is even better due to the promotional payment not repeating. The green cells are conditional formatted to show formulas in the table.
I told you there was another sheet. Suppose your compliance officer comes to you needing an APY on the above checking promotion for regulatory purposes. Again, the yellow cells are input cells. First, enter the opening balance ($500) and then the Bonus promotional payout ($100). Next you enter the month the bonus will be paid (3) and when you assume the opening balance will be withdrawn (in this example for the full year). This is a 23.41% APY.
Some people might take advantage of the promotion and take the $500 out on the third month, after receiving the bonus. Enter 3 for “Opening Balance” and see what that does to the APY.
Download workbook “Bonus_Checking” from:
Downloads Written in Excel 2013