The Top 6 Excel Formulas for Banking

Analytics In Banking

However popular Microsoft’s Excel is at banks, most bankers just use a fraction of the application’s capabilities. As data and predictive analytics become more popular, we still fall back on Excel as our go-to analytic engine. We chuckle when banks talk about using “artificial intelligence” when they are not using even the most basic statistical functions of Excel. We have discussed the basic formatting skills for bankers (HERE) and now highlight our top functions that get us through the day.


Starting Off


Organizing data is a critical first step in understanding what that data means.  By requiring bankers to use, load and organize data in Excel, the application forces us to make decisions about how to slice and calculate and interpret that data. There are many outsourced specialized applications that make that judgment for us, but by doing so they also outsource much of the thinking and insights of the banking business.  We are big proponents of Excel because it forces us to think and make decisions about banking.  We have many favorite Excel functions that we use but want to share our top six.


Our Favorite Functions


We want to highlight the function name, the syntax, a brief explanation of the function and give an example of how we use the function to help our banking business.  These functions are not only useful for bankers but are very simple to use.


Conditional Formatting Function: = Format Cells Based on Values


This is not a function but an operation that quickly takes a list of values and formats each cell by color code, by data bars, or by any one of a number of icon sets.  Each example of the three sample conditional formats is shown below. 


Excel for Bankers


We use this function to help us focus our marketing and relationship management resources on important clients.  We look at the following parameters to rate our relationships:  1) risk-adjusted return on capital, 2) shareholder value added, 3) total revenue and 4) lifetime value.  This allows us to quickly identify important and profitable customers to help us retain this business.  This function easily shows us which relationships require cross-sell or upsell to increase profits, and which relationships may never be profitable for us and may require cutting losses with those clients or market segments.


If Function: =IF( condition, [value_if_true], [value_if_false] )


Banks need to data mine their existing loan portfolio to prevent loan prepayments and protect existing loans from being poached by competitors.  This conditional function helps us determine if borrowers are top relationship for our bank, and then we determine if they meet criteria that makes the borrower more likely to refinance with a competitor.  We use this function to establish if the fixed rate loan has less than 2-years to a pricing reset, if the borrower is paying above market rate, and if the loan has no prepayment protection.  If loans meet any of these criteria we contact the borrower to present new loan commitment options.  We want to be the first to present the borrower a proposal because being preemptive allows us the opportunity to refinance our own loan before a competitor does.


Payment Function: =PMT(rate, nper, pv, [fv], [type])


Borrowers care more about their monthly payment rather than the interest rate on the loan.  This function calculates how much a borrower will pay in principal and interest every payment period.  Because the yield curve is so flat, this function shows that the monthly payment on a $1mm loan is only $192 more per month at a 20-year fixed rate versus a 5-year fixed rate.  This function is a powerful way for a banker to show borrowers various potential loan scenarios and the effect and cost of eliminating the borrower’s refinancing risk.  Furthermore, it also highlights how the 5-year fixed rate loan repricing creates a credit risk for the bank.


Net Present Value Function: = NPV(rate,value1,[value2],...)


This function is great to calculate the lifetime value of a loan or other banking products.  We were amazed when we started using this function to compare the amount of money our bank earns on short-term loan products (like bridge financing, mini-perms or construction loans) versus long-term stable loans (like permanent finance and real estate term facilities).  Given that commercial underwriting expensive and origination costs are high, this function gives bankers the ability to compare profit streams for various relationship timelines.  We find that longer-term relationships are much more profitable for commercial banking.


Weighted Average Life Function: = Sum (value1,[value2],...)


Excel does not have a loan-weighted average life function, but with a very simple sum function, and a two-step formula we create a formula that calculates loan average life.  This allows us to compare various amortization and loan commitment terms to try to find sweet spots in the market.  For example, the average life of a 15-year fully amortizing deal is actually shorter than the average life of a 25-year amortizing 10-year loan.  Because most borrowers only pay attention to contractual loan term, a 15-year fully amortizing loan can be more appealing to a borrower but is actually a shorter credit commitment than a slower amortizing 10-year loan.  This is a great example of using simple math to provide safer or more profitable loans.


Correlation Function: = CORREL(array1, array2)


This function allows us to determine the statistical relationship between two properties. For example, we track our cost of funding to various indices and market developments.  We have found that our cost of funding is more closely correlated to LIBOR than to the Prime rate even though we price very few deposits directly to LIBOR.  This phenomenon is true for almost all banks in the country.




At CenterState Bank we take data mining and quantitative business analysis seriously and we find that Excel is an invaluable tool that has democratized banking.  Excel’s transparency allows anyone with a computer and a database to assess financial data that previously would only be possible by large Wall Street banks or the academia.  Excel allows anyone with a computer to work, manipulate and interpret a large amount of banking data.  We also believe that Excel is a powerful tool that helps community banks to be able to effectively compete against the national banks.