I could not find a thread that matched exactly what I am looking for - but it is possible that it is there and I just did not see it. Apologies for any oversight.

I am trying to create an excel sheet that will give me "up to the minute" position sizing for my forex trades. I can just use an online position sizing chart, but I would rather not do that.

I have already created the appropriate tables to get the updated currency rates imported into Excel.

I have no problems with Direct Currency rates of course - those are easy.

My account is in USD.

Let's assume a $25,000 account balance wanting to Risk 1% per trade ($250 / trade).

Trading EURAUD.

We will assume that I want to go long at 1.52417 & a stop at 1.51417. Total of 100 Pip risk.

250/100 = $2.50 per pip.

But since I am trading EURAUD then I need to use the AUDUSD current rate to find out how many AUD / pip.

Current AUDUSD rate: .89615

AUD 2.24 / pip.

That would make a total loss of AUD 224.-- if i get stopped out.

How many Units do I need to buy in order to place this trade? (I realize that the exact rate will change as the prices change - but do we have enough information in order to create a really close estimate?)

Thank you in advance for any help that you for me.

Can anybody help me with the excel formula for this?

Thanks!

I believe you can easily program your formula on excel.
The sequence of operations would be as follows:

1. convert your risk, i.e. 250$ to the base currency of the Forex pair of interest. In your example you want to trade the EUR/AUD, so you need to convert to AUD using the most accurate exchange rate available at that time. So you will get 250USD / 0.89615 = 278.97AUD

2. now calculate the price difference between your entry and your exit. In your example you say buy at 1.52417 and stop at 1.51417 so you have a total distance of 0.01

3. finally derive your position size by dividing your risk in the base currency (in this example AUD) by the price distance. In other words, 278.97AUD / 0.01 = 27,897EUR

So to double check your work: the position size that you should buy on this pair is roughly 28,000 EUR at 1.52417.
If you get stopped at 1.51417 you will lose 280AUD and that's equivalent to 250.922USD at the exchange rate that you state above.

Hope that helps.

Cheers
Fadi

Wow, that is wonderfully helpful - and by far the best and most clear explanation that I have found anywhere. Thank you

The process is, of course, similar when the USD is the base currency, but it does not seem to be exact. Could you help me out, also, with that?

Let's take USDJPY

Short @ 102.089
Stop @ 102.543

45.4 pips

We will say that the risk is the same at $250.

Sure let's try together:

You wanna short at 102.089 and stop at 102.543 --> so price distance = 0.454
We risk 250USD and that would be equivalent to 250x 102.543 = 25,635.75 JPY.
I am taking the exchange rate at time of being stopped out, as this will be the most accurate

Finally: 25,635.75 / 0.454 = 56,466.41 USD

Let's check for final confirmation:
Sell 56,500 USD.JPY at 102.089 and buy at your stop 102.543 -> so you lose 25,651 JPY
... and that would be equivalent to 25,651 / 102.543 = 250.15 USD

Again, thank you...and, again, this is the most clear example that I have seen anywhere and I really really appreciate it.