Category Archive 'Excel function tutorials'

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.

Calculating A Bonus Or Commission In Excel

Excel function tutorials

One of the recurring type of requests that I receive through managing this blog is trying to calculate a commission or bonus structure, especially by managers that have a sales force. I did touch on the subject a bit but I thought I’d do a quick review of the different type of commissions and how I’d calculate them in Excel. I am also providing an Excel file that you can view here if you can’t wait:) Here a few types of commissions and how I’d do them:

Flat Commission

In this structure, a sales person would get a % of sales no matter what. This is the most simple case:

1

Flat Commission With A Floor

In this structure, a sales person would get a % of sales but only under the condition that they reach a minimum of sales:

2

Flat Commission With A Ceiling

As much as companies like to reward star employees, some are worried about the idea that a sales guy would end up making more than the president so they end up adding a maximum/ceiling that someone can earn:

3

Brackets

Here is a common system that is used. If you generate 10K in sales, you will earn X%. If you earn 25%, you will earn X% of your sales, etc. Here I will use the Nested If excel function:

=IF(A10<$A$2,$B$2*A10,IF(A10<$A$3,$B$3*A10,IF(A10<$A$4,A10*$B$4,A10*$B$5))) 4

Scaling Bonus

The scaling bonus looks similar but is different in the sense that if you look at the same chart:

5

This time, if you make $25K in sales, you will get 3% of the first 10,000 and 8% on the next 15,000. This is slightly more complicated. Here is how I will do it, step by step:

First I will determine the amounts you would make at each number of the scale:

6

Then, I will determine the appropriate “level” and add anything incremental by using nested if conditions:

=IF(A11<$A$3,A11*$B$2,IF(A11<$A$4,$C$3+(A11-$A$3)*$B$3,IF(A11<$A$5,$C$4+(A11-$A$4)*$B$4,IF(A11<$A$6,$C$5+(A11-$A$5)*$B$5,(A11-$A$6)*B6+$C$6)))) 7

As is always the case, you can Bonus_Commission.

Using Excel Keyboard (and other) Shortcuts To Dramatically Increase Productivity

Excel function tutorials, Excel spreadsheets (.xls)

I talk about productivity and improving the efficiency of Excel spreadsheets on this blog but I`ve never actually discussed one of the primary ways that I save time when working in Excel. In the past, I`ve talked about adding the most common things that I do to save time. There`s no doubt that creating macros and using powerful functions in order to automate some processes are key but I probably save as much if not more time by simply “not searching” for functions in the Excel spreadsheets. How? You’ve probably heard of the most famous keyboard shortcuts:

Ctrl-C: Copy
Ctrl-V: Paste

These are clearly the ones I use most, not only in Excel but on my computer in general. Here are a few others that I use a lot:

CTRL+PgUp:Switches between worksheet tabs, from left-to-right.
CTRL-F: Find
Shift+Space: Select entire row

You can also get a full list on this PDF from the Microsoft website.

This certainly solves most of my issues but there are some functions that are more complex to use as shortcuts but that I use over and over. What do I do with those? Simply add the icons to the top of my Excel bar in order to make it much easier to do. How? Let’s take the example of “Paste as Values” which I tend to do all the time. How?

-Click on File

-Click on options
1

-Click on “Quick Access Toolbar”
3

-Find the exact function and click add
4

After that it will appear at the top of your “Excel” application:
5

Easy enough? I promise that you will be very surprised at how much time this will end up saving you.

Understanding Complex Spreadsheets With “Slate”

Excel function tutorials

I don’t know if this has happened to you before but I can tell you that it’s a major challenge when you open a spreadsheet built by someone else and try to see where numbers come from. Yes, there are some Excel built-in functions that can help but it’s very difficult to use honestly. A few weeks ago I looked into “Slate”, an Excel add-in that makes that whole task a lot easier. It gives you a better understanding of the numbers that were used to get to a sum. It adds it in a visual way as you can see here:

As the spreadsheet becomes more complicated, so does the Slate chart as you can see here:

I simply installed the add-in (you get a free licence for now), opened a spreadsheet and saw this:

1

Then I clicked on that Slate button and got this, which I can dig into:

2

I’d be curious to hear from any of you that have tried this or are considering it? Just to confirm, I’m not being paid to write this, even if you try it:) It is just a very interesting product in my opinion. You can try it at UseSlate.com

Gathering Financial Data in Excel With YCharts

Excel 2013, Excel function tutorials

One of the most requested subjects on this blog is getting financial information into Excel. The easiest way of course is having a Bloomberg terminal which makes it possible to access a seemingly unlimited amount of data about the economy, publicly listed companies, indexes, etc. The possibilities are incredible. Think there is a drawback? Of course. Given it has a financial professional target audience, the cost is typically between $1000-3000/month depending on the type of service you have, if you’re getting live prices, etc. Yes you read that right, it’s per month.

It seemed incredible to me that no one was trying to disrupt this market no matter how complex it is. I have written a few times about how it’s possible to get some financial data from Google finance in Google docs, cloud-based spreadsheets:

Using Google Spreadsheets As An Alternative To Excel For Stock Prices And Information
Managing A Stock Or ETF Portfolio From Google Docs
How To Add Exchange Rates In Google Docs

There’s no doubt that those capabilities are very interesting and make it easy to track the value of a portfolio for example. What if you’re trying to do more though?

One clear candidate emerges: YCharts

Ycharts is a website where you can do a lot of what you’d do on a site like Yahoo finance or Google finance. You can build stock lists, look into a company’s financials, past statements, etc. The big difference is that if you join one of their paid memberships, you can get access to their very powerful excel plug-in. While not exactly Bloomberg, the possibilities with this spreadsheet are very very impressive. Look at how I’m using it personally:

I have a list of 300-400 stocks that are on my screener and represent potential purchases. I then have all of those stocks in a spreadsheet and look to get specific metrics such as market cap, price, dividend yield, P/E and some growth metrics with their custom formulas. For example, to get Blackrock’s dividend yield, I’d enter:

=YCP(“BLK”,”dividend_yield”)

Every time I refresh my spreadsheet, I’ll get updated values. This is what this specific spreadsheet looks like:

They also provide templates that you can use to either look at many companies or at one in more details:

http://ycharts.com/excel/templates

I’m guessing at this point i’ts starting to look like I was paid to write about this but I’m just a big fan of what they’re doing and have been a paying member for 6 months now.

Has anyone else given YCharts a try? If so, I’d love to get your thoughts.