How To Reconcile Your PayPal Accounts

by Auditor of a Small Non-Profit

Best Practices For Your PayPal Accounts

Best Practices For Your PayPal Accounts

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

section divider

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.

Comments for How To Reconcile Your PayPal Accounts

Click here to add your own comments

Jul 04, 2017
How to record USD Paypal Deposits & Expenses
by: Marie

I was thrilled come across your website during a Google search the other day! I've gone through all of your tutorials re: USD and Paypal transactions.

I am an Alberta blogger who receives monthly affiliate income in USD deposits via Paypal. Normally I try to keep my Paypal balance at zero by transferring the CAD conversion on these deposits into my bank account (according to receipt provided by Paypal). This makes it easy to reconcile the bank account.

However, life got hectic and I wasn't on top of transferring the deposits right away to maintain that zero balance. In the meantime, USD expenses were credited towards that Paypal Balance now I'm not sure how to properly record that USD Income and the associated CAD transfers to my bank account (which don't align with the total sales income) in order to reconcile my bank account? I hope this makes sense because my head hurts from trying to figure this out!

If I could afford to hire you to be my virtual bookkeeper, I'd do it in a heartbeat. One day as my blog continues to grow (finger's crossed).

Thank you :)

Aug 06, 2017
by: Lake

Sorry, I'm not understanding your problem. If you are using QBO, are NOT using the PayPal bank fee and have a bank account for each PayPal currency, it is very easy to reconcile each one.

As explained in the above post:

1. Enter in your foreign affiliate income (by invoice or sales receipt) by currency to the PayPal bank account that matches your foreign currency.

2. Enter in every expense you made using "Expense" window and booking it in the appropriate foreign currency (means you have to have setup the supplier in the appropriate currency) and book it to the PayPal bank account that matches the currency.

3. Record any transfers of currency between your home currency and your foreign currency.

Once you've done these three things, everything should reconcile easily. The only way I can think you are running into trouble is if you are pulling in data from your PayPal bank feed in QBO. The PayPal feed does not recognize currencies and dumps everything into one account. See my article - QBO-PayPal Data Sync for more on this:

If you are purchasing or selling in USD, I don't recommend transferring your USD revenue to your CAD account unless you need to take an owner's draw. Leave the money in the account to offset your purchases ... or move some of your balance to a USD savings account to get interest but still leave a small balance in PayPal USD to cover your expenses. It will save you on the currency exchange fees.

I know everyone is into apps and automating data entry but it is my opinion that you don't need all your PayPal data cluttering up your QBO database if most of your sales are one time customers. See my chat on eCommerce workflow tips:

Hope that helps. If I didn't understand your problem question, please post back.

Mar 07, 2018
Reconciling PayPal
by: Anonymous

when I look at the gross receipts in paypal 1099K they do not add up to all the funds received to the account.

1) I only work on the completed transactions.

2)I have a total of 13 different labels for transactions that I am trying to follow. payment reversed versus refunded

What is not considered in the reported 1099K amount? Do you know if they don't count reversals etc?

I contacted PayPal but they cant explain it other than "all gross receipts are recorded and included" and that is obviously not as simple as it sounds!

Apr 04, 2018
Multi Currency
by: Tammy

Do you turn on multi currency when you do this? I'm using QBO so could be slightly different than desktop.

Apr 07, 2018
Multi-currency in QBO
by: Lake

It depends if you want QBO to handle the multi-currency or if you want to track it manually.

Just remember once you turn multi-currency on, you can't turn it off.

Click here to add your own comments

Return to Self-Employed Bookkeepers.


Back to top