How to calculate your foreign exchange fees in Excel

Excel function tutorials, Personal finance

Moving currency across borders is far more expensive than most suspect. That’s because banks and money transfer providers charge more than the wire fees you see upfront.

These businesses make money in three different ways. Their wire fees are the most visible charge. However, two other categories often fly under the radar – commissions, and the exchange rates they offer.

To calculate how much you’re paying in foreign exchange fees, you need to account for all three factors. Below, we’ll show you how to create an Excel spreadsheet that will evaluate the fees charged by money transfer providers.

The easy part: accounting for wire fees & commissions

Let’s get the basic stuff out of the way first. The first two factors – wire fees and commissions – are absolute numbers that don’t change much from day-to-day. That makes them incredibly easy to account for.

After creating the outline of your spreadsheet (as shown above), enter the institutions you’ll be comparing in column A. Then, enter your transfer amount in column B. Next, find the fees these institutions charge upfront. $30 is our default for the banks, as it’s the lowest amount many American institutions charge for international outbound transfers. Enter what you find for each service in column C.

Then, if you can track it down, enter the commission percentage in column D. Enter it as a decimal (e.g., 0.02, not 2%), so it will work in our equation. However, we’ll acknowledge that this figure is hard to find in the public sphere. For instance, Western Union pays agents a commission for the transfers they process. However, to protect itself against competitors (and from public scrutiny), it treats these figures as a trade secret.

Above, we’ve entered a figure of 0.02 for the banks. This figure is a standard rate many airport exchange desks in airports earn on every transfer they make. We use 0.06 as a conservative estimate for Western Union’s agent commission – however, some rumours state that some can earn up to 30%!

The math-intensive part: calculating the exchange rate margin

So far, putting together our spreadsheet has been an exercise in data collection. Here’s where it starts to get “mathy” (yes, we’ve just invented that word… deal with it.) You’ll need to find two exchange rates – the one your institution charges, and the interbank (aka the “wholesale”) rate.

Some banks make this data readily available, like Toronto Dominion Bank in Canada. However, many American institutions aren’t fond about making this info public. To be fair, Bank of America kind of does it, but only for inbound transactions (e.g., they’ll show you their CAD/USD rate, but not USD/CAD.) As a result, you may have to call your local branch.

Next, find the interbank rate. For decades, XE.com has been the web’s trusted source for this data, a purpose it continues to serve. Plug in the amount you want to transfer, your desired currency pairing (e.g., USD/CAD), and click the arrow button. You’ll get the interbank rate, as well as the inverse for the pairing you chose.

Now, take your bank’s rate and subtract it from the interbank rate. Repeat this for every money transfer provider you’re analyzing, and input the result in column E.

The hardest part: Calculating your total cost

Time to find out how much each money transfer provider is charging you in fees. We put the “Total Cost” column to the right of the others to add emphasis – we advise you do the same.

Begin the formula creation process by clicking the first cell in Column G, and entering the equal symbol (=). Start with the easiest component – wire fees. Click on the first cell in Column C, then enter a plus sign (+).

To calculate the commission, we’ll need to make a bracketed equation. Open a bracket, then click on the first cell in Column B (your initial transfer amount.) Then, enter the multiplication symbol (*), followed by clicking on the first cell of Column D (the commission percentage.) Remember to keep this figure in decimal form, or you’ll break the equation.

Close the bracketed equation, and enter another addition symbol. Then, start the second bracketed equation – this one will determine the exchange rate margin. After opening the bracket, click on the first cell of Column B. Follow it with a multiplication symbol, and then click on the first cell of Column E.

Close this bracketed equation, and hit enter. If done right, this should calculate how badly your bank is ripping you off. According to the spreadsheet we’ve made for this article, your equation should look like this:

=C3+(B3*D3)+(E3*B3)

Your final step: Duplicate these results for the remaining entries. Hit CTRL+C  (Command+C if you’re a Mac user). Then, highlight the remainder of the cells in Column G and hit CTRL/Command+V. This step will copy the formula to each row, giving you data on the remaining money transfer providers.

Not an Excel wizard?

If you can’t be bothered to craft your own spreadsheet from scratch, we don’t blame you. However, you shouldn’t allow money transfer providers to take liberties with your money.

Over on MoneyTransferComparison.com, they have an international money transfer fee calculator. Enter your initial transfer amount, and the amount sent. In seconds, you’ll know whether you got a killer deal, or if you got fleeced.

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.