I'm attempting to validate the formula used to calculate the Federal Reserve Dollar Indexes, as published on January 15, 2019, in this document: https://www.federalreserve.gov/econres/notes/feds-notes/revisions-to-the-federal-reserve-dollar-indexes-20190115.html
The formula states that the index value "I" at time "t" should be computed as follows:
I = I(t-1) * Product(j=1..26, [e(j,t)/e(j,t-1)]^w(j,t))
Here, I(t-1) represents the previous index value (from the previous day), j=1..26 denotes the 26 currency exchange rates that constitute the Nominal Broad USD index, e(j,t) represents the current exchange rate of currency "j" (the price of 1 Dollar in currency "j"), e(j,t-1) represents the previous exchange rate, and w(j,t) corresponds to the weight of currency "j" in the index on the current day.
The list of currencies comprising the index, along with their respective weights (updated annually), can be found in this publication: https://www.federalreserve.gov/releases/h10/weights/.
To validate the formula, I collected the closing prices of all 26 currencies for June 13, 2023 (t), and June 12, 2023 (t-1), as well as the index values for those two dates. Using Excel, I performed the calculation as described above, expecting to reproduce the index value for time "t". However, the obtained result significantly deviates from the expected value.
I obtained the official exchange rates and index values from this page: https://www.federalreserve.gov/releases/h10/20230620/. However, this source lacks data for 9 exchange rates. To retrieve their daily closing values, I referred to:
https://www.exchangerates.org.uk/USD-ILS-exchange-rate-history.html
https://www.exchangerates.org.uk/USD-ARS-exchange-rate-history.html
https://www.exchangerates.org.uk/USD-CLP-exchange-rate-history.html
https://www.exchangerates.org.uk/USD-COP-exchange-rate-history.html
https://www.exchangerates.org.uk/USD-IDR-exchange-rate-history.html
https://www.exchangerates.org.uk/USD-PHP-exchange-rate-history.html
https://www.exchangerates.org.uk/USD-RUB-exchange-rate-history.html
https://www.exchangerates.org.uk/USD-SAR-exchange-rate-history.html
https://www.investing.com/currencies/usd-vnd-historical-data
Here are my data and calculations in csv format:
Country;Weight;12.06.2023;13.06.2023;e(t)/e(t-1);[e(t)/e(t-1)]^weight
Argentina;0,353;246,71;247,12;1,00166187;1,000586325
Australia;1,211;0,6747;0,6776;1,004298207;1,005207486
Brazil;1,714;4,8773;4,8567;0,995776352;0,992771587
Canada;13,104;1,3377;1,3298;0,994094341;0,925318597
Chile;0,723;788,6;803,7;1,019147857;1,013807483
China;14,567;7,1437;7,1585;1,002071756;1,030607098
Colombia;0,64;4184;4176;0,998087954;0,998775869
Hong Kong;1,177;7,8335;7,8336;1,000012766;1,000015025
India;2,954;82,4300;82,3800;0,999393425;0,998209238
Indonesia;0,744;14871;14870;0,999932755;0,999949969
Israel;0,877;3,6001;3,5598;0,988805867;0,990175958
Japan;5,49;139,7200;140,0000;1,002004008;1,011051618
Korea;3,594;1288,6100;1271,4800;0,986706606;0,953041476
Malaysia;1,566;4,6180;4,6140;0,999133824;0,998643901
Mexico;13,784;17,3080;17,2140;0,994568985;0,927683065
Philippines;0,618;56,075;55,912;0,997093179;0,998202586
Russia;0,501;83,792;83,949;1,001873687;1,000938279
Saudi Arabia;0,466;3,7494;3,7501;1,000186697;1,000086996
Singapore;2,008;1,3443;1,3416;0,99799152;0,995971054
Sweden;0,588;10,8244;10,7179;0,990161117;0,994202957
Switzerland;3,046;0,9099;0,9053;0,994944499;0,984680445
Taiwan;2,652;30,7600;30,6700;0,997074122;0,992259313
Thailand;1,275;34,6800;34,6500;0,999134948;0,99889719
United Kingdom;4,654;1,2489;1,2611;1,009768596;1,046281477
Vietnam;2,37;23,48;23,495;1,000638842;1,001514717
Euro Area;19,325;1,0747;1,0792;1,004187215;1,084098745
INDEX;100,001;120,0069;119,5948;;0,933218371
[Last line: INDEX; sum of weigth; index value 12.6; index value 13.6.; ; Product of all values of last column]
As observed, the published index value for June 13th is stated as 119.5948. However, based on my calculations using the provided formula (120.0069 * 0.933218371), I obtained a result of 111.9926437.
I am unable to identify the specific error in my calculations that led to this discrepancy. I have carefully followed the formula and utilized the closing prices of the respective currencies on June 12th and June 13th, as well as the corresponding weights.
If possible, I would appreciate any guidance or assistance in pinpointing the source of the discrepancy and correcting my calculations to obtain the accurate index value for June 13th.
