Looking for Currency Exchange Formulas/Spreadsheets

[Sabastan]

I have an account where the client buys and sells Euros and Dollars on a regular basis. This would be simple enough to track except I have to keep track of the original inventories exchange rate. Let's say an original purchase of 100,000 Euros is at 1.5% (to the Dollar). The client then purchases 100,000 more Euros at 2%. Then decides to sell 150,000 Euros. The original 100,000 is exhausted and I have to apply the basis rate of 1.5% to it, and 2% to the remaining 50,000 Euros of the total 150,000 Euros sold. Is there a formula that can help keep track of original inventories so I can just put the amount in and it totals automatically, knowing the original basis rate so it accurately reflects the profit off of the 1.5% of the original 100,000 Euros, and 2% of the 50,000? This is further complicated by the fact that the client not only makes several transactions a month, but that they buy and sell both Dollars and Euros. It's such a headache to try to keep track of original inventories that I would be willing to donate a body part just to get a spreadsheet built for this. I have thus far been unsuccessful finding one or a formula that simplifies this process... Can Anyone Help?

 

[SamTYler]

By what manner are the transactions entered into the spreadsheet at this time?

Are you willing to change that?

when they buy Euros, do they use the USDollars in inventory and vice versa?
Or, shudder, sometimes use inventory and sometimes other funds?

 

[Sabastan]

Currently, the Euro or Dollar sales are entered manually. I have the excel sheet set up to apply whatever exchange rate (either multiplied if it's Dollars, or divided if it's Euros), then subtracted from the proceeds. I'm looking for a way to apply a FIFO (or First in First out) methodology which many companies use - whether it's Dollars or orange crates - many companies have to keep track of inventory prices as they purchase regularly each month and exhaust initial inventories before applying new rates, even though they still have additional stock. If that makes sense.

But yes, I am more than willing to change the manner in which the transactions are entered...

And yes, when they buy Euros, do they use the Dollars in inventory and vice versa...

I've managed to keep the Swiss Franks out of the equation, but even between the Euros and Dollars, it's been sufficent to keep this headache perpetuated lol

I'm sure there's a spreadsheet out the for the FIFO methodology, but so far, haven't found one that works... Thanks in advance for any help you can provide...