Author: | Martin Blais <blais@furius.ca> |
---|
Company expenses... should I categorize on the personal side, or on the company side?
In march 2008, at 35 years of age, I discovered double-entry accounting. Call me stupid, but before that, accounting was but an ill-defined concept in my mind, and I was not really paying attention. When I observed someone use Quicken--this person had all his data balanced within Quicken since 1997--I got curious. My friend complained about Quicken not being a double-entry accounting system, and he then mentioned that I should try GnuCash to understand the concept, and we had a discussion about this, although, I did not really understand the basic concept until I tried GnuCash. When I did, however, a massive light-bulb went on in my brain.
I had understood the beautiful basic principle of double-entry accounting: by insuring that the amounts of at least two entries within a transaction balance to zero, we insure that the entire finances of a system balances precisely. This not only insures that no money is lost untracked anywhere, it also makes it very easy to figure out inconsistencies when they occur.
Before this day, I had a number of little ad-hoc mechanisms for solving money issues that were all different and unsatisfactory. I realized that a double-entry accounting system could be saving me a lot of time, by framing all of these mechanisms into a single, elegant, closed system that would contain them all. Here are the many problems that double-entry accounting solves for me:
I would record all tax-relevant questions in text files edited by hand, which oftentimes became outdated or had errors in them. It was difficult to figure out when I last edited these files, and I would often have to refer to statements many months after the facts I was researching, where my memory of the events was way gone.
In particular, I will be able to track exactly how many taxes I paid on my paychecks, or what-not. I will be able to easily answer all relevant questions to my accountant, and the end-of-year, instead of being a stressful process riddled with uncertainty, will be a simple relaxing formality; I will just send Yves my books.
I had a little text file that I used to track items or money that I lent to friends. I was never sure if I had updated the file, so it wasn't all that useful. For example, I made two loans to my father, and I was never sure if he had paid it back in its entirety --he wans't either. I once had to go back to really old bank statements to try to figure out which entries were payments from him. I still don't know.
By having a single system, all amount tracked in the same place, I will be able to easily figure out who paid what back or what-not.
For a long time I felt the need to be able to make annotations to my bank statement, even before they complete (not having all the paper statements in hand made things worse). I even sent internal mail to the bank to suggest that users be allowed to do that.
With my system, this is now perfect: by simply editing the description line I have my own notes in the list of transactions!
Because a lot of my expenses are paid back by the company, I had developed a little system with a simple file format that I used to process manually at the end of the year to produce a list of the expenses to be paid back by the cie. I had to manicure this file on a regular basis, and be very careful at the end of the year. Now I can simply write a little query to my database!
The company is making payments to its employees. I had a little text file that I used to automate payments, and despite the fact that I trusted it, I had to always be super extra careful when I manipulated it to avoid losing amounts. This caused be a great amount of stress while editing the file (and after). Also, it was rather difficult to find out at any point in time how much I had already paid in each of the taxes.
Now, I can simply place all these amounts as entries in my simplistic accounting system!
One of the purposes of the 'flair' project that I was developing was to be able to track my net worth and its progress over time (i.e. generate a PnL report for myself, and for each of my investments as well). One problem whose representation I was stuck with was how to represent the trades and position over time; as it turns out, this was simply an accounting problem applied to my investments and trading activity!
I had already designed a really bad system, where I would manually enter the positions and cash amounts into each account... and I stopped at the representation of trades. This sucked. Now it'll just be part of a system for my entire finances!!!
After doing my taxes, Yves would tell me to expect checks of specific amounts. On more than one occasion I wondered if I had indeed received the check or not. This was stressing me out every time it happened.
Now, I can simply enter these amounts as transactions in the Accounts-Receivable and Accounts-Payable categories!!!
For my investments, I had an encrypted text file that I used to track every single investment I was making. I don't need this anymore, all I need is to add this information in my unified accounting system.
I had a special encrypted file that I used to track comments on "pending" transactions, to keep confirmation numbers and figure out later the transactions that would occur in my accounts. I can now simply use my accounting file, and reconcile it later when the statements arrive!
I'm selling books online. I used to find it error-prone and annoying at the end of every year to figure out how many sales I'd done. Now I can simply download the data from PayPal and have it converted automatically, and the balance check insures that I haven't made any mistakes.
In addition, a double-entry accounting system would allow me to do things I never could do easily before:
It turns out that a double-entry accounting system can be represented as a very simple data structure, using just a few database tables. I tried to use GnuCash, and quickly gave up: it was buggy and difficult to use. I did searches on the internet, which turned up a number of large bloated systems, until I found "Ledger", a simple program written by John Wiegley, which was using simple text files to solve the problem elegantly. I learned a lot by just playing with Ledger (I could not sleep for a few days, was so excited with the elegance of the solution).
Unfortunately, the implementation of his system had a few shortfalls, described in the next section.
Some notes about accounting theory from Yves, over a quick discussion.
DT = CT Assets = Liability + Equity Equity = Salary + Profit - Dividends Profit = Income - Expenses
therefore:
Assets = Liability + (Salary + Profit - Dividends) Assets = Liability + (Salary + (Income - Expenses) - Dividends) Assets + Expenses = Liability + Salary + Income - Dividends
The five top-level accounts are classified with numbered categories (standard). This number also serves as the ordering in the general ledger:
Assets 1000 \ Liabilities 2000 | -> Balance sheet Equity 3000 / Income 4000 \ -> PnL report Expenses 5000 /
The "trial balance" is the sum of all entries (signs adjusted).
Assuming that our accounts may contain any kinds of assets, we have a choice when we transfer assets in that account: we may choose to
Store the value of the assets, e.g.:
2008/02/27 * Purchase of loft Assets:Real-Estate 235000 CAD <- value of loft Assets:Checking 100000 CAD Liabilities:Loan 135000 CAD
Store the assets themselves:
2008/02/27 * Purchase of loft Assets:Real-Estate 1 Loft Assets:Checking 100000 CAD Liabilities:Loan 135000 CAD
In this case, the "Assets:Real-Estate" account is able to contain "Loft" units. It's a bit strange, but this is the way other investment accounts work, for example, a discount broker account:
2008/02/27 * Buy some shares Assets:Broker 12 AAPL @ 50.00 USD Assets:Broker -600.00 USD 2008/02/28 * Buy some shares Assets:Broker 3 MSFT @ 60.00 USD Assets:Broker -180.00 USD After these trades, the account balance should look like this:: 12 AAPL 3 MSFT -780.00 USD So why not with the loft? The changing "mark" price (valuation on the market) of the loft would indicate its value, instead of accounting for it in dollars.
If I want to take into account the increasing value of my loft, how do I do this?
An idea would be to make periodical transfers from a special "equity" account, e.g.:
2008/04/01 * Monthly growth in loft value. Assets:Real-Estate 400 Equity:Real-Estate-Growth -400Such adjustments could be created from monthly real-estate data growth (if I can find such a thing). The special equity account reflects how much money the growth of my asset took "out of society".
Note that if I store the property itself and use some price entries to reevaluate the home, it solves the problem pretty nicely.
It appears to me a little arbitrary that when I buy something it becomes either an Asset or an Expenses, for example, I see the following two transactions as entirely valid:
2008/04/01 * Bought a computer. Assets:Checking -1600 USD Expenses:Electronics 1600 USD 2008/04/01 * Bought a computer. Assets:Checking -1600 USD Assets:Gear 1600 USD
They are not equivalent, however; I'm afraid that this is going to make an important distinction when some of the reports will be generated. In particular, the second form will not affect the PnL, while the first form will reduce them.
So what is the rule for classifying expenses as assets or not?
What account should I pay my taxes to?
Is this good?
2008/04/01 * Paid federal taxes. Assets:Checking -1027.32 CAD Expenses:Taxes:Federal 1027.32 CAD
Is it an expense?
Here is a transaction:
#! trade-1.ledger 2007/04/02 * Buy 100 IVV @ market. Assets:Investments:HSBC-Broker 100 IVV @ 130.0023 USD Assets:Investments:HSBC-Broker -13000.23 USD 2008/02/10 * Sell 100 IVV @ market. Assets:Investments:HSBC-Broker -100 IVV @ 153.6060 USD Assets:Investments:HSBC-Broker 15360.60 USD
The book does not balance, due to the capital gain:
2360.37 USD Assets:Investments:HSBC-Broker -------------------- 2360.37 USD
We could declare the capital gains as an imbalanced transaction?
#! trade-3.ledger 2007/04/02 * Buy 100 IVV @ market. Assets:Investments:HSBC-Broker 100 IVV @ 130.0023 USD Assets:Investments:HSBC-Broker -13000.23 USD 2008/02/10 * Sell 100 IVV @ market. Assets:Investments:HSBC-Broker -100 IVV @ 153.6060 USD Assets:Investments:HSBC-Broker 15360.60 USD (Income:Capital-Gains) -2360.37 USD
It seems to work nicely... we balance! And we have an account to report the realized capital gains at the end of the year to the joy of our real personal accountants:
2007/04/02 Buy 100 IVV @ market. Assets:Investments:HSBC-Broker 100 IVV 100 IVV Assets:Investments:HSBC-Broker 13000.2300 USD 100 IVV -13000.2300 USD 2008/02/10 Sell 100 IVV @ market. Assets:Investments:HSBC-Broker -100 IVV -13000.2300 USD Assets:Investments:HSBC-Broker 15360.6000 USD 2360.3700 USD (Income:Capital-Gains) -2360.3700 USD 0
Just one problem remains: I end up having to calculate the difference manually, which sucks. Maybe I can do this instead?
#! trade-4.ledger 2007/04/02 * Buy 100 IVV @ market. Assets:Investments:HSBC-Broker 100 IVV @ 130.0023 USD Assets:Investments:HSBC-Broker -13000.23 USD 2008/02/10 * Sell 100 IVV @ market. Assets:Investments:HSBC-Broker -100 IVV @ 153.6060 USD Assets:Investments:HSBC-Broker 15360.60 USD (Income:Capital-Gains) 13000.23 USD (Income:Capital-Gains) -15360.60 USD
This works beautifully!
2007/04/02 Buy 100 IVV @ market. Assets:Investments:HSBC-Broker 100 IVV 100 IVV Assets:Investments:HSBC-Broker -13000.2300 USD 100 IVV -13000.2300 USD 2008/02/10 Sell 100 IVV @ market. Assets:Investments:HSBC-Broker -100 IVV -13000.2300 USD Assets:Investments:HSBC-Broker 15360.6000 USD 2360.3700 USD (Income:Capital-Gains) 13000.2300 USD 15360.6000 USD (Income:Capital-Gains) -15360.6000 USD 0
How about closing the books, how does it work in that context?
#! trade-closing-1.ledger 2007/04/02 * Buy 100 IVV @ market. Assets:Investments:HSBC-Broker 100 IVV @ 130.0023 USD Assets:Investments:HSBC-Broker -13000.23 USD 2007/12/31 * End of year -- close the books. Assets:Investments:HSBC-Broker -100 IVV @ 130.0023 USD Equity:Closing-Balances 13000.23 USD 2008/01/01 * Start of year -- open the books. Assets:Investments:HSBC-Broker 100 IVV @ 130.0023 USD Equity:Opening-Balances -13000.23 USD 2008/02/10 * Sell 100 IVV @ market. Assets:Investments:HSBC-Broker -100 IVV @ 153.6060 USD Assets:Investments:HSBC-Broker 15360.60 USD (Income:Capital-Gains) 13000.23 USD (Income:Capital-Gains) -15360.60 USD
An important issue is that of figuring out which lot is bought and sold. One idea would be to assign a unique trade-id with each lot traded:
2007/04/02 * Buy 100 IVV @ market. Assets:Investments:HSBC-Broker 100 IVV @ 130.0023 USD ; lot:da02edbc Assets:Investments:HSBC-Broker -13000.23 USD 2008/02/10 * Sell 100 IVV @ market. Assets:Investments:HSBC-Broker -100 IVV @ 153.6060 USD ; lot:da02edbc Assets:Investments:HSBC-Broker 15360.60 USD (Income:Capital-Gains) 13000.23 USD (Income:Capital-Gains) -15360.60 USD
It should then be possible to write a script to figure out which lot remains, in order to calculate the book-value and capital gains properly.
A: Ledger 2.6 has support for this. (See the NEWS file.) Here is how it is supposed to look like:
(17:19:11) johnw: no, you'll love real lots, it's the 3rd bullet item in NEWS (17:20:03) blais: aaaaaaaahh (17:20:05) blais: you index by price (17:20:06) blais: very clever (17:20:13) blais: that's the right way to do it actually (17:20:18) johnw: you can index by date and by a descriptive tag also (17:20:21) blais: but then, do you track if it's "legit"? (17:20:22) johnw: or all three (17:20:26) blais: awesome. (17:20:43) johnw: Ledger does not know about "legit", that's up to you to create the right reports to tell you if everything is legit or not (17:21:08) johnw: Ledger is just a fancy adding machine, it's very stupid under the hood as to what the meaning of your data is (17:21:37) johnw: it will let you bork yourself -- it just won't let you have an entry which does not balance to zero. That's pretty much it's only requirement (17:21:39) blais: No, I mean, could I select a lot that doesn't exist? (17:21:43) johnw: sure you can (17:21:50) johnw: you would then have a negative quantity transfer of that lot
If a trade is performed on 2007/12/31 and settles on 2008/01/03, which year does it get declared into?
You use the settlement date.
- 2006/02/01 * Start of year.
- Assets:Cash 100.00 USD Equity:Opening-Balances
- 2006/02/01 * Change currencies at cash booth.
- Assets:Cash -100.00 USD @ 1.4 EUR Assets:Cash
Most of the time, I spend for the company using my own accounts. I need to be able to declare within the company which kinds of expenses it is making, but the expenses occur on the personal side. Does this mean I have to create entries on both sides? Can I not simplify?
Using a subaccount for each of the categories is a little bit ugly. I don't really like those ":X" and it makes the tree larger than it would otherwise be.
Another option for tracking company expenses would be to mark transactions with a special flag or tag name, 'cie':
2008/04/01 * Bought a computer. Assets:Checking -1600 CAD Expenses:Electronics 1600 CAD ; cie
I could probably select for all of the entries marked with such a tag and generate the balance, and later on balance when with a special account for cie expenses:
2008/04/15 * Paid expenses back from cie. Assets:Checking 2000 CAD Expenses:Cie-Reimbursements -2000 CAD ; cie
Filtering all transactions over all accounts by this tag would allow be to generate the distribution of the cie-paid expenses by category, without having to track special accounts:
Expenses:Cie-Reimbursements -2000 CAD ; cie Expenses:Electronics 1600 CAD ; cie Expenses:Food:Restaurant 400 CAD ; cie
The concept is a simple one: balance the entire book by considering only a subset of the entire set of transactions. In other words, we can consider that there is always a global filter on the books! This would be great, as we can then decide to balance each book individually.