Looking for Currency Exchange Formulas/Spreadsheets

Sabastan

New Member
Joined
Apr 6, 2010
Messages
2
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?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

SamTYler

Well-known Member
Joined
Mar 10, 2004
Messages
784
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

New Member
Joined
Apr 6, 2010
Messages
2
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,471
Messages
5,596,350
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top