Recording forex transactions

[mfmelb]

Mark,

I am having a bit of problem recording forex transactions.
Example:

AUD/USD trade - sold $1M AUD @ $1.08 giving me $1.08M USD.
My base account for fund manager software is all in AUD.
How do I create the investment transaction which will track the changes in exchange rate?
I have tried creating an investment called AUDUSD with a base currency of USD. Then I enter a transaction that sells 1M shares of AUDUSD @ price of 1.08 which gives me a value of 1.08M USD.
This creates a problem in my broker cash account - it increases the total of my AUD account by $1.08M AUD.

I tried setting up a USD cash account and link the investment to the USD account and manually adjust the AUD account. That is, I reduce $1M in the AUD cash account and then when I do a sell AUDUSD, the USD cash account would increase by $1.08M. The problem with this method is that I have problems with the valuation reporting. It is not converting the USD cash account to AUD properly eventhough I have tick the exchange rate adjusted view. Also, the overall portfolio balance is incorrect when including both the investment and the USD cash account.

How do you record such transaction that allows easy pricing with the exchange rate and also tracking the balances correctly.

Thanks.

Matt

[Mark]

Hi Matt,

You'd want to set up investments and record your transactions to model what actually happens. So, if you sold AUD cash and bought USD cash, set up an AUD cash investment and a USD cash investment. Record a sell in AUD and a buy in USD. In your example, you would sell 1M shares of AUD and buy 1.08M shares of USD, keeping both cash accounts at a fixed price of 1.

If you have both AUD and USD investments in the same sub-portfolio you may not want to use a default cash account in that single sub-portfolio, as all recorded transactions that use/generate cash will have a corresponding entry in that one single assigned default cash account. I'm not sure of your situation, but you may want to divide the account into 2 sub-portfolios, one for all your USD holdings and one for all your AUD holdings, so you can assign a USD cash account in one and a AUD cash account in the other. If you only are dealing with cash and everything else is a AUD investments, this isn't necessary, but if you have some holdings in AUD and some in USD, it will work out better to separate them, so when you sell a US investment, the proceeds go into your US cash, and when you sell a AUD investment, your cash goes into the AUD investment. If you go with the 2 sub-portfolio approach you would need to record both sides of the cash transaction, where you sell AUD in one sub-portfolio, and buy USD in another. If you are able to stick with a single sub-portfolio, you only have to record the transaction into the non-default cash account investment, and the other side will be recorded automatically for you, based on your assigned exchange rate at the time you record the transaction.

[mfmelb]

Thanks Mark.

I did have the two accounts - a AUD cash account and a USD cash account. However, I can't get the USD account to convert properly to AUD when running the report for valuation.

I have the following transaction created - in the AUD account, I reduce it by $1M AUD.
In the USD account, I increase it by $1.08M USD (1,080,000 shares at $1). When running the valuation report, it gives me $1.08 AUD for the USD cash account. It should be looking at the exchange rate and gives me the AUD equivalent.

Is it b'cos I am using $1 as the price that is causing this problem? I set it at $1 because I have declared the transaction as an USD transaction.

Regards

Matt

[mfmelb]

Mark,

I think I might have found the problem.
I declared the currency using USD. If I use $, it appears to change the amount based on the exchange rate.
Will continue to test this.

Regards

Matt