How To Reconcile Your PayPal Accounts

by Auditor of a Small Non-Profit

What is the best way to keep track of a "PayPal" account?

I'm not conversant in audit procedures but what I can do is explain how I setup and reconcile PayPal accounts in QuickBooks. Hopefully it will be helpful to you. I'm sure there are other ways to setup but I have found this works best for me.

1. I setup a separate "bank" type account for each PayPal currency. I don't merge it all into one PayPal account.

2. My preference is to not download PayPal transactions. Instead I download the PayPal data for the period I am working on in Excel format. I massage the data (do multiple sorts) to break the information down to sales, affiliate payments received, transfers and purchases.

3. I book a summary invoice (daily, weekly, monthly ... whatever is most appropriate for your organization) of any sales received. One sales invoice for each currency. My source document(s) for this entry is usually the shopping cart reports/data. The only time PayPal would be my source for this step is if the sales are not captured by a shopping cart .. i.e PayPal is the only place to get the sales information for a particular product. Because we have sales tax to account for in Canada, one of "my sorts" is by country so that I can capture Canadian sales and the associated sales tax by province/territory. Foreign sales normally attract zero sales tax so I like to capture this information in a separate invoice. It is important to note that Canadian sales can be in a foreign currency. For example, the site may sell their product to Canadians in USD.

4. I "receive payment" as a lump sum payment (daily, weekly, monthly, etc. -- from the PayPal Excel file data) to the invoice(s) booked in step 3. If there is more than one currency, there will be more than one payment to book ... i.e. one payment for each currency invoice for the period being booked. I totally ignore PayPal fees attached to individual transactions at this point. If there are no timing differences due to "in transit" items between the shopping cart and PayPal, each invoice should show paid once you complete this step. I record separate "receive payments" for "in transit" items to facilitate the bank reconciliation.

5. I "receive payment" on any affiliate payments ... again my data source for this is the PayPal Excel file. Affiliate invoices would have been booked in the prior month.

6. I deposit the payments received into the PayPal bank account using "Make Deposits". Having booked "in transit" items separately, as discussed above, allows me to deposit the "in transit" payment on the date PayPal received it (i.e. not the date the shopping cart sent it.)

7. I download a pdf of the PayPal Monthly Financial Statement - Summary and Detail FOR EACH CURRENCY.

8. I formally reconcile each PayPal account utilizing the statements downloaded in the previous step to obtain my opening and closing balances. In my reconciliation window, I enter all the PayPal fees paid in the month into the merchant service charges line. Again I obtain this information from the Monthly Statement and cross check that my Excel download file agrees to the amount.

Potential Problem Areas You May Want To Audit

These are the most common things I have found that can make a monthly reconciliaton difficult:

1. Where things can get mucked up is when a credit card is dinged for only part of the purchase price. I manually book the events pertaining to this transaction.

2. Sometimes things can go awry in the shopping cart. When this happens, the payment is sitting in PayPal with "no sale" in the shopping cart. When this happens, usually the shopping cart sale was booked manually (or with some kind of work around) by the site owner. I normally confirm these "odd" transactions with the site owner to get the " background story" on the problem transaction.

3. Refunds can create a problem when the purchase is made in one month and refunded in the following month.

4. If someone pays by eCheck, there could be timing issues regarding the physical receipt of the funds.

5. You may want to check that the flow through of credit card payments to PayPal has been properly reflected in the books.


This is just a quick and dirty summary of how I approach PayPal data entry and reconciliation. I hope it helps and would be very interested to see how other bookkeepers handle these types of transactions.

P.S. I forgot to mention that all expenses purchased through PayPal would normally have been captured when entered the data is entered from the receipt. I generally don't book these in a lump sum entry to ensure I can easily lookup vendor information and that I have the appropriate documentation to support the expense.