COUNTIF

COUNTIF is used to count the cells that match specified criteria. For example, let's say you have sold $6,160 worth of different products on a particular day, as shown in Figure 6.1. You can create this sheet yourself or a template can be found, along with the accompanying models to the rest of the screenshots in this book, at www.plumsolutions.com.au/book.

You'd like to know how many (in terms of number of products) you've sold of each product: books, CDs, and DVDs. Follow these six steps:

1. In cell B18, use the Insert Function dialog box to find the COUNTIF function. (Simply begin typing ( and then Control+A, or hit the fx symbol next to the formula bar.)
2. Highlight the product names in column A for the range, and the word “Book” in cell A18 for the criteria. Do not type in the word “Book”, as this is poor modelling practice. It needs to be linked to the input cell so that we can then copy down the formula.
3. Your Insert Function dialog box should look like Figure 6.2.

   

4. Press OK. The answer is 5. Your formula should look like .
5. Change your formula to use absolute referencing as follows: and copy the formula down the range. You don't want absolute referencing on the criteria, because it needs to change its row references to CD and DVD as you copy down the formula.

   As a shortcut, highlighting A2:A13 in the formula bar and pressing F4 will set absolute references $BA$2:$A$13. Remember that each time you press F4 it will scroll through the various absolute, relative, or mixed cell referencing options.

6. Copy the formula down, and add a total at the bottom. Your sheet should now look something like Figure 6.3.

SUMIF

SUMIF is similar to COUNTIF but it sums rather than counts the values of cells in a range that meet given criteria. SUMIF can be used in place of a PivotTable, and doing so is preferable as it uses less memory and (most importantly) because SUMIF formulas are live links. PivotTables do not update automatically, which can cause errors in models if not refreshed correctly. Therefore, although quicker to build, PivotTables are sometimes avoided in favour of a SUMIF function in financial modelling.

1. Let's say that you'd like to know how much (in terms of dollar value) you've sold of each product: books, CDs, and DVDs.
2. Select cell C18. Find the SUMIF Insert Function dialog box or start typing (. Because there are several fields required for this function, I'd recommend you use the Insert Function dialog box, as it's easier to find your way through the function.
3. The items you are adding together go in the Range field, the criteria you are looking for in that range go in the Criteria field, and the numbers you want to sum together go in the Sum_range field.
4. Your Insert Function dialog box should look like Figure 6.4.

   

5. Press OK. The answer is 2,430. Your formula should be .
6. Add in the absolute referencing, and copy the formula down the range. Your formula should be . You don't need absolute referencing on the criteria (A18), because it needs to change its reference as you copy down the formula.
7. Add a total. Your sheet should now look like Figure 6.5.

We've now got a summary report at the bottom, showing us how much we have sold in terms of number and dollar value.

Be careful. If your Range and your Sum_range don't match up, your result will be wrong. For example, will give you an incorrect result without warning! This is a very easy mistake to make, and quite common in financial modelling. You can see in Figure 6.6 that the totals are not the same, because the SUMIF function is picking up the incorrect range. This is the same for COUNTIF functions, but it is not a problem with the SUMIFS, as the formula will return an error and therefore you will notice the error straight away, unlike the SUMIF function.

AVERAGEIF

The AVERAGEIF function will average values in the specified range that match specified criteria. This is similar to SUMIF.

1. Let's say that you'd like to know the average in dollar terms of each product sold. Select cell D18. Find the AVERAGEIF Insert Function dialog box or start typing .
2. The items you are interrogating go in the Range field, the criteria you are looking for in that range go in the Criteria field, and the numbers you want to average go in the Average_range field.
3. Press OK. The answer is 486. Your formula should be
   
4. Use absolute referencing, and copy the formula down the range. Your formula should be
   

You don't need absolute referencing on the criteria (A18), because it needs to change its reference as you copy down the column.

COUNTIFS

COUNTIFS (along with similar functions such as SUMIFS and AVERAGEIFS) is a relatively new function that was introduced in later versions of Excel. COUNTIFS applies criteria to cells across multiple ranges and counts the number of times all criteria are met. For instance, COUNTIFS could answer how many of each product had been sold to a particular customer.

1. In row 16, create a filter cell to show which customer we want to show in our summary. So, in cell B16, enter the value Jenny. By linking the COUNTIFS function to this cell, we can change it in the future if we want to.
2. In cell B18, use the Insert Function dialog box and find the COUNTIFS function, or simply begin typing if you prefer. If you are using the Insert Function dialog box, it should look like Figure 6.7.

   

   As you can see, we are invited to keep adding more ranges and criteria to the function COUNTIFS, which was not possible in COUNTIF. Use absolute references for all ranges in your formula, with the exception of Criteria1 in cell A18. The reference to the word “Book” will change to “CD” and “DVD” as the formula is copied down.

3. The formula should be
   

   The result should look like Figure 6.8. The completed version of this exercise can be found, along with the accompanying models to the rest of the screenshots in this book, at www.plumsolutions.com.au/book.

SUMIFS

Like the COUNTIFS function, the SUMIFS function applies criteria to cells across multiple ranges and sums specified columns for which criteria are met. For instance, SUMIF tallied up the value of all products, whereas SUMIFS could add up only those purchased by Jenny.

1. In cell C18, use the Insert Function dialog box and find the SUMIFS function, or simply begin typing . Match Criteria_range1 (column A) with a value that appears in the range (the value “Book” in cell A18). Similarly, match Criteria_range2 (column B) with a value that appears in the range (the value “Jenny” in cell B16).
2. The result in cell C18 will be $660 and you can copy it down the range to the result shown in Figure 6.9.
3. The formula should be
   

As you can see, we are able to append one or more ranges and criteria to the function SUMIFS in the form Criteria_range, Criteria which was not possible in SUMIF. When using the SUMIFS function, we can appreciate the value of using the Insert Function dialog box, as it helps to see which criteria match which criteria range.

AVERAGEIFS

AVERAGEIF averages the value of all products, whereas we could use AVERAGEIFS in a similar way to average only those products purchased by Jenny.

1. In cell D18, use the Insert Function dialog box and find the AVERAGEIFS function, or simply begin typing . Enter in the relevant fields to achieve the result in Figure 6.10.

   

   As with COUNTIFS and SUMIFS, the AVERAGEIFS function also allows you to append one or more additional criteria to it in the form Criteria_range, Criteria.

2. The formula should look like this:
   

   As can be expected, the result of this formula will be $660, as there is only one instance of sales that were made to Jenny. Now copy the formula down to cells D19 and D20. D20 returns an error, because Jenny did not buy any DVDs, so we need to use the IFERROR function to suppress this error. Go back to cell D18 and put the IFERROR function around your formula. The formula should look like this:

   

Then copy the formula down the range. Cell D20 should now show a zero instead of the #DIV/0! error.

To make this table more complete, we need to put a total in cell D21; however, neither the total of the range ($1,540) nor the average of the range ($513) makes sense. In cell B21, we are showing the total number of purchases by Jenny, and then in cell C21, we are showing the total amount of her purchases. Therefore, it makes sense to show the average of all purchases by Jenny in cell D21, which is $807. This cannot be done by copying the function down, so we need to create a new AVERAGEIF function, as shown in Figure 6.12. The formula which should look like this:

Now, try changing the word “Jenny” in cell B16 to “Faisal” and watch the numbers change.

Filtering IFS Functions by a Variable Value    Note that instead of filtering to show one customer, we could, for example, show only those products that exceed $50. This is not particularly easy using IFS functions (i.e., SUMIFS, COUNTIFS, and AVERAGEIFS), but it is possible.

Let's remove the word “Jenny” and instead add the minimum $50, and change the label to show that the cell now contains only sales that are greater than $50, as shown in Figure 6.13. Note that we are now showing the filter in column C, because it lines up better with the sales amount, which is also in column C. We can now change the original COUNTIFS function to only aggregate the values greater than $50. It is an idiosyncrasy of all these IFS functions, however, that we cannot easily link when using the greater than (>) or less than (<) symbols. The function will force us to use inverted commas, and enter the filter value directly into the function, rather than linking it to an individual cell on the worksheet. Your formula in cell B18 will look like this:

This is not very good financial modelling practice, however. Any decent modeller would want to allow the user to change the filter minimum value. We therefore need to use a tricky workaround involving the handy ampersand (&) symbol. We'll need to replace with the phrase instead, and therefore the minimum filter will change according to the value in cell C16. Your formula in cell B18 should now look like this:

Copy it down and then try the same technique with the SUMIFS function. The solution for the SUMIFS is shown in Figure 6.13.

Similarly, the AVERAGEIFS function can be completed in the same way and should look like the solution shown in Figure 6.14. As in the last example, the formula to calculate the total for the Average needs to be the average for all Sales greater than $50.

Note that dates can also be used as input criteria for these IFS functions.

VLOOKUP

VLOOKUP stands for “Vertical Lookup”. It can be used anytime you have a list of data with the key field in the leftmost column, and it is by far the most commonly used form of LOOKUP formula. It's a favourite amongst Excel users, but it's not the most robust of functions as we'll soon see.

Let's say you have a shopping price list like in Figure 6.16. You can create this sheet yourself or a template can be found, along with the accompanying models to the rest of the screenshots in this book, at www.plumsolutions.com.au/book.

In cell C12, we'd like to find out how much it will cost to buy four oranges, using a VLOOKUP.

How to Create a VLOOKUP    The following is an 11-step process for creating a VLOOKUP:

1. Find and select the VLOOKUP function. Using the Insert Function dialog box is probably going to be the easiest for VLOOKUPs, as there are four parameters it needs.
2. The first parameter is the criteria we are testing, in this case, the word oranges, so enter B12 in the first field.
3. The next parameter is the table, which contains the data you want to reference. This is where it gets tricky. The criteria you are looking for must always be in the far left-hand column of the data table you are referencing in the table array. So, in this case, the data we are referencing will be the range B7:C10. Figure 6.15 is what your Insert Function dialog box should look like so far.

   

4. Now, if you were going to copy this formula down the page, you would have to use absolute referencing for the table array, or use a named range. Let's create a named range here.
5. Press Escape to cancel out of the Insert Function dialog box and create a named range for the whole table. Highlight the range B7:C10 and type over the name box in the top left-hand corner of your screen with the word FruitList, and press Enter. We can now use this in the VLOOKUP.

   Now that we have used a named range, we won't have to worry about absolute referencing in our VLOOKUP formula. See Figure 6.16.

   

6. Go back into the VLOOKUP Insert Function dialog box again, re-reference the lookup value to B12, and either select the table array in the second field box or simply type in FruitList. Make sure you have spelt it the same way as your named range! If you can't remember what you called it, use the F3 shortcut as shown in Figure 6.17.

   

7. The third field, called Col_index_num, tells Excel in which column the value is found. We need to tell the function which column in the table array we want it to return. In this case, we want it to tell us the price. So, counting from the far left-hand side of the table array (starting in column B), we want it to return to the second column. So, we enter a number Col_index_num field in the Col_index_num field.
8. The optional fourth parameter tells Excel if a close match is okay. If you want an exact match, enter zero; otherwise, leave it blank. You may also enter TRUE or FALSE, but typing a zero or leaving it blank is quicker. Click OK to complete the formula.
9. The Insert Function dialog box should look like Figure 6.18 and the formula should now be
   

   

10. Now, try changing the word oranges to apples. The price should change.
11. Last, we need to multiply the price by the number of items we are purchasing. Change your formula to
    

#N/A Errors in VLOOKUPs    While this example worked out perfectly, when most people use VLOOKUP, that they are usually matching up lists that came from different sources. When lists come from different sources, there can always be subtle differences that make the lists hard to match. This means you will get a #N/A error if the VLOOKUP function cannot find a match. Here are two examples of what can go wrong and how to correct the error.

Example 1. One list has dashes, for example, and the other list does not. The first time that you try the VLOOKUP, you will get #N/A errors. To remove the dashes with a formula, use a find and replace. Replace with nothing (i.e., leave the field blank). Your data will now match and the formula works.

Example 2. One list has a trailing blank space after the entry. This one is subtle because you can't see it with the naked eye, but it is very common. In fact, I've spent many happy hours searching for an invisible space! When you initially enter the formula, you will find all of the answers are #N/A errors. You know for sure that the values are in the list and everything looks okay with the formula.

One easy way to check is to move to the cell with the lookup value. Press F2 to put the cell in edit mode and you can see that the cursor is located one space away from the final letter. This indicates that there is a trailing space in the entry.

One way to solve the problem is to use the TRIM function. Entering as part of your VLOOKUP formula will remove leading spaces and trailing spaces, and will replace any internal double spaces with a single space. In this case, TRIM works perfectly to remove the trailing space. The formula would look something like this:

Improving VLOOKUPs with MATCH    The best way around this is to make the 2 in your VLOOKUP a formula instead of a hardcoded number. One way of doing this is to nest the VLOOKUP with a MATCH function. In the following eight steps, we will create the MATCH formula in a separate cell and then replace the 2 with the MATCH formula.

1. Using the VLOOKUP example above, test the problem by inserting or deleting a column in our sheet. You will see that the value in your VLOOKUP is now incorrect.
2. Now go to another spare cell, say cell C3, and create a MATCH formula that will find the word Price in the range B6:C6 and tell us its position in the range.
3. Your Insert Function dialog box should look like Figure 6.19.

   

4. By putting a 0 in the Match_type range, you are asking it to return you an exact match instead of a close match.
5. When you hit OK, your formula should be and the result should be 2.
6. Go into the MATCH formula bar, highlight the entire formula (except for the = sign), and press Control+C to copy it. Hit Enter, or Escape. Now, go back to your original VLOOKUP, and replace the 2 in your VLOOKUP formula with this entire MATCH formula by highlighting the 2 within the formula and pasting the MATCH formula over it by pressing Control+V. Hit Enter.
7. Your formula should look like this:
   
8. Test that this works, by inserting or deleting a column. You can now clear the formula in cell C3, as this is no longer necessary.

Another way of making a LOOKUP formula more robust would be to use the COLUMN function for a VLOOKUP or ROW function for an HLOOKUP instead of MATCH. This will automatically return the range's column or row reference. Using this, the formula instead would be

(The -1 is required because the range is starting from column B.)

HLOOKUP (Horizontal Lookup)

HLOOKUP works in exactly the same way as VLOOKUP, except that the data is arranged horizontally instead of vertically. See Figures 6.20 and 6.21 for examples.

HLOOKUP is subject to exactly the same issues as the VLOOKUP and works in exactly the same way, except for the orientation. If your source data in the range is orientated vertically, use the VLOOKUP, and if it is horizontal, use the HLOOKUP.

LOOKUP Function

The LOOKUP function is much simpler than either the VLOOKUP or the HLOOKUP, and it has the added advantage of being able to have the results column or row either to the left or right of the criteria column or row—a huge advantage. However, the data must be sorted in alphabetical order or it won't work. This does limit its usage significantly, and it is for this reason that it is far less popular than the VLOOKUP or HLOOKUP formulas.

It's worth knowing how it works, though, so here's a brief example. Let's say you have a tiered pricing structure, as shown in Figure 6.23. You can create this sheet yourself, or a template can be found along with the accompanying models to the rest of the screenshots in this book at www.plumsolutions.com.au/book.

1. Select LOOKUP from the Insert Function dialog box.
2. There are two types; choose the first option, as shown in Figure 6.22.

   

3. Similar to an HLOOKUP or VLOOKUP, the criteria being tested go in the first field.
4. The column (or row) containing the criteria goes in the second field, and then the column (or row) containing the result goes in the last field. See Figure 6.23.

   

5. Copy the LOOKUP function down and multiply the price by the total amount to complete the pricing table, as shown in Figure 6.24.

   

Note that this works exactly the same whether the data is shown vertically or horizontally. Importantly, unlike a VLOOKUP or HLOOKUP, it does not matter whether the result data is to the left or right, above or below the lookup data.

However, you would use a LOOKUP only for numerical values where the numbers are sorted and you want a close match, not an exact match. You would not use a LOOKUP function for text, as shown in the last fruit shopping list VLOOKUP example, because it will only return a close match, and this is dangerous. This makes the LOOKUP function almost useless in many cases where a LOOKUP is required. The only time a modeller would use a LOOKUP is when he specifically wants a close match. See Table 6.1 for the advantages of the different LOOKUP functions just described.

Using INDEX and MATCH to Create a More Robust Formula

Let's say that you are referencing a table in another file. Your co-worker keeps inserting and deleting rows and columns. The VLOOKUP solution as previously described may work, but with large tables, an INDEX and MATCH formula combination will be more efficient.

Figure 6.25 contains the source data, showing we need to pick up the staff amenities costs for Geelong. You can create this sheet yourself or a template can be found, along with the accompanying models to the rest of the screenshots in this book, at www.plumsolutions.com.au/book.

You could simply reference the cell F5, using the formula , but this will not help you if your file is closed, and someone else changes the referenced table. Let's say your co-worker inserts a column while your model is closed. When you open your file again, it will still reference F5, but Geelong has moved to column G, so the formula in your model will be wrong!

By using a combination of an INDEX and MATCH formula, we will be able, in 15 steps, to specify the exact coordinates of the required value, even if its position in the table changes.

1. In cell B12, insert the word Geelong, and in cell A13, insert the phrase Staff Amenities. These cells will become input variables that we will link our formula to.
2. Now let's find the coordinates of the required data. Start by creating a MATCH formula in a spare cell that will find the word Geelong and tell us its position in the range of city names.
3. Your formula should be , and the result should be 6. Note that you could type the word Geelong directly into the formula, but linking it is considered best practice in financial modelling.
4. Now do the same thing for the expense types. In another spare cell, create a MATCH formula that will find the phrase Staff Amenities and tell us its position in the range of expense types.
5. Your formula should be , and the result should be 5.
6. Now create the INDEX formula that will return the value that you specify. Note that when you use the Insert Function dialog box, it will ask you which argument list you wish to use. Select the first one. See Figure 6.26.

   

7. Once you are in the Insert Function dialog box, enter the array that is the whole range of data, starting from cell A1. Then hardcode in the row number (5 in this case) and the column number (6 in this case). See Figure 6.27.

   

8. Add the absolute referencing, so your formula should be , and the result should be $23,021.
9. This formula will work fine, as long as no one inserts or deletes rows or columns. We know that hardcoded numbers are not good financial modelling practice and so in order to make it completely robust, we need to replace the 5 and 6 with the results of the MATCH formulas. We are going to turn this into a nested formula, just like we did in the last section, where we nested the VLOOKUP and MATCH functions.
10. Go back into the first MATCH formula bar (the one that returns a 6), highlight the whole thing (except the “=” sign), and press Control+C.
11. Go into the INDEX formula and replace the column reference with this formula. Highlight the 6 and press Control+V.
12. Now do the same for the row reference. Go into the second MATCH formula bar, highlight the whole thing (except the “=” sign), and press Control+C.
13. Go into the INDEX formula and replace the row reference with this formula. Highlight the 5 and press Control+V.
14. Don't forget to add in some absolute referencing. Your formula should now look like this:
    
15. Test the robustness of your formula by inserting or deleting rows or columns. You can now cut this formula to another file, and it will work in the same way.

In Figure 6.28, we can see that named ranges have been inserted into the original data, and used in the formula, which has been cut and pasted to a separate file. Even if the data file is changed while the report file is closed, the formulas will still remain accurate.

Using an OFFSET to Model Cash Flow

So now that you know how to do an OFFSET function, let's see how it's used in a more practical context. Let's say that you have monthly sales data, but the terms of payment are one month, as shown in Figure 6.31. This means the cash is received one month after the sale is made.

You can create this sheet yourself or a template can be found, along with the accompanying models to the rest of the screenshots in this book, at www.plumsolutions.com.au/book.

Let's create a Cash Receipts line in row 5 using the OFFSET function, which will delay the cash receipts by the number in cell B1. We have indicated that this number can be changed by formatting the cell as an input using the Styles menu.

1. Start in cell C5 and create an OFFSET function that uses cell C4 as a reference point and offsets the number of columns by the number of months in cell B1.
2. Leave rows blank, as you do not wish to move rows.
3. Make the offset number of columns a negative number, because you wish to return the cell to the left, not the right.
4. Your Insert Function dialog box should look like Figure 6.31, and the formula in cell C5 should be .
5. Copy this formula across the row.

Nesting OFFSET and COLUMN Formulas

The formula that we have created works when the terms are one month, but if you change the number of months in cell B1 to 2, it will pick up the word Sales in cell A4.

Copy the formula all the way across the row, and B5 returns a #REF! error value, as shown in Figure 6.32. This is a very common problem when using the OFFSET function; we need to edit the formula now to make this model more robust.

One method is to suppress the errors and text. Note that we have three possible value types being returned by this OFFSET:

1. Error (in cell B5)
2. Text (in cell C5)
3. Number (in the rest of the cells in row 5)

If we suppress only the error using the IFERROR function, then this will allow the text. We could create a complex nested function that would suppress both the error and the text, like this:

But a simpler way would be to return the value only in the case of a number (hence, specifying the value we want, not the values we don't want!). Your formula should look like this, as shown in Figure 6.33:

Test the formula by changing the value in cell B1 and check that the result is what you expect. If you are expecting no delay in cash receipts, you can enter a zero in cell B1, and the formula will still work.

Alternatively, you could also nest a COLUMN function to give a similar result. A COLUMN function will return the column reference of the formula. For example, COLUMN(B3) returns the value 2.

We need to add a formula in front of our OFFSET function to say that if the number of months in cell B1 is greater than the column position, it should return a zero. Your formula would look like this:

See the sheet named “Fig 6.33a” in the supplementary Excel files for this chapter for a working demonstration in Excel.

Using a FORECAST or TREND Function

The FORECAST function in Excel predicts or forecasts data based on historical data, using the linear trend. These functions are a simple way of performing regression analysis in Excel and are useful to include in forecasting models. The TREND function almost always gives the exact same result (but uses the least-squares method instead of the linear trend), so for our purposes, the two can be used interchangeably.

Select the FORECAST (or TREND) function from the Insert Function dialog box.

Basically:

• X is the date or number for which you want the numbers to be forecast.
• Known_ys is the known historical data.
• Known_xs is the known historical dates or numbers.

Let's say, for example, that you have the following historical data available, and you need to provide a forecast for July 2020. You can create this sheet yourself or a template can be found, along with the accompanying models to the rest of the screenshots in this book, at www.plumsolutions.com.au/book.

If we were to quickly chart these numbers, we could add a linear trend line to visually see what we expect the forecast to look like. Create the chart by highlighting all the data and then selecting Line Chart from the Insert tab on the ribbon. To create the linear trend line, right-click on the series and select Add Trendline. See Figure 6.34.

We can see from the trajectory of the trend line that, based on historical data, the sales in July 2020 should be somewhere between 700 and 710. The formula tells us the slope, which is what the FORECAST function uses to calculate the projections. See Figure 6.35.

This is exactly how the FORECAST function calculates forecasts. It looks at the historical relationship between the X-axis and the Y-axis, and projects based on these numbers.

1. To create the FORECAST function, select cell B8.
2. Bring up the Insert Function dialog box. The inputs for our formula fields will be:
   • X: A8 (Jul 20) as the date for which we want to forecast.
   • Known_y's: B2:B7 (650 to 690) as the historical data we know already.
   • Known_x's: A2:A7 (Jan 20 to Jun 20) as the corresponding years for the numbers we know already.
3. Your Insert Function dialog box should look something like Figure 6.36.

   

4. The forecast sales for July 20 is 706, and the line chart that you created earlier will show forecasted values that match the trend line exactly, as shown in Figure 6.37.

   

Creating a Forecast with the Forecast Sheet Tool

A new tool, introduced in Excel 2016, was the Forecast Sheet. By selecting the data and using the tool, it automatically creates the forecast for you, without having to build the functions as described in the previous section.

To try it out, select the historical data or click within the table and, as shown in Figure 6.38, on the Data tab, in the Forecast section, select the Forecast Sheet button. This will bring up the Create Forecast Worksheet dialog box showing a preview of what the chart will look like. Expand the options button in the bottom left-hand corner of the chart, and here you can change some of the options such as the chart type, confidence interval, range, and seasonality.

Press OK, and a new sheet is created with the chart, and new formulas have automatically been created as shown in Figure 6.39.

Note that the FORECAST.ETS function is not available in previous versions of Excel, so you should only use this tool if the model users are on Excel 2016 or later.

Using a Nested CHOOSE Formula with Dates

Let's say you'd like to be able to enter in any date and have a formula that tells you, in words, the day of the week it is.

1. Go to cell B9 and enter your date. This could be your birth date, or you could create a formula with today's date by typing . Every time you open the model, it will show the current date. Note that will show the date and the time.
2. The formula will return the day of the week as a number, with Sunday being the first day of the week. Therefore, Sunday=1, Monday=2, and so on.
3. Go back to your previous formula in cell A9, and turn it into a nested formula by replacing the hardcoded 2 with the WEEKDAY formula.
4. Your formula should be:
   

Now, each time you open the model, the formula in cell A9 will show the day of the week. See the next section for more date options.

Handy Functions

Many financial models require specific dates or sections of dates for analysis. Excel has a fantastic collection of functions to isolate various parts of dates and calculate special dates like the last day of the month. Given below are some of the more commonly used handy date functions in Excel.

• . This function gives today's date, whenever you open the model.
• . This function gives the exact date and time, whenever you open the model.
• . This function gives you the last date of the month, with reference to any date. The snapshot in Figure 6.42 shows how this formula works.

  

  EOMONTH is an extremely useful function because it will always take you to the last day of the month, regardless of how many days it contains. In Figure 6.42, we have a project start date of 6 July 2022 in A2. The last date of the next month is 31 August 2022, which is represented in B2 and can be copied across the row.

  The alternative way of doing a variable start date would be to use formula , and copy it across. But this is not very accurate, and if you have interest calculations running from the dates, for example, the calculations will not be exactly correct.

  The other benefit of using the EOMONTH function is that it handles leap years very well. In Figure 6.43, if we have a February start day, and want to show six monthly milestones, you can see that by using the EOMONTH function, it will always give us the last day of the month. The year 2020 is a leap year, so cell C3 gives us 29 February 2020, whereas cell E3 gives us 28 February 2021, as 2021 is not a leap year.

  

  While finding the last date of the month is the standard output of the EOMONTH function, with some improvisation you can also get the first day of the month. Since Excel treats dates as numbers, EOMONTH()+1 will give you the first day of the next month if this is required in your model.

• . This function gives you the day number in the week corresponding to the date specified. In Figure 6.44 you have the dates corresponding to 6 July 2022 through to 9 July 2022. 9 July 2022 returns a 7 because it is the last day of the week, a Saturday.

  

  In your financial model, you may have to make some decisions based on the day of the week, and in such cases, the WEEKDAY function can be a useful filter criterion. For example, trading or stock prices are not shown on the weekends, so you would skip those days for certain calculations.

• . This function is used to identify the year in a specified date. If your model needs to use the year in the date for a particular calculation or analysis, this function can help you build the necessary logic. Note that the YEAR function does not help if your fiscal year does not match the calendar year. For example, in Australia, the financial year ends at the end of June, so if you need to return a fiscal year (e.g., 2020/21), you need to create a table showing which dates fall in each financial year, and then use a formula such as a VLOOKUP function to return the value.
• . This is another very similar function that returns the month number in the date.

  These functions are particularly useful for summarising and collating raw data. For example, we can use the MONTH function to identify the month, which can then be used as the criteria in a chart, PivotTable, or a SUMIF formula to summarise the raw data into the required format. In Figure 6.45, we have used a MONTH function in column B to automatically pick up the month number of the date. This has then been used as a range in a SUMIF function in column G to aggregate the data, which can then be shown on a column chart. You can create this sheet yourself or a template can be found, along with the accompanying models to the rest of the screenshots in this book, at www.plumsolutions.com.au/book.

  

• . This returns the day in the date. As evident, this is very similar to the MONTH and YEAR function. Note that this is different from the function, which will return the day of the week. will return the day of the month. For example, as shown in Figure 6.46, on 6 July 2022 the function will return a 6, rather than a 4 as shown previously when using the WEEKDAY function in Figure 6.44.

Note that for all three functions MONTH, YEAR, and DAY, the input must be in a valid date format. Without proper input, the desired results cannot be returned.

Date Format Dilemma

Two of the most popular standards used in the world today are the dd/mm/yy (UK) and the mm/dd/yy (US) formats. Depending on the regional relevance of the information, the first two numbers could mean the month or the date. Your financial model must eliminate any such ambiguity, particularly if the model is working across several different regions. It is, therefore, best practice to format dates as 5 June 2021 instead of 5/6/21. This way there is no chance of it being interpreted erroneously as 6 May 2021.

Long and Short Date Formatting

A shortcut exists on the Home tab that allows you to very quickly change the date format on any cell from the Number ribbon, as shown in Figure 6.47.

In the drop-down menu, you should see both the options Short Date and Long Date. (Note that this may differ depending on your regional settings.) The samples for both are shown in the drop-down itself for easy understanding. While you could use any format in your calculations, you can eliminate confusion by sticking to long date format for any information that requires user interaction.

The short and long dates are not the only formats that Excel provides; you can choose from a much wider range of formats by clicking on the dialog box launcher in the Number ribbon, or by selecting More Number Formats shown at the bottom of the drop-down menu in Figure 6.47.

This brings up the Format Cells dialog box, which you can use to change the way the date appears. In Excel for Mac, the Format Cells dialog box can be found by selecting Format—Cells from the toolbar, or use the shortcut Command+1.

Note that in all versions of Excel, the Format Cells dialog box can be found by right-clicking on the cell and selecting Format Cells.

See the section “Custom Formatting” in Chapter 7 for greater detail on how to create different formats.

Net Present Value

NPV is the value of the expected future cash flows from an investment, expressed in today's dollars. The investor specifies a target rate of return (the cost of capital) for investing capital; it is an opportunity cost concept. Investors have the choice of investing in a project, or putting their funds elsewhere, so they determine the hurdle rate or the amount they want to get back from the project.

The general rule (and the one you would have studied in your university finance textbooks) for considering the investment is: If the NPV is greater than zero, the investment should be accepted; if the NPV is negative, it should be rejected. A positive NPV means the investor can expect to earn a rate of return greater than the required return rate for such an investment. However, from a financial modelling perspective, the decision-making process is much more complicated than a deal or no-deal situation. It really depends on the scenario and sensitivity analysis from the financial model to test whether or not the project should go ahead. Large companies often have policies regarding the standard cost of capital or how long the payback period can be before it is rejected.

What Cost of Capital Should We Use?    How much time is spent calculating the cost of capital really depends on how detailed your modelling is. Many models will simply use a nominated amount and document this as an assumption. This nominated cost of capital could be anything between, say, 6 and 15 percent (although sometimes higher), and it can fluctuate depending on the perceived risk of the project. We may decide to use a very high required rate of return for a risky project, to compensate for the risk taken.

However, instead of simply nominating a cost of capital amount, as we will do in the example in Figure 6.48, you may decide to calculate the weighted average cost of capital (WACC). This calculation takes into account the mixture and rates of debt and equity in the company and is, therefore, a much more accurate way of evaluating the expected rate of return for a project. For greater detail on how to calculate the WACC, see the section “Weighted Average Cost of Capital (WACC)” in Chapter 9.

Internal Rate of Return

IRR equates the present value of the cash inflows and the present value of the cash outflows. The decision rule, in this case, is: If the IRR is greater than or equal to the investor's required rate of return, the investment should be accepted; otherwise, it should be rejected.

The Problem with IRR    There are a few issues with the IRR function that the modeller needs to be aware of.

Sometimes the IRR function in Excel can produce multiple results. Every time the cash flows change sign (i.e., from negative to positive or from positive to negative), the formula will create another solution. This is why it is a good idea to insert a guessed amount—especially if the sign of the cash flows is not consistent.

Take the following set of cash flows in Figure 6.50 as an example.

If we use the ordinary IRR function without using the guess [entered as ], we get a result of 13 percent. However, because there are several negative returns in this series of cash flows, there are multiple results. If you enter the following guesses, different IRRs will be returned:

• gives a result of 13 percent.
• gives a result of 6 percent.
• gives a result of 25 percent.
• gives a result of 481 percent.

Which one is correct? They all are! There are several valid IRRs. Excel goes through an iterative process and comes up with the first solution it finds, which does not necessarily give you the result you want. This is why it's important to use the guess. If the guess is omitted, IRR is assumed to be 10 percent because this is close to a usual rate of return.

Another issue with the IRR is that the way the IRR function calculates in Excel, it assumes that cash generated during the investment period will be reinvested at the rate that has been calculated by the IRR. If the project is generating a lot of cash, the IRR calculation can overstate the financial benefits substantially, which is something that needs to be considered and possibly manually manipulated in the calculations.

We don't normally make any changes to the calculations to account for these problems, but it's important to be aware of these issues when calculating the IRR.

What Difference Does an X Make? XNPV and XIRR    The NPV formula assumes that the values entered into the formula are annual, are in chronological order, and occur at the end of the period.

XNPV and XIRR are relatively new functions introduced for Excel 2007 that offer more flexibility, as you can specify exactly when the payments occur.

XNPV allows you to enter payments that occur at varying intervals, not necessarily in chronological order (although the first payment must still be shown first), and occur at any time. Therefore, it is a much more flexible and useful function. Also, because it does not assume that the payments occur at the end of the period, the XNPV formula will be more accurate.

Be careful when comparing the NPV and XNPV functions, as the NPV assumes the current period (the date we are in right now) is a full year prior to the first cash amount and therefore discounts the first amount back by a full 12 percent (or whatever the nominated discount rate is). If this is not the case, you need to exclude the first period from the function, and add it in separately, like this: .

The XNPV function, however, assumes that the current date is the date of the first cash flow, so, in the example shown in Figure 6.51, the current date is assumed to be 1 June 2021. Therefore, the first cash amount is not discounted at all. IRR and XIRR have the same issue. This is quite misleading, as most people naturally assume that the functions, being slight variations on the same formula, work in a similar way, but the way they treat the first cash amount is quite different!

Loan-Interest Calculation Method

Interest on loans can be calculated using either the simple interest method or the compound interest method. The simple interest method charges the same amount of interest each year, but under the compound interest method, the interest is added to the loan, thereby increasing the loan amount each year. From the moment at which the interest is added to the loan, it charges interest on itself, increasing the interest on a compounding basis. The compound interest method is by far the most commonly used method, and in the examples in this chapter we assume that the interest is compounding.

When evaluating a loan, you may also need to consider the compounding frequency. If the interest is added to the loan on a daily basis, this would actually make the loan interest payments much higher than if it were added monthly or annually. Most loans are normally calculated on a daily basis, and this means the interest rate actually charged is higher.

Note that the PMT function shown further on, if calculated on an annual basis, does not compound the interest. You will need to convert the interest rate from a nominal or effective rate before using it in the calculation.

Loan Repayment According to an Amortisation Schedule

The most common form of calculating loan repayments is to apply an amortisation schedule. Regular repayments of equal value made over the term of the loan are used to pay both the interest charged as well as a portion of the principal amount. With each repayment, an amount equal to the value of the interest charged for the period is applied first to the interest portion, and the remainder of the repayment is then set off against the principal. In this way, the principal decreases after each repayment and the interest calculated for the next period will, therefore, be less, as it is calculated on a smaller principal amount.

To calculate the amount of the equal repayments to be made, the interest rate, the principal amount, and the term of the loan must be known. As interest rates are usually subject to change, the monthly repayment will often have to be revised during the course of the loan.

If you need to calculate the repayments in longhand using the amortisation method, the following formula is used:

where

Why Does the Interest Amount Decrease on a Fixed Interest Rate?

When using an amortisation schedule for loan repayment calculations, the initial interest amounts are high, but with each period the interest amount goes down and the principal amount increases. This is reflected in Figure 6.56, which is a stacked column chart based on the results in Figure 6.55.

Looking at the graph, the calculations may initially seem incorrect, since the interest rate is constant but the interest amount is decreasing. This can be better understood by delving into the actual amounts. The original principal amount was $1 million and the interest was calculated on this principal amount as $80,000. The rest of the instalment ($69,029) went towards repaying the principal.

In the second year, the principal amount was reduced by the first instalment ($69,029 went towards the principal). This means that the new principal is $1 million less the first instalment, which is $930,971. Due to the reduced principal amount, the net interest amount is reduced. However, to maintain the constant instalment amount, the principal amount is increased. Extrapolating this calculation, it is evident that as the period number increases, the unpaid principal amount decreases, and so does the interest amount. However, the principal amount being repaid increases to maintain constant repayment instalments.