Ledgers for Payment Service Providers

In the previous issues, I've introduced you to double-entry accounting. Now we have enough basic knowledge about ledger accounts,  sub-ledgers, and external ledgers, to dive deeper into an example. I'll journalize a Payment Service Provider (PSP) in this issue.

Disclaimer: I've never worked for a PSP, nor have I complete knowledge of all the inner workings of such a business. All my knowledge comes from years of being a PSP customer and seeing accounting errors from the outside.

A PSP business

From a business perspective, the PSP is all about moving money. So building systems to validate that all cash is transferred correctly seems like a no-brainer in that case. And a lot of money is probably moved correctly in these businesses. Still, I've seen first-hand how PSPs can create and lose money.

Ledgers in a PSP are somewhat interesting. Most PSPs have two business entities: the primary entity where the customers have a contract. And a second entity that only handles money from customers. The primary entity never owns money from clients; therefore, PSPs must separate this into a second entity.

The primary entity has a ledger comparable with other businesses. It contains journal entries for the primary entity. The second entity has one ledger and needs to manage sub-ledgers for all customers. These sub-ledgers are external ledgers in the bookkeeping of these customers.

A balanced payment

Let's start with the essential financial fact for a PSP: a payment. When the payment is executed, there is probably no direct money movement. Payment methods will have daily settlements, so the PSP receives a bulk of the money at the end of the day. The payment is merely a change in assets and liabilities:

╭─────────────────────────────────────────────────────────────────────────┬──────────┬───────┬────────╮
│ Ledger account                                                          │ Relation │ Debit │ Credit │
├─────────────────────────────────────────────────────────────────────────┼──────────┼───────┼────────┤
│ balance_sheet.current_assets.receivable_from_payment_methods.creditcard │          │ 100.0 │    0.0 │
│ balance_sheet.current_liabilities.payable_to_customers                  │ c9013    │   0.0 │   99.0 │
│ balance_sheet.current_liabilities.payable_to_main_entity                │          │   0.0 │    1.0 │
├─────────────────────────────────────────────────────────────────────────┼──────────┼───────┼────────┤
│                                                                         │          │ 100.0 │  100.0 │
╰─────────────────────────────────────────────────────────────────────────┴──────────┴───────┴────────╯

I'd probably use a ledger account for each payment method, so you can precisely track amounts for each payment method. When you have many payment methods, a sub-ledger can become helpful.

For the amounts payable to customers, we include a relation reference. The reference allows us to provide a sub-ledger for each customer.

Finally, we want to charge a payment fee for the transaction. We can subtract this directly from the payable_to_customers amount. The primary entity of the PSP should receive this money as a fee for their services, so we have another liability there.

Improved sub-ledger

We need to keep in mind that our customers want to import the sub-ledger into their bookkeeping. Ideally, we provide them with as much detail as possible. Therefore, having a single transaction of $99 on the sub-ledger is not ideal. When the customer needs to reconcile this amount, he will miss $1.

You can improve the transactions by adding an extra journal entry. This additional entry doesn't change the end-of-day balance of the ledger accounts. However, for the sub-ledger, it makes all the difference to know where the $1 went:

╭─────────────────────────────────────────────────────────────────────────┬──────────┬───────┬────────╮
│ Ledger account                                                          │ Relation │ Debit │ Credit │
├─────────────────────────────────────────────────────────────────────────┼──────────┼───────┼────────┤
│ balance_sheet.current_assets.receivable_from_payment_methods.creditcard │          │ 100.0 │    0.0 │
│ balance_sheet.current_liabilities.payable_to_customers                  │ f87ae    │   0.0 │  100.0 │
│ balance_sheet.current_liabilities.payable_to_customers                  │ f87ae    │   1.0 │    0.0 │
│ balance_sheet.current_liabilities.payable_to_main_entity                │          │   0.0 │    1.0 │
├─────────────────────────────────────────────────────────────────────────┼──────────┼───────┼────────┤
│                                                                         │          │ 101.0 │  101.0 │
╰─────────────────────────────────────────────────────────────────────────┴──────────┴───────┴────────╯

When you add a description to the journal entries, exposing the sub-ledger to the customers is now as easy as a simple query over the journal entries:

SELECT date, description, debit, credit
  FROM journal_entries
  WHERE ledger_account='payable_to_customers' AND relation='...';

The payout financial fact

The next step is to settle the payable amounts to the customer. The frequency depends on the PSP. You know the amount of the payout by querying the ledger:

SELECT SUM(credit-debit) FROM journal_entries
  WHERE ledger_account='payable_to_customers' AND relation='...';

After the payout, you enter journal entries to make sure the user's balance is updated. The payout and journal entries require extra attention in the software because this needs to happen inside a transaction to prevent double payouts.

╭────────────────────────────────────────────────────────┬──────────┬───────┬────────╮
│ Ledger account                                         │ Relation │ Debit │ Credit │
├────────────────────────────────────────────────────────┼──────────┼───────┼────────┤
│ balance_sheet.current_assets.bank_accounts             │          │   0.0 │   99.0 │
│ balance_sheet.current_liabilities.payable_to_customers │ f87ae    │  99.0 │    0.0 │
├────────────────────────────────────────────────────────┼──────────┼───────┼────────┤
│                                                        │          │  99.0 │   99.0 │
╰────────────────────────────────────────────────────────┴──────────┴───────┴────────╯

Settlements from payment methods

With the previous payout, the balance of our bank account would have become negative. So hopefully, we will first receive a settlement from a payment method.

╭─────────────────────────────────────────────────────────────────────────┬───────┬────────╮
│ Ledger account                                                          │ Debit │ Credit │
├─────────────────────────────────────────────────────────────────────────┼───────┼────────┤
│ balance_sheet.current_assets.bank_accounts                              │ 100.0 │    0.0 │
│ balance_sheet.current_assets.receivable_from_payment_methods.creditcard │   0.0 │  100.0 │
├─────────────────────────────────────────────────────────────────────────┼───────┼────────┤
│                                                                         │ 100.0 │  100.0 │
╰─────────────────────────────────────────────────────────────────────────┴───────┴────────╯

I'd implement this as an external ledger like with regular bank accounts. The payment method hopefully provides you with a statement of all settled transactions. To ensure the payment method settles the correct amounts, you can now reconcile these with the transactions you expect.

Ledger of the primary entity

In the primary entity, the ledger is much simpler. Once in a while, we receive money from the second entity for transactions fees. Periodically we need to send invoices for these fees. Because the fees are already withheld from the payouts, we don't need to handle payments or keep a accounts receivable ledger.

╭────────────────────────────────────────────┬───────┬────────╮
│ Ledger account                             │ Debit │ Credit │
├────────────────────────────────────────────┼───────┼────────┤
│ balance_sheet.current_assets.bank_accounts │   1.0 │    0.0 │
│ profit_loss.revenue.transaction_fees       │   0.0 │    1.0 │
├────────────────────────────────────────────┼───────┼────────┤
│                                            │   1.0 │    1.0 │
╰────────────────────────────────────────────┴───────┴────────╯

Conclusion

So there you have it, a journal entry implementation for a Payment Service Provider. The two entities make it a bit complex, but it stays relatively simple even then. I probably overlooked some details, but the financial facts of the major business activities are covered. You have also seen how important the choices in the internal ledger can be for the usability of the sub-ledger as an external ledger.

Next week we'll dive deeper into the design of our journal entries table by discussing another topic: custom dimensions. Subscribe to be notified when the new issue launches.