Budget practice exercises
The formulas behind a household budget: what is left after the bills, yearly bills turned into monthly amounts, spending per category, a running balance and how many months it takes to save up for something.
How to build a budget in Excel
A budget in Excel is a handful of simple formulas that work together. SUM and a minus show what is left after the bills. IF turns yearly and quarterly bills into monthly amounts, and SUMIF totals spending per category. A running balance tracks the money, and ROUNDUP shows how many months a savings goal takes.
With the income in B2 and six bills in B3:B8, =B2-SUM(B3:B8) shows what is left each month. With categories in column B and amounts in column C, =SUMIF($B$2:$B$7,E2,$C$2:$C$7) totals the category named in E2. The dollar signs lock the ranges, so the formula still covers the whole list when you fill it down. A running balance is =D2+B3-C3: the balance above, plus money in, minus money out.
For a savings goal, =ROUNDUP(B2/C2,0) gives whole months. A vacation of 2,400 with 350 saved a month comes out at 7 (6.86 rounded up). Use ROUNDUP, not ROUND, or 6.4 months would become 6 and you would come up short. Lock the ranges in SUMIF, but not the references in a running balance. The exercises below cover all five formulas.
- 1What is left after the billsIn B9, work out how much is left each month after the fixed bills: the income in B2 minus all the bills in B3:B8. Subtract one SUM instead of each bill on its own.
- 2Yearly and quarterly bills per monthPremiumIn D2, turn the amount in B2 into a monthly amount: divide yearly bills by 12 and quarterly bills by 3, and keep monthly bills as they are. Put one IF inside another.
- 3Spending per category with SUMIFPremiumIn F2, total the amounts for the category in E2 with SUMIF. Lock the two ranges with $, so the formula still covers the whole list when you fill it down to F4.
- 4A running balancePremiumIn D3, work out the balance after the paycheck: the balance in the row above plus what came in, minus what went out. Leave the references unlocked, so the formula works when you fill it down.
- 5Months to a savings goal with ROUNDUPPremiumIn D2, work out how many months it takes to save up for the vacation. Divide the price by the monthly savings and round up with ROUNDUP. Part of a month still means waiting a whole extra month.
Read the guide
Questions
How do I calculate what percent of my income each expense takes?
Divide the expense by the income, such as =C2/$F$1, and format the cell as a percentage. The dollar signs keep the formula on the income cell as you fill it down. If you total spending per category with SUMIF, divide that total by the income in the same way.
How do I add up the expenses for one month only?
Use SUMIFS with a date limit at each end. With dates in column A and amounts in column C, =SUMIFS(C2:C100,A2:A100,">="&DATE(2024,3,1),A2:A100,"<"&DATE(2024,4,1)) adds up March 2024. The end date is the first day of the next month, so the last day of March is included.
How do I flag a category that is over budget?
Use IF to compare spending with the budget: =IF(C2>B2,"Over budget","OK"). To turn the cell red, use conditional formatting with the formula =C2>B2 instead. IF can change the value in a cell but not its color.