How Do You Record Income and Expenses in Excel?


income expenses excel lg

I still remember when I first decided to start a budget. I didn’t want an app and I didn’t like the premade budget sheets I saw. So I wondered how do you record income and expenses in Excel?

Here’s what I did:

  1. Put all paychecks and income at the top of the sheet
  2. Have the far left column be for monthly expenses
  3. Ideally, put those expenses roughly in the order they get paid
  4. Have a column of formulas to the right that automatically subtracts the expenses from the total income
  5. Use a column to the right of the formulas for notes on payment dates, whether it’s auto-draft, etc
  6. Once created, lock the formula column to ensure the formulas don’t get messed up
  7. Have a blank version saved as a template
  8. Then copy the template each month for the next month’s budget

But don’t worry. I’ll walk you through each of those steps one by one with screen shots below!

Budgeting is a dirty word. We panic. Then we freeze. We get stuck in analysis paralysis. It’s scary! We foolishly believe we’re better off not knowing the state of our finances or that it’s somehow more stressful dealing with it than ignoring it.

Unfortunately learning how to make a monthly budget isn’t something typically taught in school.

The budgeting process steps weren’t taught to me by my parents.  At best I learned how to balance a checkbook.  For those of you under 40, a checkbook was an ancient paper artifact used to issue payment when cash became uncool.

In this post, I’m reviewing the key steps a successful budget needs to work. But I’m also giving you a copy of my free Excel budget template which you can download below.

Want an easy and cheap way to spend less on your monthly expenses?

The app Trim automatically looks at your monthly bills & spending and then cross-checks that with savings programs almost all vendors have.

When they match up, you save. They’ll even handle the hassle of canceling memberships you no longer want or renegotiate bills for you like insurance and cable bills. Just sign up and spend on your Visa card and earn automatic savings back on your statement!

CLICK HERE to learn more about Trim on their website.

How to get started budgeting 

Budgeting just takes a little focus, a little discipline, and communication with your spouse or partner.

Like anything worth doing, it will feel a little uncomfortable at first.  It will be a little challenging.  You may feel like giving up.  BUT once you push through and stick with it, after a month or two the habit of budgeting will start to feel natural.

This new habit will take the place of your previous spending habits.

My family first learned the budgeting process steps after my wife started listening to renowned personal finance guru Dave Ramsey on the radio in 2007 when we lived in Dallas. That started our personal journey out of debt and we began to budget, plan and save for our future.

As John Maxwell says,

“a budget is you telling your money where to go instead of wondering where it went”.

The budgeting process steps are, more specifically, you and your spouse talking about and agreeing, IN ADVANCE, how you want to spend your next month’s paycheck(s).

A funny thing happens when you stop just blindly charging on a credit card.  Instead of making credit card companies rich, you start to get intentional with YOUR money.

You start to put it where it brings the most value to your family.  Your family begins to start to evaluate purchases more carefully.

As a result of using a budget, YOU WILL SPEND LESS because you’re now paying attention to how much of your hard-earned money was being wasted by not noticing what it was being spent on.

Setting up your Excel budget for expenses and income

To get started, just open a blank Excel workbook.

We’re going to have 4 basic columns: labels, expenses, a formula column to calculate the running bank balance, and a column for notes. I like to put the income at the top off to the right, but you can put that wherever you like.

It should look something like this:

Then begin to list all your monthly expenses in column 1, ideally, in the order they get paid throughout the month. Make notes in the column if they are autodraft or have to be paid manually.

It should now look like this:

Now you’ll notice that some of the columns aren’t quite wide enough for the text. You might also want to make the text size larger so it’s easier to read.

In that case, if you aren’t that familiar with Excel, we’re going to set the font size where we want it (I like 14), and then double click in between the column we want to widen and the next one over (you can also just click and drag).

Like this:

Now we need to add your income.

You might get paid once a month, twice, or even weekly. And if you are married, you and your spouse might each get multiple paychecks.

But for my example, we’ll assume it’s 2 paychecks.

To get really precise with the budget we can put the 2nd one halfway down the month, that way we aren’t thinking that money is there before it really is.

But first, we’ll just put the dollar amounts for the paychecks. This should be your net pay.

Don’t worry, we’ll get to the formulas next.

So first, let’s total the paychecks so we know exactly how much money we have for the month. For this to work, the expenses HAVE to be less than the income. Some recommend having it be a zero balance.

Personally, I like to have a little cushion in case I forgot something or if there was an unexpected expense (like a high electric bill), or if a provider recently raised their rates.

So in the cell to the left of where it says “Total Income”, we’ll simply put this formula:

=sum(G2+G3)

Of course, G2 and G3 may not be the exact same cells you’re using, so make sure to use the actual cell labels for the cells where the dollar amounts of each paycheck are.

Here’s what that looks like:

It’s also worth pointing out that if you have several paychecks, rather than just adding up each one, you can do a range to add using this formula instead:

=sum(G2:G3)

That will simply add every number from the first to the last.

Now we’re ready to add the formulas which keep a running tally of the money left after each expense. But first, we need to plug in dollar amounts for each expense. If some vary like electricity or groceries, go with the average and err on the high side; better to be pleasantly surprised than in the hole.

I used these numbers as an example:

Now let’s create those formulas to keep us on track.

The very first one will be different since it pulls the income over. So let’s look at that formula first. We’ll use the formula below in the cell to the right of the rent amount:

=G2-B2

This simply takes the first paycheck (cell G2) and subtracts the rent amount (cell B2). It should look like this:

Now for the cells below that, before we add in the 2nd paycheck, they will all get the same formula. To get a little better specifics, I’m going to break up groceries and gas into 2 areas; one for each paycheck.

So in the cell to the right of the electric bill amount, we’ll use this formula:

=C2-B3

That takes the amount of money left over after paying rent (cell C2) and subtracts the amount of the electric bill (cell B3). It should look like this:

Now we’ll just copy that same formula for the cells below. When you copy and paste, Excel will automatically move the cells down one.

So simply click on cell C3 (where we just placed that formula) and hit copy (control C on a PC or Command C on a Max).

Then click 1 cell down and hit paste (control V on a PC or command V on a Mac). It should look like this:

Then just keep copying and pasting all the way down, stopping where we want to add the 2nd paycheck. In this case, I’ll add that 2nd paycheck after the internet bill.

So to add the 2nd paycheck in, we’ll use this formula:

=SUM(C6+G3)-B7

What this does is add the current balance (cell C6) and the 2nd paycheck (cell G3) and then subtract the internet bill (cell B7).

Now we’ll just continue copying and pasting the formula from any of the cells above the one we just did (I’ll use cell C6) into the remaining cells below.

If you’re using the same dollar amounts as my example, it should look like this:

Rather use my FREE Excel budget sheet than create your own from scratch?

No problem! Just Download your CLICK HERE to download a copy totally for free right now!

Do you have to use cash only for a budget to work?

A budget can work whether the budgeter uses cash, debit cards, or credit cards to pay for expenses. The key is to budget all expenses and not make emotional spending decisions that have not been planned for. 

And for credit cards, while I know that’s a dirty word to Dave Ramsey, they work just fine as long as you pay them off in full each month.

After all, if you boil Dave’s core message down, he simply wants you to be intentional with your money and spending choices.

And you can do that with credit cards and then have the convenience of getting points and avoiding the hassles many rental car companies give you if you only have a debit card.

It just takes discipline and a plan.

If spending like you’re in Congress sounds familiar, I highly recommend you take a moment and check out my previous post about how to Stop Living Paycheck to Paycheck.

It’s only a 5-minute read and I walk you through, step by step, how to better manage your money! Just click that link to read it on my site.

How does the cash envelope system work?

The cash envelope system simply uses several envelopes for different spending categories like groceries, gas, eating out, or clothing. Each payday, the budgeted amount of cash is withdrawn and placed in each envelope. Then when the envelope is empty, nothing more gets spent until the next payday.

In our house, we pay recurring bills online through our bank’s online payment system.

And for years, until we got out of debt and started building wealth, almost everything else we pay in cash using Dave Ramsey’s envelope system.  In this system, you take out the cash on payday and divide it up into the various categories on envelopes (groceries, gas, spending, etc).

Just click that link to read my deep dive on the system right here on my site.

The categories and amounts you and your spouse agree to in advance.  Then when the money’s gone, it’s gone until next payday.

Learning how to make a monthly budget teaches you very quickly to watch how you spend your money.

If you don’t have a spouse or significant other in some ways that’s easier.  You don’t have to come to terms with anyone else’s opinion.  The downside is there also isn’t anyone holding you accountable.  So just know you may need to be extra disciplined.

If you know the envelope system is right for you, Dave has a cheap starter envelope system on Amazon Prime for under 20 bucks!

Dave Ramsey Rant - You NEED A Written Budget

How does budgeting help in managing personal finances?

Budgeting helps manage personal finances as it tracks all income and every expense before they happen. It helps ensure there will be enough money for the month and helps to avoid overdraft charges. It allows spending choices to be made knowing the exact impact it will have on the household’s finances. 

Your family WILL benefit when you use a budget. As I already covered, you can eliminate debt by living on a budget.

When you have no debts (other than your mortgage) and you aren’t throwing money away to your bank you can begin to:

  1. Build long-term savings
  2. Save for your kid’s college (instead of going into debt again with student loans)
  3. Have an emergency fund (so you aren’t reaching for the credit card every time the car breaks)
  4. Start to save for your retirement (instead of wondering which kid will welcome you to their couch in your “golden years”)

At first, your family may question WHY you are budgeting.

They may wonder why you aren’t going out to eat in restaurants 5 times per week.  They might balk at not getting what they want every time they ask.

These are good lessons.

The world isn’t kind to people who feel entitled to everything.  It just isn’t.  Learning the value of a dollar and the value of working for what we want are lost arts.  Let’s bring them back!

Want an easy and cheap way to spend less on your monthly expenses?

The app Trim automatically looks at your monthly bills & spending and then cross-checks that with savings programs almost all vendors have.

When they match up, you save. They’ll even handle the hassle of canceling memberships you no longer want or renegotiate bills for you like insurance and cable bills. Just sign up and spend on your Visa card and earn automatic savings back on your statement!

CLICK HERE to learn more about Trim on their website.

What are the downsides of not budgeting?

The biggest downsides of not budgeting are risking overspending, incurring overdraft charges or interest charges, being unprepared financially for unexpected expenses, and living paycheck to paycheck instead of building wealth.

In short, if you delay using you’re putting your future at risk.

Not budgeting is the financial equivalent of just throwing darts at a dartboard with a blindfold on hoping you hit the target.  Sure you may occasionally get lucky, but you’ll put a lot of holes in the wall in the process.

There are really 4 main reasons people procrastinate on setting up a budget.

Curious if they sound familiar? Check out my recent article where I get into all 4 and show you how to get past them. Just click that link to read them on my site.

Having no budget means continuing to spend money based on your emotions. 

When we shop for groceries when we’re hungry, we buy more.  When we have a fight with our spouse, that new TV or clothes store is awfully tempting.

We make decisions not based on knowledge or facts but on our inner child’s emotionally immature voice saying “I want it now!”

A lack of a budget could lead to bankruptcy!

At our country’s all-time high a little over 10 years ago, according to Debt.org, 1 in every 55 households in the US filed for bankruptcy.  Think about that for a minute!  That is about 2 million families!

It’s a safe bet those families don’t use a budget.

And while I know people who make poor choices, file, continue to make poor choices, and file again, that’s not how you want to live your life.

For starters, every time you file for bankruptcy you’re destroying your credit for 7-10 years.  15 years ago, credit scores weren’t looked at as often as they are today.

Now a horrible credit score can affect:

  1. Your ability to get a mortgage or refinance
  2. What employers will hire you

If you struggle with bad credit, you will definitely want to check out my post called Credit Report Repair.

You don’t need to pay a credit repair company!  I walk you through every step you need to take to legally clean up your credit report.  It’s not always quick, and it pays to be tenacious, but it’s not hard to do yourself.

It’s also not as widely known that bankruptcy doesn’t eliminate all outstanding debts.

The following types of debt will most likely NOT be eliminated if you file for bankruptcy:

  1. Owed income taxes
  2. Child support
  3. Alimony
  4. Student loans (owed to the government)
  5. DWI fines
  6. Consumer debts over $500 incurred within 90 days of filing bankruptcy

So let’s take a final look at  . . .

Why a budget helps you and how to organize it better

1. HAVE A GOAL

You need to understand your goals before you set about trying to work through the budgeting process steps. Maybe you:

  • Want to get out of debt?
  • Stop living paycheck to paycheck?
  • Starting saving for retirement?

So understand first why you want to change your money habits and focus on that end goal.

2. GET ON THE SAME PAGE WITH YOUR SPOUSE

Money fights and money problems are one of the top 3 causes of divorce and it’s SO easy to avoid that by just meeting, listening, respecting, and communicating with one another about your budget and your life.

If you and your spouse are struggling with money or other issues, one of my most popular posts is called Top Reasons for Divorce.  It’s not too late to fix your relationship with your spouse!  I know!  I’ve been there too.

3. PUT IT ON PAPER

A budget can’t just exist in your head.

It needs to be on paper, on a spreadsheet, or on an app.  I have used my Excel budget spreadsheet since 2007.  I prefer that as it’s totally customizable.  The formulas do the math for me eliminating the possibility of addition errors.  I can also modify throughout the month when the unexpected happens.

My Excel doc lives in the cloud (I use Dropbox), thus I can access it wherever I am; even on my phone.

4. START BEFORE THE MONTH BEGINS

In order for a budget to work, you have to plan in advance.

You have to write it out some time a few weeks prior to the start of the next month.  This way by the time the month starts, you already know exactly where every dollar of income is going.

Almost nothing happens exactly on the 1st of the month.  In our house, our paychecks and bills all happen sporadically throughout the month.  Therefore it’s crucial that we already know what’s happening before the month starts.

5. MAKE THE BUDGET WITH YOUR SPOUSE

It’s totally normal for one of you to be more the math nerd.  That’s OK.

It’s also OK for the one spouse who isn’t the bill payer to not be AS involved in the minutia of the budget.  But as I said above, it’s CRUCIAL that both of you be on the same page.  You have to talk about your expenses before they happen.  Both of you need to be in agreement on where your money is going.

It’s vitally important that you both agree on things like spending limits, cash withdrawals, credit/debit card use, etc.

6. KNOW YOUR TOTAL INCOME FOR THE MONTH

Unless you’re an Uber driver or freelancer this one should be pretty easy.

You know (roughly) what your paychecks are going to be the next month and what the paydays are.  If your paychecks are irregular (in amount or paydate) that does make it more challenging.  It is still worth doing though.

One thing I would do if the amounts vary is to go with the lowest possible average amount.

Look at each check for the past 6 months and average that.  Then see how that amount compares to your lowest check.  Estimate a paycheck somewhere in the middle of that range.  I’d rather be pleasantly surprised (by a bigger check), than scrambling to figure out what to cut (due to a smaller check).

If the date of your checks varies, it may make more sense to do a 2-week budget.

It may also make more sense to do what businesses call accruals.  In this case, you basically earmark money now for known expenses later.  To avoid temptation transfer that money into a savings account so it’s not gone by the time you need it.

Think you can’t budget if you’re broke?

That’s actually when you need a budget the most. And you CAN make it work even on a low income. Check out my recent article where I walk you step-by-step on exactly how to do it. It’s not that hard, but there’s 1 step that’s crucial to success.

Just click that link to read it on my site.

7. SUBTRACT AND PRIORITIZE YOUR KNOWN EXPENSES FOR THE MONTH

Once you know your total income (at the top of the page) and all your normal monthly bills, it’s time to prioritize the bills and begin to subtract those out from the income.

In tight financial situations, prioritizing your bills is crucial.  In the event you aren’t able to pay everything, your mortgage and power bill are definitely going to take the top spot from cable and other more frivolous expenses.

Prioritize roughly in this order:

  1. Mortgage
  2. Electricity & water
  3. Groceries
  4. Gas money
  5. Cell phones
  6. Internet/cable
  7. Outstanding debt
  8. Retirement savings
  9. Kid’s College Savings
  10. Spending money for parents & allowance for kids

As you go down the list ideally at the bottom would be zero.

If you find you get to the bottom and it’s negative you want to see what you can cut back on elsewhere (either permanently or one-time).  You may also want to start to plan for a long-term career change to boost income.  Worst case scenario, the things at the bottom of the list don’t get any money allocated at this time.

One trick I have done with credit cards when I was unable to pay the minimum monthly payment is just to send them something.

In theory, they can’t report you as 30+ days past due just because you paid them less (than their minimum), if you make your payments on time.  Sending anything is better than nothing.

They may call and harass you but you’ll soon learn to let those calls go to voicemail while you get your financial house in order.

8. HAVE A PLAN FOR IRREGULAR EXPENSES

Things like oil changes, car registrations, and inspections are irregular expenses. By that I mean they don’t happen every month. So we need a system where when that time comes we don’t panic because we haven’t budgeted it into our monthly budget.

You can create events in a Google Calendar with email reminders (remember to set them more than a month ahead so you have time to work it into the budget). Or if you trust yourself to not spend it, you could set aside small chunks of money that you leave in checkings or savings accounts to pay for those bills when they come due.

Lastly, what I use (for this and a million other things) is an online/app-based to-do list planner called GQueues.  The desktop version is free and the mobile app is extremely cheap and I love it.

Check out the complete details on my GQueues Review page.

9. HAVE A PLAN FOR HOLIDAY SPENDING

Whether you celebrate Christmas or Hanukkah or another late fall/early winter holiday I bet you have increase spending around that time. It’s a time of parties and gift-giving and almost any household budget can get derailed quickly without a plan.

In my house, we set aside about $75/month starting in January through November 1st. We put it in a special Christmas Club Savings Account at our local credit union. On Nov 1st they automatically transfer it back into our checking.

See more details in one of my most popular holiday posts about How to Save Money for the Holidays. I get into how I save $2,000 each year for Christmas without a hitch.

Just click that link to read it on my site.

Income and expense Excel sheet – free download

I have a copy of my free excel budget template available at no charge.

(hint: it’s the same one my family used to pay off over $80,000 in debt!) – Now that you know the budget processing steps, you need a good system!!

This is the very same Excel budget template I have used since I first started listening to Dave Ramsey in 2007.

It’s a simple, highly customizable, Excel spreadsheet and you can download it quickly and easily FOR FREE by clicking below!

budgeting-process-steps-free-household-budget-template-spreadsheet-middle-class-dad

Once you click above the budget template will be in your inbox in minutes.

If you have questions about how to use it, I also have a short video tutorial on YouTube that walks you through EXACTLY how to use it.

Check that out here:

How to Use the Best Excel Budget Spreadsheet Template Step by Step

Final thoughts

In this post, I reviewed the top budgeting strategies that my family has used for over 9 years.

We went from upwards of $80,000 in debt to being debt-free and you can too!

But more importantly, I gave you access to my free Excel budget template which you can download today for a better tomorrow!


If you like this post, please follow my Budgeting board on Pinterest for more great tips from myself and top budgeting experts!

--


Photos that require attribution:
Image by Fateh Muhammad Raja from Pixabay

Top Related Posts