Sep, 24, 2012 By Vikram Murarka 2 comments

Here's another very important MS Excel tip, one that I got from one of our clients. It has made my life so much simpler. Thank you, Krishnan!
Let us say there is a company that exported goods worth $ 5 million and received payment at a Dollar-Rupee rate of 45.45 on 17-Aug-11. It then exported goods worth $ 1 million and received payment at a rate of 56.00 on 23-May-12.
What is the average rate at which the exports have taken place? You will be surprised at the number of people who reply saying 50.7250, the simple average of 45.60 and 56.00. However, as you know, the correct answer is actually 47.21, since the larger amount of $ 5 million was exported at the lower rate of 45.45 in August 2011, as compared to the smaller amount of $ 1 million which was exported at 56.00 in May 2012. The rate of 47.21, the weighted average rate calculated as (45.45 x 5 mln + 56.00 x 1 mln)/ 6 mln.
Not only do the export/ imports/ forward contract/ option transactions take place at different exchange rates, the transaction amounts are also always different. Therefore, there is always a need to calculate the weighted average rate at which forex transactions have taken place. Since the transaction amounts are different, calculating simple average is simply wrong.
Thankfully, while a few people do erroneously make do with a simple average, most people calculate the Weighted Average Exchange Rate. Unfortunately, the way most people (I was one of them) do the calculation is quite cumbersome.

The way I used to calculate weighted average earlier in Excel is as follows:
Calculate the Rupee equivalent of the Dollar amount
Sum the Dollar amounts
Sum the Rupee amounts
Divide the sum of Rupee amounts by the sum of Dollar amounts
Note the SUMPRODUCT function in the formula bar. The weighted average is now calculated as SUMPRODUCT(range with Dollar amounts, range with Export rates)/sum of Dollar amounts.
Much simpler, isn't it? Thanks are due to Krishnan, my client at Marico Ltd, and to MS Excel. I hope this function will be as useful to you as it is to me!
In our 10-Dec-25 report (USDJPY 156.70), we expected the USDJPY to trade within 154-158 region till Jan’26 before eventually rising in the long run. In line with our view, the pair limited the downside to … Read More
Our last report (03-Oct-26, UST10Y 5.28%) warned of increased chances of Brent rising towards $135-145, US Core PCE rising towards 4.5-5.0% and the US10Yr rising towards 5.80% (Dec-26) and 6.00% (Aug-27). While Brent has risen to nearly $117 and the Core PCE (Aug-27) has inched up to 3.01%, we find that US Yields have fallen across …. Read More
In our October 2026 report (5-Oct-26, Brent @ $101.71), we had stated Brent would be structurally bullish for the long term based on the technical analysis, within which a test of $115 was expected in Oct-26, followed by a test of $134 by Dec-26 and eventually targeting $145 in 2027. In line with our expectation, Brent did test $117.45, slightly higher than our expected $115 in Oct-26. … Read More
In our October 2026 report (9-Oct-26, EURUSD 1.1250), we revised our Euro forecast downwards on a decline below 1.13 and expected a fall towards 1.10-1.0875 by the next year. Rising crude prices were a concern and were expected to raise the US and Euro yields further, along with the inflation figures. The Euro has been declining while below 1.13 and has tested
1.11 so far. Both the ECB and the FED kept policy rates unchanged in the Oct-26 meeting, while the markets expect a rate hike in the Dec-26 meeting by both central banks. …. Read More
In our 06-Oct-26 report (10Yr GOI 7.19%%), we examined the possibility of the RBI not raising the Repo rate, even though 91% of the market expected it to raise. We were barking up the wrong tree as the RBI did indeed raise the Repo on 07-Oct. Thankfully, our forecasted rise in Brent to $115-120 did materialize well with Brent touching a high of $118 on 30-Oct. We also forecasted that … Read More
Our November ’26 Dollar Rupee Monthly Forecast is now available. To order a PAID copy, please click here and take a trial of our service.

