Help- Investment tracker with foreign currency

[jonathon81]

Hi members!

Is there a formula or a better way to calculate currency gains on a global stock portfolio? I have attached a tester but i believe the currency conversions are incorrect and therefore, producing a erroneous portfolio gain.

Any help would be greatly appreciated! -Also, welcome any tips for the rest of the portfolio calculations or attempts. This is my first attempt at excel- its been a great learning story so far!!


Thanks.

Jonathon.

[bebo021999]

Lots of worksheets and we dont know which WS to start with.
Could you tell us more about which sheet, which cells need to be filled with formula and what and where are your expected results?

[jonathon81]

Hi Bebo

I am unsure with the formula ive used to calculate the currency gains and then the overall profit/loss.

For example in worksheet "TRADES"- i have for Google.. Capital gains formula = (Price USD - (Consideration USD / Quantity of shares) * Quantity of shares). Then for currency formula = (Consideration USD * (FX rate when purchased - Current FX rate). I then add those two answers together and divide by the consideration USD to give me the stock profit/loss percentage. Then to calculate the Total AUD I use the formula (Consideration USD * current FX inverted) * (1+ the profit/loss)

Just not sure if that calculates the true profit/loss with currency gain. Just unsure.

Thanks again for your help.

Jonathon.