Creating the General Ledger II

Introduction

Most of our customers need to prepare financial statements for the investment activities and for this they typically use some of the popular cloud based accounting systems (e.g. Quickbooks, Xero, Sage etc.) While these systems are very good for normal business activities like invoicing, expenses, payroll, depreciation of fixed assets etc., they usually do not do a very good job of investment accounting.

Of particular problem are journal entries for valuation and sale/purchase of financial assets e.g. things like

• Realized gains on sale including partial sale of a financial asset
• Splitting of realized gains into those due to price movement and those due to Foreign Exchange movement,
• Unrealized gains on balance sheet closing dates
• Accrued but unpaid interest on balance sheet dates etc.

Usually these entries are calculated manually (or some sort of utility is created). Canopy solves this problem for our customers by generating an appropriate chart of accounts and the general ledger. This can then be uploaded directly into the accounting software (via CSV or API)

Setup

Canopy Financial Statement Module (CFSM) will create the following tables

• Chart of Accounts
• General Ledger
• Balance Sheet (optional)
• Profit and Loss (optional)

Chart of Accounts

These are the backbone of CSFM. CSFM will automatically create a complete set of accounts as follows:

A sample chart of accounts is here

Sample Journal Entries for Purchase and Sale of Stock in Foreign Currency

Journal entries are created by going through the trade blotter (called Single Line Transactions in 'Canopy Speak') in chronological order from the previous account closing date to the next one. Let's take the following transactions for NTT (a JPY denominated stock). These are for the canopy_demo account (where the base currency is USD).

Transactions Done

The transactions in the trade blotter (i.e. Single Line Transactions) are as follows

As we can see this stock was purchased on 31 Dec 2013 and then sold in 2 different lots and also earned a dividend. The market prices and fxrates moved for each transaction.

Journal Entries for Purchase of Stock in Foreign Currency

We record the purchase as

(a) Credit the Cash Account with the equivalent amount in base currency i.e. 3100 5660 0.009497 = 166,634.36 .. please remember that all debits and credits are ALWAYS in the accounting currency (i.e. the base currency)

(b) We also record the actual amount in contract currency (i.e. 3100 * 5660 = 17,546,000) for reconciliation purposes in column cash_quantity

(c) Debit Equity (being the investment group dictated by "user_asset_class") for the same amount i,e 166,634.36

(d) We also track the outstanding amount of each purchase in a separate column called security_quantity_outstanding

The journal entries look as follows

Journal Entries for Sale of Stock in Foreign Currency (Lot 1)

Even though the sale price of 5648.953 was lower than our purchase price of 5660, a small gain was made on this sale due to a favourable fx rate movement (from 0.009497 to 0.009837). Even though the total sale consideration was USD 88,910 we need to split it up into a reversal of the original purchase and record the difference as a trading gain. The entries are as follows

(a) Credit Equity with a proportional amount i.e. 166,634.36 x (1,600 / 3,100) = 86,004.83

(b) Debit the Cash Account with the same amount 86,004.83

TradingGainLoss is calculated from the balance amount i.e. 88,910 - 86,004.83 = 2,905.17

Credit(+)/Debit(–) the gain from difference between sale price and average purchase price i.e. Transaction Quantity X (sale price – average purchase price) X (local ccy fx rate / base ccy fx rate) or 1,600 X (5,648.953 – 5,660) X (0.009837 / 1) = –173.87 (Debit)

(c) Debit the PriceTradingGainLoss Account with the amount calculated above i.e. 173.87

(d) Credit the Cash Account for the same amount

(e) Credit the balance amount i.e. 2,905.17 – (–173.87) = 3,079.04 to FxTradingGainLoss

(f) Debit the Cash Account for the same amount

(g) Update the security_quantity_outstanding to 3,100 - 1,600 = 1,500

Journal Entries for Sale of Stock in Foreign Currency (Lot 2)

We now sold the remaining quantity of stock at a different price and exchange rate. The accounting entries are similar to the ones above. We also update the security outstanding quantity (which is now zero). Look at the last three rows for the Sell entries:

Journal Entries for Receipt of Dividend in Foreign Currency

(a) Credit Distribution for the equivalent amount in base currency (i.e. 236,271 x 0.009862 = 2,330.10)

(b) Debit the Cash account for the same amount

Conclusion

At the end of this exercise we have

(a) All credits to Equity (i.e. USD 166,634.36) equal all debit to Equity (USD 86,004.83 + USD 80,629.53 = USD 166,634.36) thereby squaring off the entries.

(b) We have a net FX gain from sale of (–USD 173.87) + USD 2,776.23 = USD 2602.36

(c) We also have trading gains from price changes of USD 3,079.04 + USD 4,861.89 = USD 7,940.93

(d) Finally, we have dividend income of USD 2,330.10

Journal Entries for a Spot Fx Transaction

Let us look at a scenario where the customer bought USD 1mm against JPY on 24 Mar 2018 at a spot rate of 104.72. The market moved in favour of the customer who then took profit on the trade on 19 Jul 2018 at a spot rate of 112.79.

Background

The trade blotter entries (i.e. in the Single Line Transactions table):

Purchase of USD against a Foreign Currency (SpotFx Purchase)

We use an account called SpotFxTrade to record all the gains and losses from Fx translation. On 24 Mar 2018 the closing as per Bloomberg was a little higher at 104.7450 (i.e. 0.0250 JPY per 1 USD higher than the transacted rate of 104.72). This will mean a small gain for the customer on their purchase of USD at a rate of 104..72. This gain of 0.0250 JPY per 1 USD is approx USD 238.67 (because 1,000,000 x 0.0250 = 25000 JPY, which is equivalent to 25,000 / 107.7450 = USD 238.67). We should see this gain in the booking. The journal entries are as follows

(a) USD leg: Debit USD cash account and credit SpotFxTrade (both legs for USD 1,000,000)

(b) JPY leg: Credit JPY cash account and credit SpotFXTrade. This leg translates into 104,720,000 / 104.7450 = 999,761.33 for each leg

This means that SpotFxTrade sees a credit of 1,000,000 and a debit of 999,761.84 = gain of USD 238.67 (as expected)

Sale of USD against a Foreign Currency (SpotFx Sale)

Now let us look at the take profit transaction where the customer bought back JPY 104,720,000 against USD at a rate of 112.79 (i.e. USD 928,451). The USDJPY SpotFX rate on that day closed at 112.8668. The entries are as follows:

(a) USD leg: Credit USD cash account and debit SpotFxTrade (both legs for USD 928,451.10)

(b) JPY leg: Debit JPY cash account and credit SpotFxTrade (both legs for JPY 104,720,000 divided by closing rate of 112.8668 = USD 927,819.34)

This results in a small translation loss of 927,819.34 - 928,451.10 = - USD 631.76

Conclusion

(a) Translation Gains: At inception the customer had a translation gain of USD 238.67 and on closing had a translation loss of (USD 631.76) for a total translation loss of (-USD 393.60)

(b) Trading Gains: The customers JPY closing balance is back to zero but the USD cash balance have increased by USD 1,000,000 - USD 928,451.10 = USD 71,548.90. This gain will result in an increase in the networth of the account.

Journal Entries for Derivatives

Introduction

Derivatives can be tricky for the following reason

(a) They are easy to short (and most positions are initiated via a short sale)

(b) Bank Reporting on derivatives is usually sketchy and expiring derivatives often just 'disappear' from the following months closing statements. These are then booked as Transfer Out (or Transfer In if the original position was a short sale) in Canopy and need special treatment.

The following paragraphs summarizes our derivative bookings.

New Positions or Adding to Existing Positions (i.e. same direction as existing trades)

Unwind of Existing Positions (full or partial unwind)

T-Account Example for a Call Option. View Excel file here.

Summary of Journal Entries for all Trade Types

Journal Entries depending on the Trade Types are given below: