How to use Microsoft Excel as a finance tool

Knowing Microsoft Excel is great.

Knowing finance is even greater.

But when you can do BOTH?

That’s when you call yourself Superman!

…because there’s nothing stopping you!

Today I am going to talk to you about Microsoft Excel so that you feel like you have Excel superpowers.

I am going to give you insanely actionable Excel tools to use for corporate and personal finance.

To kick things off I am going to start with an introduction of Excel.

Like so:

1. Introduction to Microsoft Excel

Look:

Most people think that Excel is overly complicated and would like some guidance on how to use it properly.

So it definitely pays off to go back to the old definition of what Excel actually does. Excel essentially does three things:

a. Calculations, which include things like adding a column or calculating a total monthly loan payment. We can add some numbers, or find a minimum of some values or simply subtract numbers from each other.

b. Data analysis. That’s when you look at raw data, you organize it in some way and then come to a conclusion. A very simple example of this is illustrated in the figure below (1). If we for example have many interest rates and we would like to find the lowest we can do that with Excel.

c. Excel also stores raw data, but that is out of the scope for this article.

Columns and rows

Excel is a two-dimensional grid where you have different columns horizontally and rows vertically.

Cells

The intersection between a column and a row is called a cell. If we for instance highlight the green cell to the right of the numbers and below the Add-cell, you can see that cell is called I3.

That is the heart of Excel because as you will see in this series you can reference a specific cell to make formulas.

Range of cells

A range of cells is when you choose a number of cells in order to make a calculation. In the example above we can highlight F3, G3 and H3 to select this range.

Worksheet

What about all the cells?

That is called a worksheet.

Sheet tab

A sheet tab is shown below:

What you can do here is to double click on it and change the name of the sheet.

CTRL + PgUp/CTRL + PgDn

To change between the different worksheets you can use the shortcuts CTRL + PgUp or CTRL + PgDn which is very useful.

Ribbons

When I use Excel I always have my Ribbons open so that I have easy access to the functions.

It looks like this:

Now, if you want to close the ribbons for whatever reason you can just right click on your mouse and select the “Minimize the ribbon”-option when you hover over the Home tab.

Quick Access Toolbar

We then have the Quick Access Toolbar which looks like this:

What the Undo button does is that it undo:s the last action and the Redo button undo:s the Undo.

If you have a particular task that you like and use often, you can put it in the Quick Access Toolbar by right clicking your mouse and scroll down to the “Add to quick access toolbar” option.

Scroll bars

Sometimes in this article series I am going to show you large tables. The way to navigate up and down in these is by clicking on the scroll arrows or pulling the scroll box.

Formulas and functions

There will be separate articles on this concept, but just to give you foretaste I will show you how it works:

Let’s say that I want to subtract 85 from 98. How do I go about doing this?

In order to do that you’ve got to know how to tell Excel that what it wants is a formula in I9.

If you just type an equal sign in I9, you’ve just to Excel that what is coming is a formula.

What you do next is that click on G9 for the first number.

Then you type a minus sign on your keyboard and click on H9 for the second number.

When you then hit Return you will get your answer in I9.

2.  Formulas and functions

In this chapter we are going to go a little bit deeper into the formulas and functions that we discussed last time.

First of all let’s repeat what I talked about in the last chapter. The way to tell Excel that you want to input a formula is by typing an equal sign (=).

An illustration:

For the sake of argument, let’s say that we have an interest rate of 8.4 percent (C3) and this has to be paid twice a year.

To figure out how much you have to pay each time we then have to divide the number in C3 with 2, which is the number in C5.

Instead of taking each cell the way we described above, many people type the numbers directly. This of course gives the same result, like so:

The problem comes if we want to update the rate. Then the C5 is updated automatically whereas C6 stays the same. You can see what I mean in this picture:

There are three reasons for why you want to update your data dynamically like that:

1. It’s faster to change the numbers directly in the cells.
2. If you’ve labeled your cells properly, you clearly see what the individual numbers are.
3. If there is a spreadsheet filled with direct edits and they are not labeled properly it’s difficult to edit.

To calculate the sum of different numbers, there’s a nifty shortcut to use: Alt + =

When you type the keyboard shortcut, Excel will guess which numbers you want to add together and there will be “marching ants” around these.

When you are done selecting the numbers you want, just hit ENTER and the formula will automatically update.

What you don’t want to do is to type =sum and then click on D2, D3, D4 and D5. The reason for this is that a range (as we have in Figure 8) is dynamic. This means that you can insert or remove numbers to your liking, whereas if you insert numbers into a formula with only clicked numbers, the final sum will not update.

We are going to look at a few more formulas and we will begin with an average or a mean:

What I do is that I type in the start of the formula and Excel then give you suggestions of what to choose based on what you have typed. Then you can select the corresponding correct formula and press the Tab key to select. As always Excel then guesses which numbers you want to choose.

We then want to calculate a loan function.

What I want is my yearly interest payment. First of all I click in C5 to activate the cell. Then I click the fx in the upper center of Figure 10. Then I begin to type “Loan payment” in the dialog box where it says “Search for a function”.

You will then get a list of a number of different functions that you can use, like so:

Then you have to read the description to figure out exactly what the formula does. In my case the description of PMT seems fine so I click OK.

I then get this dialog box:

Here it says that I’ve come to the Function Arguments box.

What is then an argument?

It’s a mathematical term of the individual parameters within a formula. In my example there are three different arguments that I want to insert and they are highlighted in bold.

The first argument being the rate so I go back and click on C3 to insert the rate.

The next argument is the number of periods and because I have annual rates in my example, I simply click on C4 for the number of years.

The last argument is the Present Value which in our case is how much the loan is worth right now. So I click C2, like so:

What you see to the right of the dialog box in Figure 13 are the actual numbers of the cells that I’ve entered. Down below you will see the unformatted result of the formula.

Down to the left I get the formatted result which has parentheses. These are financial numbers so parentheses mean that the number is negative. The reason for this is that if you have borrowed that amount of dollars at that rate you will get a negative number because the money will come out of your wallet.

One other thing that I will talk about is screen tips. When you have done these numbers for a while you will be fluent. Once you are fluent, you can just type =PMT( and you will see that the formula pops up in the screen:

These screen tips allow you to see which value to enter next. You can between the different values by typing a comma on your keyboard.

3. Math Operators and Orders of Operation

I now want to turn your attention math operators and orders of operation. In this part of the article I will talk about all operators below:

In Figure 14 you can see how you can type the different operators.

Let’s see some math examples:

Adding

If you want to add two numbers together the easiest way is to type Alt + = in the result cell. In my example that is B5. That way you will get a formula of =SUM(B3:B4) and if you hit enter Excel will calculate that addition for you, like so:

Subtracting

To subtract a number from another I type an equal sign in C5 and then I use the mouse to click in C3 (or the up arrow twice on my keyboard). Then I type a minus sign on the number pad and click in C4:

The result is then shown in C5.

Multiplication

There are two ways to multiply numbers in Excel. The most common is to type an equal sign and then click the individual numbers, like so:

You can also use the PRODUCT function, like so:

Division

To divide two numbers we first type an equal sign and then click on the individual numbers. In this case E3 is called the numerator and E4 is called the denominator:

If the numerator is greater than the denominator, the result will be a number that is bigger than 1 (E5):

Similarly, if the nominator is less than the denominator then the resulting number will be less than 1 (F5):

 Exponent

If I want to take the number in cell G3, which in my case is 5, squared, I first have to type an equal sign. Then I have to click in G3 to get the number in G3. Then I have to type Shift + 6 to get to the caret symbol. Then I can click in G4:

That means that the base is 5 and the exponent is 2. The result is then shown in cell G5, like so:

Logical formulas

Are two numbers equal?

I will now show you how to use logical formulas. If I want to check if two numbers are equal, I click the first cell and then the second:

It may come as a surprise, but when I hit Enter now I see that I get the Boolean value “False” in B5.

Why is that?

It comes from that Excel does not always show you everything. In this case if I click in cell B3, you can see that the value is not 5, but really 5.02:

How can that be?

This comes from the number formatting in Microsoft Excel. If you look up in the formula bar you see that the number in B3 is actually 5.02 and not 5.

How do you go about changing this?

The answer is that you change the number of displayed decimals simply by clicking the Increase Decimals or Decrease Decimals box like so:

Is one number greater than another?

I then want to check if a number is greater than another number.

Is the value in C3 greater than the value in C4? I do that with a greater than symbol.

And of course if I hit Enter:

I get the Boolean value TRUE in cell C5.

Order of operations

The next thing that I will talk about is the order of operations. In particular I will focus on the question if 2+2*4^2 is 256 or 34:

If I calculate from left to right, the first calculation is 2 + 2 which is 4. Then the next is times 4 which would make 16. If I then take that number squared I would end up with 256.

But that is not the way calculations work. The parenthesis always come first followed by exponents, multiply and divide follow and addition and subtraction come last.

If we then type in the formula in the example into Excel we will see this:

So Excel knows in which order to do the calculations and it does it automatically.

The correct way of calculating is first to look for any parenthesis, but in this case I don’t have any. Then I see that I have look at exponents where I have 4 raised to the second. This turns out to be 16. I then have to multiply that number with 2 which gives 32. Finally I have to add 32 with 2 which gives a result of 34.

There is a neat acronym to memorize the order of operations:

Please Excuse My Dear Aunt Sally.

If, on the other hand, I want to have 256 as a result I have to put parenthesis around the number, like so:

If I hit enter I will get the result 256 in cell C9.

Evaluation of formulas

In this section I want to show you how to evaluate a formula.

What I do is that I go up to the formulas ribbon where I first click “Formulas” and then “Evaluate formula” as shown in the picture:

When I click on “Evaluate Formula” I get a dialog box, like so:

Then when I click on “Evaluate” Microsoft Excel will calculate the underlined value for me. This comes in handy when you want to check your formula and you don’t know the order of operations.

4. Number formatting

The next thing that I will talk about is number formatting. The reason for me to do this is there are a lot of people who get it wrong and that we want to avoid.

If I for instance want to change a naked number to a number with a comma and two decimals I first highlight the numbers, like this:

If I then click “Ctrl” and “1” at the same time I open the Format Cells dialog box. Now if I click on “Number” I will get this:

Here I can decide how many decimals I want to show. I this case I go for zero and I click the box that says “Use 1000 Separator (,)”:

Next thing that we want to look at are the numbers in A3 to A5. They all say $67.35 but if I look closer I see that what it really says is $67.351383489, like so:

If I want to get rid of the number formatting in A3 to A5, I type Ctrl + Shift + ~ and I get get this result:

If I then jump to Currency in column D, you can see that by pressing Ctrl + Shift + 4, I get the currency formatting that I want:

If you then check with Ctrl + 1, you can see that the Format Cells dialog box has indeed chosen Currency.

What is the difference between Currency and Accounting?

If I highlight cells E3 to E5 and click on Accounting in the format drop down-menu, like so:

What happens when you click Accounting is that all the numbers that you are working with will align so that you can compare them directly.

I will be talking about loans and cash flow later on where you will have to do math on those dates.

Let’s say thatI have a loan that is due on a specific date and today is another date and I want to know how many days the loan is behind:

In the cells there are dates written, but behind the scenes Excel consider these as numbers. That means that there is an actual number there where Excel can do calculations.

You can check which actual numbers Excel uses by changing the formatting drop down to Text. First I highlight cells F7 and F8 and then change to Text:

You can then see that Excel use the numbers 42675 and 42712. If you want to know the difference between those you can write a formula:

First of all I’m typing an equal sign, then I click on the later date (F8), I click on the earlier date (F7) and hit Enter.

The result is 37, like so:

If you get a date as a result, it’s because the formatting is set to date. Just change to General (Ctrl + Shift + ~) and you will see the number.

The keyboard shortcut for today’s date is Ctrl + ; so if I for instance would like to add today’s date in cell F3, I first activate the cell by clicking in it and then type Ctrl + ; like so:

If I then want to increment my dates I click in the bottom right corner of cell F3 to get the crosshairs. Then I can simply drag it down:

If I then click on the Fill options handle I can choose to increment months or years instead of days. That is going to be handy when we want to analyze cash flows.

If we now want to know which numbers Excel use behind the scenes, you can highlight the dates that I just used (F3 to F5) and click General:

In Figure 49 I went from a date to a number so let’s do the opposite instead. I cells G3 and H3 I have written 1 and 2:

How do I make these numbers into dates? I type Ctrl + 1 to bring up the Format Cells dialog box. Here I choose the option “Date” and the format 3/14/01:

The result then becomes 1/1/00 where 00 is short for 1900:

As a sidenote I will now discuss how dates and text are differently treated by Microsoft Excel:

By default, Excel align numbers to the right and text to the left. From Figure 53 it is obvious that our dates are aligned to the left. That means that I have made something wrong in the formatting:

What has happened here is that we have started the date in cell C3 with an apostrophe and in C4 I’ve formatted the date as “Text” in the number formatting.

The reverse of converting dates to numbers can also be achieved. I have some numbers in cells I3 to I5:

Then I go to the number formatting drop down menu and choose “Short Date”:

Then I click and I get these dates out:

You can also find the maturity date of a loan that is issued on a specific date:

In this case the Loan Issue Date is in cell C3 and the Loan Length In Days is in cell C4. When I then type =C3+C4 I get the maturity date in cell C5.

5. Percent Number Format

In this section I will talk about Percent Number Format, Percent and Percent Change.

One of the biggest mistakes made in Excel with the Percent Button:

If I then type 0.02 in cell B2 and I click the Percent Button above I get the following:

The 0.02 is then transformed into 2%. On the other hand if I click the Percent Button with the value 2 highlighted in cell C2, I get the following:

That is an example of Number First, Then Format. The other way to do it is by first formatting and then typing. Here I highlight the cells B3 and C3 before I click the Percent Button:

If I then type the number 3 in cell B3 I get the result that I want (which in my case is 3%).

But when I type 0.03 in cell C3 I also get 3.00% so it doesn’t matter if you use 3 or 0.03:

I don’t know why this has been programmed this way, but it’s the way Microsoft Excel works.

Sometimes you have just one interest rate and then you can just type for example 3% in cell B4:

You will then get the result 3.00% when you hit enter.

Then I am going to talk about Percent and Percent Change. A Percentage is calculated as a Part of the Total. In Figure 66 I am comparing the Cost Of Goods Sold to the Total Revenue:

The result in cell B9 is of course the following:

Then we have the concept of Percent Change. This is calculated as (End – Beginning)/Beginning:

The result is of course as follows:

Conclusion:

Microsoft Excel is a very powerful tool for doing calculations and data analysis. It is very often used in finance and we will dive deeper into the topic in later articles.

Today I have only gone through the basics of Microsoft Excel to whet your appetite.

I hope you come back for more.

(This article is highly inspired by the great work of ExcelIsFun on Youtube. You can find part 2 of this series here.)

If you liked this post and/or have comments please leave comment below.