spreadsheet finance calculation help please

  rsturbo 00:11 25 Nov 2014

i want to make a spreadsheet to work out the following: pounds invested into dollars and profit when i sell so i need columns for the following (i think) pounds invested purchase exchange rate dollars bought sales exchange rate profit in dollars profit in pounds i would sell all in one go and dont need to concern myself with commissions just yet. i cant work out how to do this and its driving me mad!! :( please can somebody help me or point to where to go??

  lotvic 16:19 25 Nov 2014

Is it something like this you want? click here it says "The Excel function is perfect for whenever you need a currency conversion. I use it to keep track of the British Pound value of a US and Canadian Dollar bank account."

"The rates are scraped from Yahoo Finance, and are real-time (but delayed by 15 or 20 minutes)."

Other than the above, do a google for excel formulas for exchange rate and there's lots to choose from.

  rsturbo 22:15 29 Nov 2014

thanks for replies. i want to create a sheet whereby i can place in the fields the pounds i invested the exchange rate so that feeds into a cell that tells me how many dollars i bought.


i want to play around so i can look to see if i invested say; £100 the exchange rate was £1 for $1 so i would get $100

but then the new exchange rate is now 1.5 dollars for 1 pound so i plug that into the new exchange rate cell that could then tell me what profit i would make(in pounds) if i sold all the dollars at the new rate

i hope i have explained myself clearly???

  lotvic 02:25 30 Nov 2014

If you invest £100 at exchange rate of (£1 buys $1) you would receive $100

If you invest $100 at exchange rate of ($1.5 buys £1) you would receive £66.66p (and two thirds of 1p)

so you would make a loss of £33.33p (and a third of 1p)

If you want it on a spreadsheet:

A1: £ Invested

A2: 100

B1: Exchange rate £? buys $1

B2: 1

C1: USD $ received

C2: =SUM(A2xB2)

D1: Exchange rate $? buys £1

D2: 1.5

E1: GBP £ received

E2: =SUM(C2/D2)

F1: GBP £ Profit or Loss

F2: =SUM(A2-E2)

  lotvic 02:37 30 Nov 2014

ps: format the cells on row 2 as Number so they will show decimal points

  lotvic 02:42 30 Nov 2014

sorry, I meant General not number.

  wee eddie 04:09 30 Nov 2014

There is, of course, another possibility.

Several of the On-line Brokers allow you to create a Virtual Holding and trade with that. All the calculations are done for you and, as far as I know, this facility is usually free.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Huawei MateBook X Pro (2019) review

Inside the redesign of the world's largest computer games platform, Steam

Apple TV Plus streaming service launch, release date and price

Apple TV+ : date de sortie, prix et catalogue