An income-based gas reimbursement tracker works best when it separates the fill-up, each person's share, and the later repayment. Put agreed income figures and percentages on one tab, log each purchase once, then use a balance tab to show who should receive money and who should send it.
That distinction matters. A $60 fill-up paid by Alex isn't a $60 expense for Alex alone, and marking the transaction as paid can hide Jordan's and Taylor's open balances. The setup below works for a stable roommate group or travel group in Google Sheets, with a second layout for trips where riders change.
Choose the split rule first
Income-based splitting is a choice, not a default. Turns out, the formula is simple; agreeing on the rule takes more care.
| Split rule | Works well when | Tradeoff |
|---|---|---|
| Equal | Everyone uses the car or household resources about the same | Simple, but it ignores income and uneven usage |
| Usage-based | People ride different distances or join only part of a trip | More precise, but you must record participants or miles |
| Income-based | The group wants the cost tied to ability to pay | Requires income disclosure or agreed percentages |
| Hybrid | The group wants income to matter while still recognizing usage | More setup, so write the rule before spending |
For an income-based split, each person's share is their agreed income divided by the total agreed income. Use the same period and measure for everyone, such as monthly gross income or annual take-home income.
The boring part is the agreement. Write down what the income figure means. Otherwise, the formula keeps changing while everyone insists the spreadsheet is wrong.
Create the four tabs
Create tabs named People, Transactions, Settlements, and Dashboard. Keep the names unchanged unless you also update the formulas.
| Tab | What it stores | Main columns |
|---|---|---|
People |
Names, income basis, and percentages | Name, income basis, share |
Transactions |
Each gas purchase | Date, cost, payer, receipt |
Settlements |
Reimbursement requests and completed payments | From, to, amount, status |
Dashboard |
Amount owed, paid upfront, and open balance | Person, share, net position |
Keep names spelled exactly the same on every tab. A stray space in a name can make a SUMIF result look wrong.
Add the People tab
Use one income period for the whole group. Here is a sample roster:
| Name | Monthly income basis | Share |
|---|---|---|
| Alex | $6,000 | 60% |
| Jordan | $2,000 | 20% |
| Taylor | $2,000 | 20% |
Set up row 1 with Name, Income basis, and Share. In C2, enter:
=IFERROR(B2/SUM($B$2:$B$5),0)
Copy the formula down for each person, then format column C as a percentage. The range above allows four people. Extend it when your group is larger.
If people don't want exact incomes in a shared file, enter agreed percentages in column C instead. Check that the percentages add up to 100 percent. Don't mix typed percentages and formula percentages without labeling which rows use which method.
Log each fill-up once
The Transactions tab should describe the purchase, not every person's debt. Use these headers:
| Column | Header | What to enter |
|---|---|---|
| A | Date | Date of the purchase |
| B | Description | Gas station, trip name, or short note |
| C | Odometer Start | Reading before the fill-up |
| D | Odometer End | Reading after the fill-up |
| E | Total Gas Cost | Full amount charged |
| F | Payer | Person who paid at the pump |
| G | Receipt Link | Link to a photo or scan |
| H | Split Note | For example, All members |
Odometer readings add useful context, especially on a road trip. They don't decide the split by themselves. Your written rule still controls.
Enter the full cost in column E. Do not enter only the amount other people owe. The dashboard needs the full expense to calculate each person's share.
Build the dashboard formulas
The simple dashboard assumes every listed person shares every transaction. Add these headers in row 1:
Person, Share, Share owed, Paid upfront, Net before settlement, Paid out, Received, Open balance
Enter the following formulas in row 2 and copy them down:
| Dashboard column | Formula in row 2 | What it shows |
|---|---|---|
| A | =People!A2 |
Person's name |
| B | =People!C2 |
Income-based share |
| C | =SUM(Transactions!$E$2:$E$100)*B2 |
Person's share of all logged gas |
| D | =SUMIF(Transactions!$F$2:$F$100,A2,Transactions!$E$2:$E$100) |
Amount that person paid upfront |
| E | =D2-C2 |
Position before reimbursements |
| F | =SUMIFS(Settlements!$D$2:$D$100,Settlements!$B$2:$B$100,A2,Settlements!$E$2:$E$100,"Paid") |
Amount the person has paid out |
| G | =SUMIFS(Settlements!$D$2:$D$100,Settlements!$C$2:$C$100,A2,Settlements!$E$2:$E$100,"Paid") |
Amount the person has received |
| H | =E2+F2-G2 |
Balance still open |
A positive value in Open balance means the person should still receive money. A negative value means the person still owes money.
The Paid status belongs on the Settlements tab, not on the gas purchase. Several people can reimburse one payer at different times, so one status on the original transaction isn't enough.
Record settlements separately
Use these columns on the Settlements tab:
| Column | Header | Example |
|---|---|---|
| A | Date | 2026-07-21 |
| B | From | Jordan |
| C | To | Alex |
| D | Amount | $12.00 |
| E | Status | Unpaid, Pending, or Paid |
| F | Note | July 20 fill-up |
Use data validation on column E to create a dropdown with Unpaid, Pending, and Paid. Mark a row as Paid only after the recipient confirms the money arrived.
Thing is, the spreadsheet records the obligation. It doesn't verify a transfer or replace the payment method your group chooses.
Check the math with a worked example
Suppose Alex earns $6,000 per month, while Jordan and Taylor each earn $2,000. The group's total income is $10,000, so the shares are 60 percent, 20 percent, and 20 percent.
Alex buys $60 of gas and records the fill-up, including the odometer readings and receipt link.
| Person | Share | Share of $60 | Paid upfront | Net before settlement |
|---|---|---|---|---|
| Alex | 60% | $36.00 | $60.00 | +$24.00 |
| Jordan | 20% | $12.00 | $0.00 | -$12.00 |
| Taylor | 20% | $12.00 | $0.00 | -$12.00 |
Alex still has a $36 share of the expense. Because Alex fronted $60, Alex should receive $24 after accounting for that share. Jordan sends $12, and Taylor sends $12.
That is clearer than treating the payer's own obligation as zero. The payer's share still exists; the upfront payment creates the credit.
Handle trips with changing riders
The basic dashboard has a clear boundary: it assumes everyone shares every logged purchase. For a trip where one person rides only part of the route, use an allocation tab instead.
Create one row for each person and each expense. Use these columns:
| Column | Header |
|---|---|
| A | Expense ID |
| B | Date |
| C | Payer |
| D | Total Cost |
| E | Person |
| F | Included? |
| G | Income |
| H | Included Income |
| I | Gross Share |
| J | Amount Due |
Repeat the same Expense ID, date, cost, and payer for each person connected to that expense. Enter Yes or No in Included?.
In row 2, use these formulas:
G2:=IFERROR(VLOOKUP(E2,People!$A$2:$B$5,2,FALSE),0)H2:=IF(F2="Yes",SUMIFS($G$2:$G$100,$A$2:$A$100,A2,$F$2:$F$100,"Yes"),0)I2:=IFERROR(IF(F2="Yes",D2*G2/H2,0),0)J2:=IF(F2="Yes",IF(E2=C2,0,I2),0)
Gross Share calculates the person's income-based portion among the people marked Yes. Amount Due removes the payer's own share and shows what that person owes to the payer for that expense.
Use either this allocation approach or the simpler all-member dashboard for a given set of transactions. Mixing both methods for the same purchases will double count the gas.
Add a few useful review formulas
These formulas can support a small dashboard area:
| Purpose | Formula |
|---|---|
| Total settlement requests marked Unpaid | =SUMIFS(Settlements!$D$2:$D$100,Settlements!$E$2:$E$100,"Unpaid") |
| Show fills over a chosen $50 threshold | =IFERROR(FILTER(Transactions!A2:H100,Transactions!E2:E100>50),"No fills over $50") |
| Group upfront payments by payer | =QUERY(Transactions!A1:H100,"select F, sum(E) where F is not null group by F label sum(E) 'Paid upfront'",1) |
Keep the ranges in each SUMIFS the same length. A mismatched sum range and criteria range can produce errors or misleading results.
Test the workbook before sharing it
A few checks catch most setup problems:
| Check | Formula | Expected result |
|---|---|---|
| Shares total | =SUM(People!$C$2:$C$5) |
1, or 100% |
| Total gas | =SUM(Transactions!$E$2:$E$100) |
Matches the receipts entered |
| Net positions | =SUM(Dashboard!$E$2:$E$5) |
0 before settlements |
| Open balances | =SUM(Dashboard!$H$2:$H$5) |
0 when entries and settlements balance |
Format gas costs, settlement amounts, and income as currency. Format shares as percentages. Then enter one real receipt and one sample settlement before backfilling the rest.
Share the file with the right access
Give Editor access to the people who will enter receipts or settlements. People who only need to check balances can use Viewer access.
Exact income figures may be visible to anyone with access to the file. A hidden tab isn't a privacy control. If that matters, keep income figures in a private file and place only the agreed shares in the shared workbook.
After a trip ends, review the access list and remove people who no longer need the file. Keep the receipt links and settlement notes together so the record remains understandable later.
Send clear reimbursement requests
A good request names the expense, amount, and reason. For example:
Gas from the July 20 fill-up was $60. Your agreed share is $12. I paid upfront. Please mark the $12 settlement Paid after sending it.
Review the sheet after each fill-up during a trip. Roommates may prefer a weekly or monthly routine. Either way, settle from the Open balance column rather than guessing from the last receipt.
Common mistakes to avoid
| Mistake | Better approach |
|---|---|
| Mixing monthly and annual income figures | Use the same period for every person |
| Comparing gross income with take-home income | Agree on one definition first |
| Marking the purchase Paid after one person pays | Track each reimbursement on Settlements |
| Giving the payer a zero obligation | Calculate the payer's share, then subtract it from the upfront payment |
| Including a person who didn't ride | Use the allocation layout or choose a usage-based rule |
| Leaving out the receipt link | Link a photo or scan while the purchase is fresh |
Using different range lengths in SUMIFS |
Keep sum and criteria ranges aligned |
| Rounding every share too early | Keep formulas precise and document who handles a one-cent remainder |
FAQ
How do I calculate an income-based gas share?
Divide each person's agreed income basis by the total income basis. Multiply that percentage by the full gas cost. The formula is person income / total income, followed by total cost * share.
Should I use gross income or take-home income?
Either can work if the group agrees and everyone uses the same basis. Label the column clearly so the rule doesn't change from one review to the next.
Is income-based splitting always fair?
No single rule fits every trip. Income-based splitting reflects ability to pay, while usage-based splitting reflects who rode and for how far. A group can choose equal, usage-based, income-based, or hybrid rules.
Can I use this for roommates and road trips?
Yes, the same structure works for both. The simple dashboard suits a stable group where everyone shares each transaction. Use the allocation tab when participants vary.
Does this spreadsheet determine whether a reimbursement is taxable?
No. It records an agreed allocation and payment history; it doesn't classify the payment for tax purposes. Employer, business, rental, and organization arrangements can have separate rules. IRS Publication 15-B discusses employer fringe benefits, but it isn't a blanket rule for ordinary roommate gas reimbursements.
Start with one receipt
Create the four tabs, add your actual group members, and enter one recent gas receipt. Compare the dashboard with the receipt, add one settlement row, and fix the split rule before you import the rest of the trip.