Microsoft Office Excel is a great computer program that is widely used throughout the financial industry. Excel is an invaluable tool for portfolio managers, traders and accountants. Billion dollar portfolios and positions can be managed and traded using Excel spreadsheets. Management reports and risk management tools can be created and run in this program. In short, Microsoft Excel has created incredible efficiencies in the finance and accounting industries.

In this article, we'll demonstrate some of Excel's functions and features that a financial professional can use to make his or her job more efficient. This article does not discuss Visual Basic for Applications (VBA), but instead focuses on Excel features that non-programmers can deploy. Only basic knowledge of Excel is needed to make use and benefit from this program, so read on to learn about how it can make your job easier.

Inserting Functions
Excel comes with a wide array of functions that can easily be inserted into a spreadsheet. In Microsoft Excel 2010, adding a function is as easy as clicking on the "Insert Function" button from Formulas > Insert Function

Figure 1

Clicking on the "Insert Function" button allows you to search for a function by typing a brief description of what you want, or by category.

For example, if you click on the "Insert Function" icon, the following window will appear:

Figure 2: Insert Function

Adding a particular function to your spreadsheet is then as simple as following the on-screen commands, but if you are ever in doubt, press the F1 key on your keyboard to access Excel's "Help" program.

In addition to financial functions - such as present value, future value, payment and internal rate of return - that are of use to financial professionals, Excel has many functions that are useful in cleaning up and reconciling large data sets, a task frequently encountered by many in finance. Some of these functions are:

  • EXACT - Checks whether two strings of text are precisely the same and will return either True or False.
  • LEFT, RIGHT or MID - Returns the characters from a text string given a starting position and length, such as the left side, middle of the text string or the right side.
  • TRIM - Will remove, with the exception of single spaces in between different words, all spaces in a text string.
  • TRANSPOSE - This function will convert a range of cells aligned vertically to a range of cells aligned horizontally, or vice versa.

The above list is a brief example of just some of the Excel functions. The best way to discover what functions are available and what can be done to make spreadsheets and data manipulation more efficient is simply to play around with Excel's "Insert Function" feature or to take one of Microsoft's courses or tutorials. If you browse by the Text or Lookup & Reference categories, you will have a great head start on using Excel's features to their fullest potential.

Look-Up tables
"Look-up" tables are usually a part of any financial spreadsheet or model. In Excel, the "Look-Up" function searches for values in a table based on a certain condition. For example, if you use on-the-run Treasury yields as a benchmark pricing for other bonds, a "Look-Up" table could be used to pull in the appropriate Treasury yield. (To help you learn this function we suggest you open up your own Excel spreadsheet and follow along.)

Figure 3: Look-up table

In the example above, Column F contains the "Look-Up" formula in each cell, and pulls the appropriate on-the-run Treasury yield from the "Look-Up" table to the left. In this example, the "Look-Up" table runs from Cell A2 to Cell B6. The first value within the "()" of the formula is a cell reference to the value in the "Look-Up" table that is searched for. This means that the value the "Vlookup" function will search for in the "Look-Up" table is a 10-year on-the-run Treasury. The second value in the "Vlookup" function is the name of the "Look-Up" table ("Look-Up" Tables must be named prior to using the "Vlookup" function and are described below). The third value in the "Vlookup" function is the column in the "Look-Up" table that is returned (there can be multiple columns in a "Look-Up" table).

Creating a "Look-Up" table is a simple, two-step process.

1. A "Look-Up" table must be sorted in ascending order by the first column.

  1. Using the cursor, highlight the entire data table.
  2. Click on "Data."
  3. Click on "Sort."

2. You must give the "Look-Up" table a name.

  1. Using the cursor, highlight the entire data table.
  2. Click on "Insert."
  3. Click on "Name."
  4. Click on "Define."
  5. Key in a name

Using this example to create your "Look-Up" table, sort cells A2:B6 using the instructions above. Now, when the same cells are highlighted, you can name the "Look-Up" table to "Benchmarks," which is the name included in the example above.

"Look-Up" tables have many uses beyond pulling in information for securities prices. However, we note that a simple securities pricing spreadsheet, as shown in the example above, can be made even more efficient by using the features offered by most pricing services, such as Bloomberg, which allow spreadsheets to link directly to live price feeds. For example, the Treasury yields in the benchmark "Look-Up" table above could be pulled directly into the table as a live link from a pricing service.

Looking Up a Value Based on Two Conditions
As demonstrated above, a "Look-Up" table can be used to pull in values based on a certain condition. The user must identify that condition and provide the column in which to find the value that is to be returned. (In the example above, the condition was the name of an on-the-run Treasury bond, and the column was the second column, which contained that particular bond's yield). There is a way to use a "Look-Up" table to return values when the column to be returned is not constant. In other words, based on certain variables, you might want to return the second, third, fourth, etc., columns of a "Look-Up" table. To do this, you must create a "Look-Up" table function within a "Look-Up" table function.

For example, the table below shows the value of mortgage servicing based on loan size and loan-to-value ratios (LTV). Let us call this table the "Servicing Table."

What if you wanted to pull the value of servicing into a spreadsheet for multiple loans with varying sizes and LTVs? In other words, the column number of the data you want to retrieve is not constant.

Figure 4: Spreadsheet example

You must first create a separate "Look-Up" table that identifies the column number for a given LTV. Let's create a "Look-Up" table using the following:

Figure 5: Table small

This "Look-Up" table is named "LTV" in the formula shown below. The servicing value "Look-Up" table (the original table in our example) is named "Servicing".

Now you can write a formula to find serving values based on both the variable of loan size and the variable of LTV as shown below.

Figure 6: Vlookup

As you can see from this example, the "Vlookup" function will work when using two different conditions. Keep this information handy, as we will use it in our next section.

The "Exact" Statement
The "Exact" statement (or function) is very useful in working with large sets of data, such as securities, where values within a spreadsheet vary by small amounts (for example, CUSIP numbers). You can use the "Exact" function to ensure that you are pulling in the actual value that you need, or to identify why you might not be able to find a value that you believe should be in the data set.

For example, when using the "Look-Up" function as described above, you must sort the "Look-Up" table by the first column in ascending order. The "Look-Up" function then searches for values in that first column. If a value in the "Look-Up" function cannot be found in the "Look-Up" table, the "Look-Up" function will find the next closest value - this is generally not good in financial spreadsheets, as exact figures are usually required.

For example, if the "Look-Up" function is searching for CUSIP number 912833WZ3, which is not found in the "Look-Up" table, but 912833WZ4 is in the "Look-Up" table, the "Look-Up" function will return the value in the specified column number for the 912833WZ4 CUSIP number. This is simply not the correct CUSIP number.

To avoid pulling the closest value when the actual value is not found, use the "Exact" function as shown below.

Figure 7: Exact function

The "Exact" function shown above says: If the value in Cell B2 is exactly the same as a value found in the "Look-Up" table called "Benchmark," then it will return the value in column two from the table. If there is no value in the "Look-Up" table called "Benchmark" that is exactly the same as the value in Cell B2, then it will return the words "not found".

Another common use of the "Exact" function is to figure out why you cannot find a value in a set of data when you "know" it is there. For example, you might be trying to reconcile two sets of data by CUSIP number. You know a certain CUSIP number is in both sets of data, but the formulas you have written aren't recognizing one of the CUSIP numbers as being the same as the other. Simply key the CUSIP number into both spreadsheets, and use the "Exact" function to let Excel tell you whether they are the same - there could be an unseen character in the data, such as a space before the CUSIP number that is causing the problems, which the "Exact" function will help you identify.

Figure 8: Exact function #2

As shown above, the "Exact" function is returning a "FALSE." Upon looking closer, the two CUSIPs are different. The CUSIP in A2 ends in "zero" while the CUSIP in A6 ends in "O."

Using Arrays
"Arrays" is a powerful feature in Excel that allows you to make calculations within large sets of data based on multiple conditions. For example, using "Arrays," you can sum the value of integrated oil stocks with a certain market cap within a large set of data that consists of stocks from many different industries and with many different market caps, and you can calculate the weighted average price of those stocks. Creating "Arrays" is a simple process, and the first step is to name the "Arrays."

1. Name the "Arrays."

  1. Using the cursor, highlight the entire set of data, including the column headers (the data must have column headers as they become the names of each "Array").
  2. Click "Insert" on the tool bar.
  3. Click "Name."
  4. Click "Create."
  5. Mark the "Top Row" check box only and click "OK."

Writing "Array" functions is also relatively simple. The examples below show "Array" functions that sum the market value of stocks within several industries by market capitalization and calculate the weighted average prices of those stocks based on the data set shown immediately below.

Figure 9: Array
Figure 10: Array chart

The "Array" functions are not as complex as they might look at first glance. First, we named the "Arrays" as described above, then we simply use a combination of "If" and "Sum" statements to return values based on the conditions that we set. The weighted average calculation is simply embedded in the "Array" function. The best way to gain expertise using "Arrays" is to practice. Once you master the use of "Arrays," they can be used very effectively to create many different efficiencies.

Important: The "{ }" brackets shown in the formulas are not keyed into the "Array" function, but once the "Array" function has been created, you must simultaneously hit Shift, Control and Enter on your keyboard to activate the "Array" - this creates the "{ }" brackets.

"Array" Tips:

1. "Array" names that consist of more than one word are automatically named with a "_" to separate each word. For example, market cap becomes "Market_Cap," as shown above.

2. The vertical ranges of each "Array" used in an "Array" function must be identical. In other words, one column of data in a table of "Arrays" cannot be longer than the others.

3. You are not limited to conditions as shown in the examples above. You can create up to seven conditions using the "IF" function. Each condition is separated by a "*", as shown above.

The Bottom Line
Microsoft Excel has many features and functions that can add value and be used to create great efficiencies. One of the best ways to learn about and master these features and functions is to use the "Insert Function" feature. Creating "Look-Up" tables can create efficiencies, and mastering the use of "Arrays" can create vast efficiencies. Finally, don't forget about the simple functions like "Exact" or "Trim," as they can save you hours of frustration when reconciling data.

Note: All images for this article were taken from Microsoft's Office Excel program. Microsoft holds all copyrights to these images.

Related Articles
  1. Mutual Funds & ETFs

    The ABCs of Mutual Fund Classes

    There are three main mutual fund classes, and each charges fees in a different way.
  2. Investing Basics

    5 Common Mistakes Young Investors Make

    Missteps are common whenever you’re learning something new. But in investing, missteps can have serious financial consequences.
  3. Mutual Funds & ETFs

    The 4 Best American Funds for Growth Investors in 2016

    Discover four excellent growth funds from American Funds, one of the country's premier mutual fund families with a history of consistent returns.
  4. Products and Investments

    A Guide to DIY Portfolio Management

    These are some of the pillars needed to build a DIY portfolio.
  5. Investing

    What Investors Need to Know About Returns in 2016

    Last year wasn’t a great one for investors seeking solid returns, so here are three things we believe all investors need to know about returns in 2016.
  6. Mutual Funds & ETFs

    The Top 5 Buffalo Funds for Retirement Diversification in 2016

    Discover the top five Buffalo Funds for retirement diversification in 2016, with a summary of each fund, including manager and performance information.
  7. Mutual Funds & ETFs

    How to Build Your Own Mutual Fund

    Here are some tips for building a mutual fund that may help pave the way to a strong performance.
  8. Investing Basics

    How to Become A Self-Taught Financial Expert

    Becoming a self-taught financial expert may not be as daunting of a task as it seems.
  9. FA

    The Basics of The Series 79 Exam

    Passing the Series 79 exam is usually necessary for anyone who wants to work in investment banking.
  10. Mutual Funds & ETFs

    Are the Most Popular Mutual Funds Your Best Bet?

    Learn about the dangers that come with investing in the most popular mutual funds and discover how investors can better fill out their portfolios.
  1. Does mutual fund manager tenure matter?

    Mutual fund investors have numerous items to consider when selecting a fund, including investment style, sector focus, operating ... Read Full Answer >>
  2. Why do financial advisors dislike target-date funds?

    Financial advisors dislike target-date funds because these funds tend to charge high fees and have limited histories. It ... Read Full Answer >>
  3. What licenses does a hedge fund manager need to have?

    A hedge fund manager does not necessarily need any specific license to operate a fund, but depending on the type of investments ... Read Full Answer >>
  4. Can mutual funds invest in hedge funds?

    Mutual funds are legally allowed to invest in hedge funds. However, hedge funds and mutual funds have striking differences ... Read Full Answer >>
  5. When are mutual funds considered a bad investment?

    Mutual funds are considered a bad investment when investors consider certain negative factors to be important, such as high ... Read Full Answer >>
  6. What fees do financial advisors charge?

    Financial advisors who operate as fee-only planners charge a percentage, usually 1 to 2%, of a client's net assets. For a ... Read Full Answer >>
Hot Definitions
  1. Super Bowl Indicator

    An indicator based on the belief that a Super Bowl win for a team from the old AFL (AFC division) foretells a decline in ...
  2. Flight To Quality

    The action of investors moving their capital away from riskier investments to the safest possible investment vehicles. This ...
  3. Discouraged Worker

    A person who is eligible for employment and is able to work, but is currently unemployed and has not attempted to find employment ...
  4. Ponzimonium

    After Bernard Madoff's $65 billion Ponzi scheme was revealed, many new (smaller-scale) Ponzi schemers became exposed. Ponzimonium ...
  5. Quarterly Earnings Report

    A quarterly filing made by public companies to report their performance. Included in earnings reports are items such as net ...
Trading Center