Mixing U.S. and Foreign Currency in Quicken
Dave and Helen Damouth www.damouth.org
October 25, 1999 (note added 10/14/2000)
Note added 10/24/2000: The release of Quicken 2000 makes the following document obsolete. Quicken now contains full support for multiple currencies. The multiple currency feature is very well designed, and is simple and intuitive to use - a great benefit to those who travel in more than one country. For those still using older versions, or who use other personal finance products which do not have support for multiple currencies, I'll leave this document on the Web Page.
RV travelers who spend significant time in two different countries may have a difficult time keeping a consistent record of expenses. A computer makes it much easier, but there are still problems. Since Quicken is the most common computer program for managing personal finances, I will describe the system I have developed for dealing with this problem using Quicken. The examples below assume an American traveling in Canada, but all the procedures are valid for any countries - A Canadian traveling in Mexico would do exactly the same thing. I should note that I'm numerically compulsive, and like to know at the end of the year how every penny was spent. If you're the type who doesn't worry about where the money went, this article won't be of interest.
Whenever traveling in a foreign country, I use a credit or debit card as much as possible, since this always results in the most favorable exchange rate. Every couple of days, receipts from credit purchases are entered into Quicken, in local currency. That is, if my receipt shows that I paid $32 Canadian dollars, I'll simply enter a transaction for $32 in the appropriate credit card account in Quicken. (From now on, I'll indicate Canadian dollars as C$32). Cash transactions are entered into a special Canadian cash account, also in local currency (that is, without making any conversion to U.S. currency at the time of entry).
People who don't travel with a computer and who wait until they are back home to do the accounting must be particularly careful to save receipts (and to make paper notes about cash purchases where a receipt is not received). Canadian credit purchases may not show up on your bank until you've long since forgotten what you bought. It's real easy to end up with a bunch of vague receipts you can't match with the bank statement, and a lot of money unaccounted for. When dealing with small vendors in rural areas, I have a particular problem with those old-fashioned cash register tapes, which record no information about a purchase except the price, and may not even record the name of the vendor. Unless you make a handwritten note on these receipts, they will mean nothing when you get home.
I obtain foreign cash from an ATM machine in the foreign country. This method is very convenient and gives the best exchange rate, but you won't know the exchange rate until a month or two later. Therefore, I enter the transaction into Quicken in local currency That is, if I receive C$100 from an ATM machine in Canada, I'll simply record a $100 transfer from my credit card account to my Canadian cash account
So far, this record keeping is all simple and familiar (I keep it that way deliberately, so that there is no excuse to avoid entering the data promptly.) But now the fun starts: The Quicken accounts won't balance properly because they contain a mixture of Canadian and U.S. dollars.
First, we'll deal with the cash: As a simple example, suppose I take a weekend trip into Canada. I obtain C$100 from an ATM machine after I enter Canada. I then make three cash purchases in Canada, which for simplicity, I'll assume add up to C$90. When I get back to the U.S., (or perhaps at the currency exchange booth in the duty-free shop at the border), I'll exchange the remaining C$10 for $6.00 in U.S. cash. Of course, I keep receipts for each transaction. Back home, I enter each of these transactions into Quicken. Quicken only knows about one kind of dollar. I don't know what exchange rate will be used by the bank when the C$100 ATM transaction is billed to my account, so I simply enter a $100 cash withdrawal. I also enter the cash purchases for the actual amount in Canadian dollars, not attempting to guess what the amount will be in U.S. dollars.
It helps keep this all straight if I create a new cash account in Quicken to be used only for Canadian cash and for purchases made in Canadian dollars. After I've initially entered all the raw data, this new cash account shows a $100 transfer in from my checking account, three cash purchases of $20, $30, and $40, and a transfer out to my U.S. cash account of $6.00, with a bottom line balance of $4.00. As it stands this doesn't make much sense. We'll straighten it out later.
At the end of the month, I receive my bank and credit card statements. From these, I'll learn that the C$100 which I received from the ATM machine in Canada was billed to my account as $64 (This has been a great year to travel in Canada!). Now, I have all the information necessary to balance the cash accounts.
I first go to the $100 transfer in the Canadian Cash account, and make the following change: I click "split" on this transaction, to create a split transaction. In the "split" window, I change the $100 cash transfer line to $64. The next line in the split window now shows a $36 uncategorized remainder. Enter a category for this amount. I use "currex", meaning "currency exchange". Quicken will ask you to confirm that you want to create a new category. Click "yes" to finish setting up the new category, then close the split window and enter the transaction. Your checking account or credit card account now correctly shows that $64 was transferred to the Canadian cash account, and will now balance properly.
Next, open the transaction which shows a $6.00 transfer to the U.S. cash account, and again create a split transaction. Enter an additional split line, showing a $4 expenditure with a category of "currex". Click the "adjust" button in the split window, which removes the -$4.00 uncategorized amount and changes the overall transaction amount to $10.00. Close the split window and hit "enter" to complete entering this modified transaction. The Canadian cash account now shows a zero balance - which is correct, since I no longer have any Canadian cash.
Everything in Quicken is now in balance. But if I print an overall cash flow statement, I'll find that I have a mysterious income of $40, categorized as "currex". If I like to print summaries of my expense categories, I'll find them slightly distorted, because the things I bought in Canada seem to cost too much. If I don't care about such things, I'm done. However, I like to fix this problem, so that, for example, my grocery expenses for the two months I'm in Canada won't show up as 40% higher than normal. I do this the laborious way - by manually converting each Canadian cash purchase back to U.S. dollars. (I haven't found any automatic way to make this conversion.)
I do this as follows: Continuing with the example above, I know from my bank statement that the exchange rate I received on the ATM cash was 64%. So I multiply every Canadian purchase by 0.64, and enter the new transaction amount. So, for example, the $20 purchase will now be entered as $12.80. (Before doing this, I copy the actual Canadian amount into the memo field, so I can recover it if I make an error, or in case a question arises in the future). After doing this for each purchase, the Canadian cash account no longer shows a zero balance. The reason is that I need another "currex" currency exchange adjustment, since I've just converted a bunch of Canadian currency back to U.S. dollars. So I now enter one more transaction, a $32.40 expense in the "currex" category, to properly account for my having converted $90 in Canadian purchases back to $57.60 in U.S. dollars (a $32.40 difference). The Canadian cash account now shows a zero balance, and I am done.
Perhaps not quite done, if I am watching all the details. At this point, if I print a summary of the "currex" category, I might expect a zero total. In fact, for the example above, the total is -$.40. This is a real expense. It reflects the fact that I lost money when I converted $10 of Canadian currency back into U.S. currency at a poorer exchange rate than I received when I initially acquired the Canadian currency. This could have been a gain instead of a loss if the exchange rate had shifted in the other direction. (If I was good at predicting which way the exchange rate will move, I could get rich). There is also another reason for the above currency exchange loss: Exchange of cash at a bank or at the duty-free shop (as in the example I chose) will always be at a worse rate than I would receive on the same day at an ATM machine.
All of the above was just to deal with cash purchases. Now, let's reconcile the credit purchases I made in Canada. This is conceptually easier, because my credit card statement shows exactly what I paid in U.S. dollars for each purchase. As I do my usual reconciliation of the bank statement with my Quicken account, I simply replace the Canadian amount I entered previously with the U.S. amount on the statement. Again, I like to move the Canadian amount into the memo field for future reference. (Knowing the Canadian amount may be helpful if I have to call the Canadian vendor with a question or to arrange an exchange)
Folks who are making short trips into Canada and returning to a home base may choose not to enter the credit transactions until the bank statement arrives and then to enter the correct U.S. amount. (But as noted below, it may be months before the Canadian charges show up on the statement)
Some people have the ability to directly download the credit card statement in a form that will automatically be entered into Quicken, doing essentially no manual entry. I don't do this. I like to enter the transactions as they occur, usually within a few days of the transaction while my memory is fresh, and so that I have an up-to-date estimate of account balances. For purchases in Canada, it may take a long time for some purchases to appear on my statement. And a surprising number of Canadian vendors still process credit card transactions manually. This means that the transaction date which shows up on my bank statement may be a week or more after the date on my paper receipt. Sometimes the vendor names don't match up either - and because of the exchange rates, the amounts will never match. This can make reconciliation difficult, unless I enter the data while it is fresh. For people who spend a few days in Canada, it's not a big deal. But we have spent over two months in Canada in each of the past two summers, and after we are back in the U.S., it may take another two months to receive the last bank statement which contains a Canadian transaction. (As just one example, we stayed in Bigfoot Campground, in British Columbia, on August 29. The charge was posted to our credit card account on September 16, and I'm reconciling the statement on October 25, having just received the statement from our mail forwarding service on October 24).
For people with only a few Canadian transactions, the above process can of course be simplified: It's not necessary to create a separate account for Canadian cash transactions. Entering these into your regular cash account works fine (this is what I did until recently). But in this case you lose the convenient crosscheck of seeing a zero bottom-line balance in the Canadian cash account when you've correctly dealt with all the currency exchange issues. Also, there's really no need to create the separate "currex": category. Simply use your regular "miscellaneous expense" or "bank charge" category instead. You lose the convenience of being able to instantly generate a report showing your currency exchange adjustments, but if there are only a couple of such transactions, it doesn't matter. Finally, none of the "currex" transactions need to be entered at all. Without them, you won't have the intermediate consistency checks that were generated in the above example. But after all the Canadian transactions are converted to U.S. dollars, everything will balance except for that small gain or loss from different exchange rates. This can be ignored, or may simply be entered as a single "miscellaneous" transaction at the end. I don't recommend this for extended trips, just because if you mess up something in the several pages of transactions, leaving out the intermediate steps makes it much harder to find the problem.
Once you get used to it, the above procedures are fast and easy - it's quicker to do these things than to read about them. Have fun!