How to work out the business-use share of home costs in a spreadsheet (US and UK)
If you run a small business from home, some of your household bills are partly business costs. Rent or mortgage interest, electricity, heating, your phone, your internet.
The hard part is not the idea. It is the split. How much of the electricity bill is "business"? And how do you keep a record that still makes sense next year?
This guide gives you a small spreadsheet that does the split for you, and explains the two flat-rate shortcuts (one in the US, one in the UK) so you can compare. It works in Excel and Google Sheets. It takes about 20 minutes to set up.
In a hurry? The free Home office business-use calculator does the same split and the flat-rate comparison in your browser, with no sign-up.
This is a general guide to record keeping, not tax advice. Rules depend on your country, your business type and your situation. Check the official pages linked below, or ask a tax professional before you file.
What you will build
- A Settings area with your business-use percentages and how you got them.
- A Bills table that splits each bill into a business part and a personal part.
- A Summary row with the yearly totals.
- A comparison against the flat-rate option, so you can see which one is bigger.
Step 1. Decide a fair basis for each kind of cost
Each cost needs a reason for its percentage. Use something you can measure and explain.
| Cost | Common basis | Example |
|---|---|---|
| Rent, mortgage interest, insurance | Floor area used only for work | Office room 12 m² of a 60 m² home = 20% |
| Electricity, heating | Floor area, or floor area adjusted for hours | 20% area × share of time used |
| Phone | Share of calls or time for business | Check one typical month of your call log |
| Internet | Share of time or use for business | Hours online for work vs total |
Two tips:
- Write the reason down. "Office = 12 m² of 60 m², measured Oct 2026" is far better than just "20%".
- Use one typical month as a sample for phone and internet, and keep that month's notes.
Step 2. Set up the Settings area
On a sheet called Settings, make a small table:
| A | B | C | |
|---|---|---|---|
| 1 | Item | Business % | How I got it |
| 2 | Area | 20% | Office 12 m² / home 60 m² |
| 3 | Energy | 15% | 20% area × 75% of the year used |
| 4 | Phone | 40% | Call log sample, March |
| 5 | Internet | 50% | About 4 of 8 hours online a day |
Format column B as a percentage. Every other formula will read from here, so if a number changes, you change it in one place.
Step 3. Make the Bills table
On a sheet called Bills, add these headers in row 1:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Date | Bill | Type | Amount | Business part | Personal part |
Column C holds one of the item names from Settings: Area, Energy, Phone or Internet.
In E2, look up the percentage for that type and multiply:
=D2*XLOOKUP(C2,Settings!$A$2:$A$5,Settings!$B$2:$B$5,0)
If your Excel does not have XLOOKUP, use this instead:
=D2*IFERROR(VLOOKUP(C2,Settings!$A$2:$B$5,2,FALSE),0)
In F2, the personal part is simply the rest:
=D2-E2
Copy E2 and F2 down for every row. To stop typos in column C, add a dropdown: select column C, then Data > Data validation, and choose a list from Settings!$A$2:$A$5.
Round only at the end. If you need whole amounts, wrap the final totals in ROUND(...,0), not each row.
Step 4. Add a yearly summary
Under the table, or on its own sheet, total the business part by type:
=SUMIFS(Bills!$E:$E,Bills!$C:$C,"Energy")
Change "Energy" to each type. Add one more line for the grand total:
=SUM(Bills!$E:$E)
Now you have one number per type and one total for the year, and every number traces back to a bill and a stated percentage.
Step 5. Compare with the flat-rate option
Both the US and the UK offer a simpler method for some home costs. It can be smaller or larger than your actual split, so it is worth comparing.
United States: simplified home office method
The IRS simplified option is $5 per square foot of the home used for business, up to 300 square feet. That makes a maximum of $1,500.
The space still has to be used regularly and exclusively for business. The simplified option does not change who qualifies. Employees cannot claim this deduction for a home office.
In the sheet:
=MIN(OfficeSqFt,300)*5
Source: IRS - Simplified option for home office deduction
United Kingdom: simplified expenses for working from home
If you are self-employed and work 25 hours or more a month from home, HMRC lets you use a flat rate instead of working out a share of bills:
| Hours of business use per month | Flat rate per month |
|---|---|
| 25 to 50 | £10 |
| 51 to 100 | £18 |
| 101 and more | £26 |
The flat rate does not include phone or internet. You can still claim the business share of those separately, using your own split like the one above.
A formula for one month, where B2 holds hours worked at home:
=IFS(B2>=101,26,B2>=51,18,B2>=25,10,TRUE,0)
Add up the 12 months for the year, then add your Phone and Internet business parts from the Bills sheet.
Source: GOV.UK - Simplified expenses: working from home
Common mistakes
- No reason for the percentage. A number with no note is hard to defend and hard to remember.
- Using the whole home. Only the part used for work counts, and in the US it must be used only for work.
- Missing phone and internet in the UK. The flat rate does not include them. Claim their business share once, from your own split.
- Forgetting to update. If you move, change rooms or change how much you work from home, update the Settings sheet and note the date.
Keep the records
Keep the bills, the floor plan or measurements, and your sample-month notes with the spreadsheet. How long depends on where you are. See our guide on how long to keep business records.
Summary
- Pick a measurable basis for each cost and write it down.
- Put the percentages in one Settings table.
- Split each bill with one lookup formula.
- Total by type with SUMIFS.
- Compare with the IRS or HMRC flat rate and use what fits your situation.
This article was drafted with the help of AI and checked against the official IRS and GOV.UK pages linked above on 21 October 2026.