A budget is the foundation of your financial health. Want to know if you can afford a vacation? How much you can save for retirement? How you can pay off your mortgage faster? These questions can all be answered if you have a budget.
This post examines the steps required on how to create a budget in Excel.
What is a Budget?
What is a budget? A budget is a listing of your income and the amount you can afford to spend on your expenses and put into savings. Income includes your wages and returns you get from your investments.
Expenses include every dollar you spend, from income taxes to housing costs to food to fun stuff like vacations, sporting events and going out for a nice dinner and drinks.
You’ll also want to include line items in your budget for the different types of savings – emergency, designated for specific large purchases and long-term/retirement.
To be clear, the budgeting process is to your financial health what cleaning your bathroom is to your physical health. Most people don’t find creating and sticking to a budget to be a lot of fun.
The longer you put it off, though, the harder it is to do. If you don’t clean your bathroom or make a budget, you can end up with physical or financial illness that can be really hard to overcome.
However, if you make a budget, you’ll be much more confident in your financial decisions. You will be less likely to run up more debt than you can afford to re-pay and will be more likely to be able to retire in the fashion you desire.
Receive Your Free Excel Spreadsheet
Have It Delivered Direct to Your Inbox
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
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 card 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.
- 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).
- 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.
This chart shows that, if you start saving at age 25 under the assumptions above, you need to save “only” 11% of your pre-tax salary in a Roth IRA or 401(k) every year until you retire.
On the other hand, if you are 45, have the characteristics above and use those assumptions, you’ll need to save 42% of your salary every year just for retirement.
Keep in mind that your employer’s matching 401(k) contributions reduce the amount you need to contribute. All of the values in this chart scale proportionally, so you can increase or reduce the salary percentages if your retirement needs are more or less than are assumed in the illustration.
Also, please note retirement plan limits have increased for 2020. Take advantage of them if you can when creating a budget in Excel and be sure to put as much in there as you can afford.
You Need a Budget
The amounts you need to set aside for emergency, designated and retirement savings are often a large percentage of your salary.
A budget is a critical tool for increasing the likelihood that you will realize your dreams and meet your current day-to-day needs.
There are many ways to find additional room for savings in your budget. By tracking your income, expenses and financial goals across time, you will position yourself better.
Whether you make a lot of money or live on a more modest income, take the steps listed above for creating a budget in Excel and learn how to take control of your financial future.
About the Site Author and Blog
In 2018, I was winding down a stint in investor relations and found myself newly equipped with a CPA, added insight on how investors behave in markets, and a load of free time. My job routinely required extended work hours, complex assignments, and tight deadlines. Seeking to maintain my momentum, I wanted to chase something ambitious.
I chose to start this financial independence blog as my next step, recognizing both the challenge and opportunity. I launched the site with encouragement from my wife as a means to lay out our financial independence journey to reach a Millennial retirement and connect with and help others who share the same goal.
I have not been compensated by any of the companies listed in this post at the time of this writing. Any recommendations made by me are my own. Should you choose to act on them, please see the disclaimer on my About Young and the Invested page.