SUMPRODUCT Help

[Guest]

I have a table of prices like the following:

Currency Currency FX Rate Price 2 Quantity
Price 1 Price 2

USD USD 1 38.61 500
USD USD 1 36.95 718
CAD USD 1.114 74.11 325
GBP GBP 0.56930 19.72 900
EUR EUR 0.7996 23.34 602
EUR USD 0.7996 49.87 400

Some of the time Price 2 is quoted in the same currency as Price 1, but if I
want to get the total cost of each record (price*qty) in the same currency as
Price 1, I have to multiply by the FX Rate whenever the two currencies aren't
the same. So the equation I am using is
=Quantity*Price*IF(Currency1<>Currency2,FX Rate,1).

Next, I want to calculate the percent change in value of all the records as
a portfolio. So for one record, (Price2-Price1)/Price1 = % Change in value,
but I cannot just add the % change of all the records to get the total change
in value of the portfolio. I believe I can do it with SUMPRODUCT, but if I
use

=(SUMPRODUCT(Price2*Qty)-SUMPRODUCT(Price1*Qty))/SUMPRODUCT(Price1*Qty)

I neglect to convert Price2 to the same currency as Price1 when the two
differ. (When they do not differ, no conversion is necessary to get an
accurate percent change.) I tried embedding something like

=(SUMPRODUCT(Price2*Qty,IF(Price1Currency<>Price2Currency,FXRate,1))-SUMPRODUCT(Price1*Qty))/SUMPRODUCT(Price1*Qty)

but I am getting an error. Anyone know how to make this work?

THANKS!

 

[Guest]

Why do you need the "IF" test when, according to your table, "FX Rate" is 1
if the values are the same so why not simply multiple by "FX Rate" for all
transactions?

+Quantity*Price*FX Rate

 

[Bob Phillips]

Toppers made the point that the IF test is redundant, but I don't see where
Price 1 and Price 2 comes into it, and why they would be different
currencies. As far as I can see, there is one calculated price dependent on
the unit price, the rate and the quantity.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

I may have over simplified when trying to create an example here.

Price 1 is the cost basis for an asset purchased.
Price 2 is a market quote on the current price of that asset from a data
feed like Bloomberg.

Some of the time the cost basis (Price 1) is a foreign currency, but the
market quote (Price 2) is USD. If I want to calculate the percent change in
the asset's value, independent of movements in the FX rate (aka FX
translation) then I have to look at the percent change from Price 1 to Price
2 in the same currency as Price 1 (the cost basis). If the cost basis (Price
1) is EUR, then the FX rate will reflect the EUR/USD FX rate. If you look at
the second to last row of my example, the FX rate actually is not 1 even
though the currencies are the same. That is why the "IF" statement is
necessary. It is not redundant. Multiplying by the FX rate in that case
would convert the EUR quote for Price 2 and throw off the calculation.

 

[Guest]

I replied to Topper as to why the IF test is not redundant. It is actually
necessary when you consider the second to last row of the example set where
both price 1 and price 2 are EUR, but the FX rate is not 1 because FX rate
reflects the conversion rate to USD. Price 1 and Price 2 are quotes from two
sources at two different points in time. They are different currencies
because they are foreign securities and one source quotes them in the local
foreign currency while the other source quotes them in the USD equivalent.
Hopefully that clarifies it.