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. Investing Basics

    5 Tips For Diversifying Your Portfolio

    A diversified portfolio will protect you in a tough market. Get some solid tips here!
  2. Entrepreneurship

    Identifying And Managing Business Risks

    There are a lot of risks associated with running a business, but there are an equal number of ways to prepare for and manage them.
  3. Active Trading

    10 Steps To Building A Winning Trading Plan

    It's impossible to avoid disaster without trading rules - make sure you know how to devise them for yourself.
  4. Mutual Funds & ETFs

    Best 3 Vanguard Mutual Funds for Retirement

    Discover the top Vanguard target-date retirement funds with target dates in 2020, 2030 and 2050, and learn about the characteristics of these funds.
  5. Investing

    What’s the Difference Between Duration & Maturity?

    We look at the meaning of two terms that often get confused, duration and maturity, to set the record straight.
  6. Fundamental Analysis

    Top Private Equity Bargains for Your Portfolio

    Investing in private equity firms can lead to long-term profits.
  7. Fundamental Analysis

    Boost Your Portfolio by Adding 3 Turnaround Stocks

    Peter Lynch loves turnarounds. The stocks of these battered companies can offer incredible rewards if bought at the right time.
  8. Markets

    Are EM Stocks Finally Emerging?

    Many investors are looking at emerging market (EM) stocks and wonder if it’s time to step back in, while others wonder if we’ll see further declines.
  9. Markets

    What Slow Global Growth Means for Portfolios

    While U.S. growth remains relatively resilient, global growth continues to slip.
  10. Investing

    The Enormous Long-Term Cost of Holding Cash

    We take a look into how investors are still being impacted by the memory of the tech bubble and the advent of the last financial crisis.
  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 >>

You May Also Like

Hot Definitions
  1. Barefoot Pilgrim

    A slang term for an unsophisticated investor who loses all of his or her wealth by trading equities in the stock market. ...
  2. Quick Ratio

    The quick ratio is an indicator of a company’s short-term liquidity. The quick ratio measures a company’s ability to meet ...
  3. Black Tuesday

    October 29, 1929, when the DJIA fell 12% - one of the largest one-day drops in stock market history. More than 16 million ...
  4. Black Monday

    October 19, 1987, when the Dow Jones Industrial Average (DJIA) lost almost 22% in a single day. That event marked the beginning ...
  5. Monetary Policy

    Monetary policy is the actions of a central bank, currency board or other regulatory committee that determine the size and ...
  6. Indemnity

    Indemnity is compensation for damages or loss. Indemnity in the legal sense may also refer to an exemption from liability ...
Trading Center