How To Project Future Bank Balances In Google Sheets

Predicting future bank account balances in Google Sheets can help you maximize savings and debt repayments while avoiding overdrafts and interest payments.

The first step to understanding your financial position is knowing when money will come in and go out.

Predicting your future bank account balance and knowing how much cash will be available can help you avoid overdrafts and credit card interest while enabling you to maximize savings and debt repayment.

Projecting your future balances can be done in Google Sheets in 5 simple steps:

1- Review your past income and expenses

Your historical financial data will inform your future income and expenses. Ideally, you want to look at a full year of transactions to get a well-rounded estimate of your financial history. If you can’t aggregate a full year of data, aim for at least three months. Keep in mind that this data needs to be an accurate representation of actual income and expenses, not a budget.

If you don’t already use a spreadsheet to track your finances, you’ll need to download your bank transaction history.

image 11

2 – Forecast your income

Most people are paid weekly or biweekly, and their paychecks are the same amount every time. If that’s true for you, this step will be easy.

If, however, you’re a freelancer or work on commission, it may be difficult to anticipate your future income. Use your best judgment based on your historical performance. Don’t be too rosy in your estimates; it’s better to have more money coming in than you anticipated than to build a forecast on funds that never materialize.

3 – Forecast your expenses

Expenses like bills and utilities are regular and predictable and should form the foundation of your expense projections.

Irregular expenses such as entertainment, periodic insurance premiums, car maintenance, and birthday gifts are variable and, therefore, difficult to predict. While these are areas in which you can cut your expenses, it’s safer to take your past performance as a guide rather than assuming you will curb your spending.

4 – Aggregate your income and expense forecasts to estimate your future balances

The length of your future projections will be roughly equal to the length of your historical financial data, so if you analyze a year of past income and expenses, you should be able to predict your finances a year into the future.

Once you aggregate your estimated income and expense data, you can predict your net income and your account balances.

  1. Start by entering your beginning balance for the first month in your projection period (January for this example). Your beginning balance is the total cash you have on hand.
  2. Fill in your estimated income and expenses for each month.
  3. Using cell reference, calculate the beginning balance for the following month.

=beginning balance + total income – total expenses

=B2 + B7 - B19

The ending balance for one month is the starting balance for the next month.

Repeat these steps for each month in your projection period.

image 12

5 – Adjust your budget

Now that you know what to expect, you can adjust your budget. If your income is insufficient to cover your expenses or you’d like to have more money to save, invest, or reduce your debt, you’ll need to either increase your income or decrease your expenses.

With minimal effort, you may be able to monetize a hobby or make a little extra money working a fun side job for a few hours a week.

If you have no time to spare, consider setting spending limits on your discretionary expense categories by using a budget strategy such as the envelope budget method to decrease your spending.

The first step to achieving financial success is being able to project your future balances. Doing so enables you not only to plan your income and expenses but also to map out steps toward your financial goals.

Ansley Fender

Ansley Fender

Business financial consultant, personal finance coach, tax preparer, mother, spreadsheet nerd.

Leave a Reply

Start Your Free Trial

"There isn’t another tool on the market that does what Tiller can do.”
Will Hinton, Google Review October 30, 2023