How to Make a Monthly Home Budget in Excel (Planned vs Actual, Savings and Overspending Alerts)

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: home-budget-planned-vs-actual-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

The Bagale family in Pune (made-up numbers) earns ₹1,50,000 in October 2026: ₹85,000 + ₹62,000 salaries and ₹3,000 other income.

They planned ₹96,200 across 13 heads and actually spent ₹97,230, so the month went ₹1,030 over plan. Three heads caused it:

Petrol (₹400 under), Medical (₹1,150 under) and Miscellaneous (₹350 under) saved ₹1,900, so the net is −₹1,450 − ₹280 − ₹1,200 + ₹1,900 = −₹1,030.

The good news: actual spending split into Fixed ₹34,500, Variable ₹27,730 and Savings ₹35,000. Income minus actual leaves ₹52,770. Savings plus that left-over money is (₹35,000 + ₹52,770) / ₹1,50,000 = 58.5% of income.

2. Build it step by step

Step 1. Type the income

1. In A4:B4 type the headers Income and Amount.
2. In A5:A7 type the income names and in B5:B7 the amounts (yellow input cells).
3. In B8 type =SUM(B5:B7): ₹1,50,000.

Step 2. Make the budget table

1. In A10:F10 type Category, Type, Planned (₹), Actual (₹), Difference (₹), Status.
2. From A11 down, type one spending head per row (13 heads, rows 11 to 23).
3. Select B11:B43 > Data > Data Validation > List and type Fixed,Variable,Savings as the source (drop-down lists).
4. Type the planned amounts in C11:C23 and the actual amounts in D11:D23. A yearly bill can be typed as a formula: LIC ₹24,000 a year is =24000/12 in C20.

Step 3. Difference and status

1. In E11 type =C11-D11. A minus number means you spent more than planned.
2. In F11 type =IF(D11>C11,"Over","OK") (IF function).
3. Select E11:F11 and double-click the fill handle to copy down to row 23.

Step 4. Totals

1. In C24 type =SUM(C11:C23) and copy it right to D24: ₹96,200 planned, ₹97,230 actual.
2. In E24 type =C24-D24: −₹1,030.
3. In F24 type =COUNTIF(F11:F23,"Over")&" over": 3 over.

Step 5. Summary by type with SUMIFS

1. In E5 type =SUMIFS($D$11:$D$23,$B$11:$B$23,"Fixed"): ₹34,500 (SUMIFS).
2. In E6 and E7 use the same formula with "Variable" (₹27,730) and "Savings" (₹35,000).
3. In E8 type =B8-D24: ₹52,770 left over.
4. In E9 type =(E7+E8)/B8 and format it as % : 58.5%.

Step 6. Red alerts for overspending

1. Select E11:E23 > Home > Conditional Formatting > Highlight Cells Rules > Less Than, type 0, choose light red fill (conditional formatting).
2. Select F11:F23 > Highlight Cells Rules > Equal To, type Over, light red fill.

Step 7. Pie chart

1. Select D5:E7 > Insert > Pie (pie charts).
2. Click the chart > + > Data Labels, then show Percentage only: Fixed 35%, Variable 29%, Savings 36%.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Mixing planned and actual in one column. Keep two columns. The whole point is the gap between them.
2. Typing a yearly bill as a monthly amount. LIC, insurance and school fees are yearly or term-wise. Divide by 12 (=24000/12) so each month carries its share.
3. SUMIFS ranges of different sizes. $D$11:$D$23 and $B$11:$B$23 must cover the same rows, otherwise Excel shows #VALUE!.
4. Spelling the type differently. "Saving" and "Savings" are different words for SUMIFS. The drop-down stops this.
5. Adding a new head below the Total row. Insert the row above row 24 so it is inside every SUM and SUMIFS range.

5. More real examples

Example 1: Two-income family with a home loan. Replace Rent with Home loan EMI (Fixed). The EMI comes from the EMI calculator chapter.

Example 2: Single person in a hostel or PG. Heads: PG rent, mess, travel, mobile, fun, SIP. Same formulas; delete the rows you do not need.

Example 3: Festival month. Add a Diwali shopping head (Variable) with its own planned amount, so a festival does not hide inside Groceries.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I make a simple monthly budget in Excel?

List income at the top, then one row per expense with Planned and Actual columns. Difference = Planned − Actual, and SUM gives the totals. The template on this page has all of it ready.

Planned vs actual: which formula shows overspending?

=C11-D11 gives a minus number when actual is more than planned, and =IF(D11>C11,"Over","OK") writes the word.

How do I total only fixed expenses?

Use SUMIFS on the Type column: =SUMIFS(D11:D23,B11:B23,"Fixed").

Can I use this budget for every month?

Yes. Right-click the sheet tab > Move or Copy > Create a copy, rename it to the new month and type the new actuals.

How is this different from a business budget vs actual report?

A business report compares departments and variance %. This one is for a home: income, family heads and savings.

7. Quick quiz

Ten questions on this page. Sign in to save your score.