How to Create an Excel Spreadsheet Annual Budget

Last Updated: June 3, 2021 Tested

You will learn to create an annual budget on an Excel spreadsheet that is sensitive to many Income Tax Schedule A itemized deductions, as well as Schedule C for a small business (which applies these days to a lot of people). The included example is designed primarily for a couple in semi-retirement, both working; it thus includes accounts for younger and older citizens pertinent to their inclusions and deductions on their annual 1040 and state tax returns. If you follow the steps below to set up your budget, you can tweak the inputs depending on your needs, and create a plan suited to your specific case.

Steps

1
Open Excel. Double-click the green X on the dock, or open the Applications folder and then the Microsoft Office folder, before clicking Excel.
2
Open a New Workbook. Title the top, leftmost worksheet, "Actuals".

Advertisement
3
Into cell B1, type the date 01/31/16, or the current year if other than 2015.
4
Select cell range B1:M1. Do Edit > Fill > Series Rows Date > Month > Step_Value 1 > OK
5
Select columns B:N, right click, and hit "Format Cells". Select the "Custom" option under the Number tab. Pick $#,##0.00;-$#,##0.00 with column width 1.
6
Select range B1:N1, right click, and hit "Format Cells.". Select the "Custom" option under the Number tab. Pick mmmm.
7
Enter in cell N1 the label Year-to-Date.
8
Enter in cell N4 the formula, =sum(B4:M4). Then Edit > Copy the formula and Edit > Paste it to cell range N4:N110.
9
Enter the following labels to cells A1:A110. They should cover all of the budget items you need:
- ANNUAL BUDGET
- INCOME:
  - Source 1 - Net Paycheck(s), excl. taxes, 401K, etc.
  - Source 2 - Trust Income
  - Source 3 - Dividend Income
  - Source 4 - Interest Income, excluding Savings
  - Source 5 - Schedule C Income
  - Source 6 - Home Rental Income
  - Source 7 - Other Income, from Investments/Other
  - Refinance (REFI) Loan Receipt
  - Misc. Income (Yard Sales, etc.)
  - Other Assets Sold for Cash
  - Other Assets Sold for Cash Installments
  - Gifts Converted to Cash
  - TOTAL INCOME
- SAVINGS RECAP:
  - Beginning Balance
  - Add: From Source 1 - withdrawable 401K/Other
  - Add: Regular and Other Savings Contributions
  - Deduct: Withdrawals (new trust: gain, sale of home)
  - Add: Interest Earned
  - Deduct: Fees & Charges
  - Ending Balance
- PRINCIPAL and OPTIONS:
  - Trust Balance
  - Non-withdrawable 401K/Pension Balance
  - Other "untouchable" Principal balance(s)
  - Stock Options, Unexercised, at est. market value
  - TOTAL PRINCIPAL and OPTIONS
  - TOTAL SAVINGS, PRINCIPAL and OPTIONS
- Equity additions & Expenses:
  - Home - Mortgage Interest /Rent, w/ REFI int.
  - Home - Equity, Repairs & Improvement, w/ REFI Princ.
  - Remodeling
  - Roof
  - Driveway
  - Home - Maintenance
  - Yardcare & Gutters Maintenance
  - Sewage Line Maintenance
  - Home- Property Tax
  - Home - Insurance
  - Home - Other Mortgage-related Exp.
  - Home - Rental Portion Improvements & Repairs
  - Home - Rental Portion Maintenance
  - Home - Rental Portion Property Tax
  - Home - Rental Portion Insurance
  - Home - Rental Portion - Other Expense
  - Auto Payment(s) - Interest
  - Auto Payment(s) -Equity
  - Auto Insurance w/ GroceriesTransport
  - Auto Gas - w/ GroceriesTransport
  - Auto Oil & Maint. w/ GroceriesTransport
  - Auto Repairs w/ GroceriesTransport
  - Auto License, Fees, Registration Expenses
  - Auto Depreciation/Obsolescence
  - Sys: Macs, Phone, TV, Printer Ink & Ppr
  - Sys: Software & Hardware Equity
  - Sys: Other Tech Accessories
  - Sys: Depreciation/Obsolescence
  - Supplies
  - Groceries, Rx & Comestibles (non-deductible)
  - Moving Expense
  - Credit Card - Interest payments
  - Long-term Loan Repayments, e.g. Educatnl. Princ.
  - Long-term Loan Repayments, Interest
  - Short-term Loan Repayments, Principal
  - Short-term Loan Repayments, Interest
  - Utilities: Garbage & Recycling
  - Utilities: Gas & Electric
  - Utilities: Water
  - Medical: Transport, Tests & Procedures
  - Dental: Including Transport
  - Vision & Eyewear, w/ Transport
  - Chronic Conditions Counseling, w/ Transport
  - Legal Fees/Retainer, etc.
  - Other Professnl Fees, Dues, Subscrptns, Mmbrshps
  - Career/Professional Library +/or Software, Aids
  - Education & Training Expense Unreimbursed
  - Donations: Church and Other Tax-deductible
  - Donations: Non-deductible
  - Gifts
  - Sched. C - Accounting / Bookkeeping Expense
  - Sched. C - Payroll Expenses
  - Sched. C - Production & Pkg'g Expenses … or
  - Sched. C - COGS & Supplies Inventory Exp'd.
  - Sched. C - UPS / Freight / S&H and Mail Charges
  - Sched. C - Admin, Sys & Communications Expense
  - Sched. C - Mktg / Promotional / Selling Expense
  - Sched. C - Meals & Entertainment Expense
  - Sched. C - Travel Expense
  - Sched. C - Facilities Maintenance Expense
  - Sched. C - Licenses, Fees, Registration Expenses
  - Sched. C - Other Internet Expenses
  - Sched. C - Other Expense
  - Other Tax-Deductible Expense
  - Other Non-Deductible Expense
  - Miscellaneous Expense (= Supplies?)
  - Equity additions & Expenses:
- ANNUAL BUDGET RECAP:
  - Cash On Hand: OVER (SHORT), Beginning Balance
  - Total Income
  - LESS: Regular Savings Contribution
  - ADD: Withdrawals from Savings (except new home)
  - LESS: Equity additions & Expenses
  - Cash On Hand: OVER (SHORT),Ending Balance
- (Note: if you're SHORT per your Budget, you need to spend less, get a loan and/or to make more money.)
10
Do the math. Follow the subsection instructions for January as to whether to add all the items in a subsection (like INCOME or SAVINGS) or subtract some. Then bring the subsection total or line item down to the ANNUAL BUDGET RECAP bottom section and add or subtract accordingly -- careful, some are reversed from Savings because a contribution to Savings is a deduction from Cash on Hand, and a Withdrawal from Savings adds to Cash on Hand.
11
Copy the January formulas over to columns C:N.
12
Click Edit > Move or Copy Sheet. When you have your copy, retitle it Budget. Again make another copy of the Actual sheet and title it OVER (SHORT). Subtract your Actuals from Budget to arrive at OVER (SHORT) by clicking in cell B4 of OVER (SHORT) and entering the formula, =Budget!B4-Actuals!B4.
13
Copy and paste that formula from cell B4 to cell range B4:N110. Clear any blank rows you may have inserted so you don't end up with cluttering zeroes.
14
Remember that if you insert or delete a row line-item on Actuals or Budget, you must insert or delete the same row also on both of the other two sheets as well, and adjust the formula(s) accordingly.
15
Consider the notes for this example:
- This couple / family receives at least 1 paycheck, more probably at least 2.
- They also have a supplemental trust left to them.
- They have other investments and are pretty thrifty.
- They own a small business they report on Schedule C of Form 1040.
- They own their own home and rent out a room to also supplement their income; the tenant also does housework and cooking, etc. in lieu of cash rent.
- They have recently refinanced their home to take advantage of low interest rates and to do a remodel, driveway repaving and fix the roof.
- However, the refi wasn't as much as they wanted, so they've had yard sales and also sold some recreational vehicles and assets of their youth -- they're semi-retired -- and they also sold one of their two cars. They're going to either buy a smaller home or retire to a senior community -- they haven't decided which yet though.
- They do have a fair retirement "nest egg" built up though, that they'd like to avoid drawing down from for as long as possible.
- So now, one of them pays a friend to transport her to doctor's and dental appointments, grocery shopping, etc., and this was all worked out on a mileage basis, with amounts factored in for repairs, insurance and fees, etc.
- Her husband, though working and helping her run the small business, went back to school, and will need to start paying back an educational loan this year. His employer covered most of the cost of the texts and school supplies but not the laptop or home PC he bought, which he also uses in his home business, and keeps a career library on and other professional software (which is deductible above what was reimbursed by the employer).
- The couple has certain medical, visual and other physiological issues -- all deductible, as well as the transportation to and from the offices.
- The couple is active politically but these expenditures are not deductible in most cases.
- The couple requires assistance with certain aspects of the bookkeeping and accounting for the small business, and they also have hired a part-time production assistant, so they needed help coping with all the payroll, insurance and human resources issues as well.
- The new company will operate partially over the internet via a website, which is under development, for a "one-time" fee, which is deductible.
- They plan to use Excel to help budget both their business and personal financial activities, by copying these accounts to a second and third worksheet. The leftmost tab or worksheet will contain Actual amounts, the middle worksheet will contain Budget amounts, and the rightmost worksheet will compute the difference as OVER (UNDER) amounts. With this workbook, a Cash Flows worksheet, a Balance Sheet and a Profit & Loss Statement, they will have the reporting they need to not only stay on top of their business but also take all the deductions on their tax forms to which they are legally entitled, so long as they maintain their files, ledgers and receipts in good order.
- You will note that, because this document is designed for people in semi-retirement, it contains many tax items faced by other people with fewer years of work behind them (but may be missing a huge item like day care), as well as addressing many of the concerns of many senior citizens.
- The following is the Budget for the semi-retired couple. Note that items which have a Beginning Balance and Ending Balance have the Beginning Balance transferred over to the far right YTD column, which otherwise sums across, except for the Ending Balance line, which sums down vertically. Mostly, the Year to Date column sums across horizontally. Months April to October are filled in but hidden.

Community Q&A

Question

How do I add and subtract in a column?

Wordsmth

Community Answer

Select the column you want to display the answer. Click that column. Know which cells contain the items you want to add or subtract. Example: You want to add the numbers in D10, D11, and D12 and display them in D13. Click on D13. Then enter: "=D10+D11+D12" (without the quotes, but with the equal sign). D13 will display the answer. For larger columns (or rows) you could enter "=sum(D11:D13)" The colon (:) means all the cells in that range, so you're telling Excel to add all the cells from D11 to D13. To subtract D11 from D10, the formula would be "=D10-D11". Again, remember to include the equal sign.