Google Sheets can give a roommate group one shared record of PayPal security deposits without adding another payment system. Keep the original export separate, use positive amounts for payments and negative amounts for refunds, and let formulas handle repeated totals.
That works for roommates moving out, friends holding a vacation rental, or a small group paying a venue. The sheet records payment activity; it does not decide what a landlord, host, or organizer legally owes. Lease terms and local rules can change that answer.
Start with a three-tab workbook
Use three tabs from the start. That small separation keeps your source file intact when someone needs to correct a label or add a receipt.
| Tab | Job | Working rule |
|---|---|---|
| Raw PayPal Export | Holds the downloaded CSV | Do not edit or overwrite it |
| Deposit Ledger | Contains cleaned, deposit-related rows | Use consistent labels and one row per money movement |
| Summary | Shows totals, filters, and review flags | Keep formulas away from imported values |
Keep unrelated PayPal activity out of the shared ledger. If the export includes personal transactions, share a separate summary or a cleaned copy instead of assuming a hidden tab protects private information.
Choose columns and a sign convention
Put headers in row 1. The layout below gives you a stable A:J structure for formulas and future imports.
| Column | Field | What to record |
|---|---|---|
| A | Date | The transaction date as a real date value |
| B | Payer | The person or group sending the money |
| C | Payee | The landlord, host, venue, or recipient |
| D | Amount | Positive for money paid in; negative for money refunded |
| E | Deposit Type | Use labels such as Security Deposit, Travel Rental, Venue, or Other |
| F | Status | Paid, Refunded, Pending, or Disputed |
| G | PayPal Transaction ID | The identifier shown in the PayPal export |
| H | Notes | Purpose, deduction explanation, or follow-up needed |
| I | Receipt Link | A restricted Google Drive or shared-folder link |
| J | Split Owed | The person's agreed share, not a duplicate payment |
Use the same spelling for every deposit type and status. Formulas rely on those labels.
One row should represent one money movement. For a partial refund, keep the original positive payment and add a separate negative refund row rather than rewriting the first transaction. That preserves the history.
PayPal exports may show gross, fee, and net fields. If fees affect what the group reimburses, add K Fee and L Net Amount; keep the gross signed amount in column D. Decide whether your group tracks the payer's gross payment or the recipient's net receipt, then apply that rule consistently.
Import the PayPal CSV without losing the original
PayPal's available export views and labels can vary. A third-party PayPal export walkthrough from Tiller describes the general process of choosing a date range and file format, but check the fields in your own download.
- Open PayPal's activity or transaction history area and use the available download or export control. Select the date range that covers the deposit activity and choose CSV if it is available.
- Create the three tabs above. In Google Sheets, use
File > Import > Uploadto bring the CSV into the Raw PayPal Export tab, or choose an import option that leaves your existing ledger untouched. - Inspect the raw file before copying anything. Remove extra title rows, summary rows, and blank lines from a working copy. Do not delete them from the original download.
- Map the export fields to your ledger. A source field such as Gross may belong in Amount, while a fee or net field belongs in its own column. Preserve the transaction ID exactly as displayed.
- Copy cleaned values into Deposit Ledger beginning in row 2. Use
Paste special > Values onlyso imported text does not replace formulas or formatting. - Convert amounts to numbers and confirm several dates manually. If the sheet interprets a date such as
01/02differently from PayPal, correct the date before calculating totals. - Apply your labels for Deposit Type and Status. If the export does not make a transaction's status clear, use Pending and explain the uncertainty in Notes.
- Compare a few ledger rows with the original PayPal activity. Sort or filter by transaction ID, then remove any manual row that duplicates an imported movement.
Google Sheets imports data; it does not know which PayPal rows are security deposits. That classification still belongs to the person maintaining the ledger.
Put the summary formulas on a separate tab
Assume the cleaned tab is named Deposit Ledger, headers are in row 1, and the data uses columns A:J. Put these formulas on Summary so later imports do not overwrite them.
| Task | Formula | Result |
|---|---|---|
| Show security deposit rows | =IFERROR(FILTER('Deposit Ledger'!A2:J,'Deposit Ledger'!E2:E="Security Deposit"),"No matching rows") |
A deposit-only view |
| Total paid security deposits | =SUMIFS('Deposit Ledger'!$D$2:$D,'Deposit Ledger'!$F$2:$F,"Paid",'Deposit Ledger'!$E$2:$E,"Security Deposit") |
Positive completed payments |
| Total refunds as a positive figure | =-SUMIFS('Deposit Ledger'!$D$2:$D,'Deposit Ledger'!$F$2:$F,"Refunded",'Deposit Ledger'!$E$2:$E,"Security Deposit") |
The magnitude of refunds |
| Net completed movement | =SUMIFS('Deposit Ledger'!$D$2:$D,'Deposit Ledger'!$F$2:$F,"Paid",'Deposit Ledger'!$E$2:$E,"Security Deposit")+SUMIFS('Deposit Ledger'!$D$2:$D,'Deposit Ledger'!$F$2:$F,"Refunded",'Deposit Ledger'!$E$2:$E,"Security Deposit") |
Payments less refunds |
| Group totals by deposit type | =QUERY('Deposit Ledger'!A1:J,"select E, sum(D) where E is not null group by E label sum(D) 'Net amount'",1) |
A net total for each type |
| Find paid deposits over $500 | =IFERROR(FILTER('Deposit Ledger'!A2:J,'Deposit Ledger'!E2:E="Security Deposit",'Deposit Ledger'!F2:F="Paid",'Deposit Ledger'!D2:D>500),"No rows over threshold") |
A review list for larger payments |
The refund formula assumes refund rows use negative amounts. Change Security Deposit if you selected another exact label. The $500 threshold is only a review choice, not a rule about what counts as a security deposit.
Add Import Check in K1 if you want a duplicate warning. Put this in K2 and fill it down:
=IF(G2="","",IF(COUNTIF($G$2:G2,G2)>1,"Duplicate ID","OK"))
For an exact expected refund amount stored in N2, you can use conditional formatting with this review rule:
=AND(ISNUMBER($N$2),$F2="Refunded",ABS($D2)<>$N$2)
Use that rule only when an exact refund is expected. Cleaning deductions, fees, or partial refunds may make a different amount legitimate.
Reconcile the ledger before sharing
Check every security deposit row against the original PayPal export. Compare the transaction ID, date, payer or payee, signed amount, and status.
Turns out, a correct formula can still produce a wrong answer if one refund is entered as positive. Test the sheet with a sample payment and a sample negative refund before relying on the totals.
Review fees separately. If the payer sent one amount but the recipient received less, record both figures and explain the difference in Notes. Do not silently replace the payment amount with the net figure.
Review the ledger after each active transaction or once a week during a move, trip, or event. For an occasional move-out, review it before requesting reimbursements and before closing the shared record.
A spreadsheet is a payment record, not a ruling on a landlord dispute. Keep the lease or written deposit terms, move-in and move-out photos, deduction notices, receipts, and PayPal records together. Security deposit requirements vary by state, locality, and agreement.
Share carefully and protect formulas
Give most group members Viewer access when they only need to check balances. Use Commenter access for people who need to ask about a row, and reserve Editor access for the person who imports data and maintains formulas. Layer's permissions overview explains how those Google Sheets roles differ.
Protect the summary formulas and header ranges through Data > Protect sheets and ranges. A Google Sheets protection walkthrough from Sheets Bootcamp shows the basic range-selection process. Protection helps prevent accidental edits, but it is not a substitute for limiting who can open the file.
Receipt links can expose addresses, names, or other private details. Store them in a folder with matching access, and never put card numbers, passwords, or bank login details in the sheet.
Common questions
How should PayPal fees appear in the tracker?
Keep fees in their own column when they affect the amount available for reimbursement. Use Amount for the signed transaction value, then explain whether your group uses gross paid or net received for settling shares.
Can this track travel rental or venue deposits?
Yes. Keep the same columns and change Deposit Type to Travel Rental or Venue. The formulas work as long as the label in the formula matches the label in the ledger exactly.
What if the PayPal export uses different status names?
Map the source status to your agreed labels and preserve the original wording in Notes when it matters. Do not mark a transaction Paid merely because it appears in the file if the payment is still pending or disputed.
Is this enough documentation for a landlord disagreement?
It helps show payment history, but it does not establish every party's legal rights. Keep the underlying agreement and supporting records, then check official local guidance if a dispute develops.
Create the three tabs and enter one sample payment plus one sample refund. Confirm that the paid, refunded, and net formulas behave as expected before importing real PayPal activity and sharing the workbook.