How to Create a Budget in Excel

I have a budget spreadsheet for you to download and use. Simply enter your email address in the form above and have the Excel Budget spreadsheet sent directly to your inbox.

Use it to follow along with the steps outlined below to make sure you are learning how to create a budget in Excel which works for you.

As an overview, here are the steps necessary to create a budget in Excel:

1. Identify Your Financial Goals

In my case, my primary financial goal is to make sure I enjoy my retirement. For many of you, your goals will include down payments on houses, refinancing student loans, college for your children and funding retirement.

2. Determine the Period Your Budget Will Cover

The most common time periods for a budget include a monthly or annual view. If you budget for a month, it is critical to remember to add expenses that you don’t pay every month as you’ll need to set aside money in short-term savings to have the money when those expenses become due.

3. Calculate Your Total Income

Figure out how much Form W-2 or Form 1099 income you’re going to earn during your budgeting period (1099 vs. W-2 can result in different tax situations).

4. Begin Creating Your Excel Budget

Build a personal budget worksheet for tracking your expenses. Consider using the free Excel budget template provided in the email signup above.

5. Enter All Cash, Debit and Check Transactions into the Budget Spreadsheet

Enter all of the checks, debit and cash transactions from your checkbook or online bank account into the Excel spreadsheet and identify the types of expenses.

6. Enter All Credit Transactions

Enter all of the transactions on your credit cards into the same Excel spreadsheet, identifying the type of expense. Make sure that you don’t double count the credit card payments from your checkbook with the details of the expenses from your credit card bills.

7. Calculate Total Expenses from All Sources

Add up all of the expenses in (5) and (6) by type of expense. These totals tell you where you’ve been spending money and how much you’ve spent in total.

8. Identify Areas for Expense Reduction

Make a first pass at the expense part of your budget by looking at how much you’ve spent and whether any of your expenses are going to change.

9. Run a Comparison of Income to Expenses

Compare your expense budget to your income. If your expenses and target savings exceed your income, you need to figure out how to get more income (take a second job or, in my case, take on a consulting assignment in my retirement?) or cut expenses. At this point in the process, you will want to identify if you can save more in your retirement accounts about distinguishing needs and wants are critically important.

10. Be Diligent with Your Budget

Monitor your expenses to make sure that you are not overspending your budget.

To learn more in-depth steps, consider visiting Susie Q’s series of posts.

Do I Need a Budget? I’m Already Saving Some Money.

If you have been working for several years and are able to put money in savings, you might wonder why you need to create a budget. The biggest reason to make a budget at this stage of your life is to make sure you are putting enough money into savings.

I think of savings in three components:

Emergency Savings – Money to cover either your living expenses if you find yourself without an income or travel expenses in case of the death or medical emergency of a close family member. Most people recommend that you target three to six months of basic living expenses for your emergency savings. It is critically important to distinguish between what is an emergency and what is not. For example, a funeral is an emergency; a wedding is not.

If you want to be able to go to someone’s wedding, the costs should already be in your budget or come from savings specifically designated for attending weddings. Designated Savings – Money to fund large purchases you want to make in the future. Think of creating designated savings as budgeting over several years. For example, you will need to replace your car every-so-often or might have a few “dream” vacations you’d like to take or want to make a down payment on a condo vs. apartment -renting or to fund your children’s education.

-renting or to fund your children’s education. Unless you make so much money that you can fit the full cost of these items in one year’s budget, you’ll want to fund them over several years. Long-Term or Retirement Savings – Money to allow you to live when you are no longer working and living a Millennial retirement.

By creating a budget that includes all three components of savings, you’ll be more confident that you will be able to meet expenses when emergencies arise and realize your dreams. The amounts you need to include in your budget for emergency and designated savings are pretty straightforward.

Determine how much you need and the date by which you want to have those amounts available and do the arithmetic. For a bit more information on emergency and designated savings, you can see my three-part post on what to do with your savings.

How Much Retirement Savings Should I Budget For?

Figuring out how much to budget each year for retirement savings is much more difficult. There are many factors to consider.

How much you’ve already saved.

How much money you want to spend each year when you retire.

How much risk you are willing to take with your retirement savings.

Whether the amounts you set aside are before or after-tax (i.e., in HSAs , traditional or Roth IRAs and 401(k)s).

, and 401(k)s). Your current age.

Your target retirement age.

How long you will live.

Whether any of your employers provide you with a defined benefit pension plan.

How much you’ll get from Social Security.

Inflation rates between now and the time you die.

Tax rates on any non-Roth savings when you retire.

And so on.

To provide you with some insights on how much to save, I’ve created a very simple example.

You want to retire at age 60.

You want to be able to spend an amount equal to 80% of your current after-tax salary, adjusted for future inflation. Federal, state and social security taxes currently total 40% of your salary, so the equivalent percentage of your pre-tax salary is 48%.

You are willing to take the risk of the overall stock market, so we will assume you average 9% return on your investments.

Inflation is 4% per year until you die at age 90. Your salary increases with inflation plus 1 percentage point for merit raises and promotions.

You put all of your retirement savings in Roth accounts, so future taxes aren’t an issue.

You don’t have any retirement savings yet.

You don’t have or plan to get money from any defined benefit plans and, for conservatism, I will assume you get nothing from Social Security.

The chart below shows you what percentage of your salary you need to put in a Roth IRA and/or Roth 401(k) to meet these goals based on your current age.