When building project finance models, one of the key Best Practice methods adhered to is that models are transparent. This is done by using simple formulas that are laid out in a logical manner. Simple models will ensure that the end user can quickly and easily understand the model, and reviewing models will be very efficient and cost-effective.
To maintain the transparency in a model, it is recommended that some Excel formulas are not used unless there is no other alternative. The following Excel functions can increase the complexity of a model and are often unfamiliar to users of the model.
Top 10 Excel Functions to Avoid
- The OFFSET function returns a cell or number of cells that is a given number of rows or columns from a specified reference cell.
- The main drawback of the function is that it cannot be traced by using the in-build formula auditing tool in Excel. Thus cells that are being used by an OFFSET would appear redundant and may lead to cells being accidentally moved or deleted. This could result in a long process of trying to trace back steps.
- The OFFSET function is often hard to understand because it is used to return a reference of cells by moving a number of columns and rows. This type of function is quite different to functions that most people are used to seeing in models.
- Most applications of the OFFSET in models can be replaced by the LOOKUP function.
- The INDIRECT function returns a cell reference given a text string. Although it can be traced it should be avoided.
- The use of text in a formula to find cell references, rather than directly referencing a cell creates more room for error.
- There is almost no reason why a cell should not be directly referenced in a formula.
- The function can create very complex sections in a formula and may be hard to follow.
- This function is hard to understand as it is not commonly used and is hard to follow within a formula.
- TRANSPOSE is an array-formula, which means that the same number of cells in the range must be selected when using the formula.
- Single cells in a TRANSPOSE array, like all array formulas cannot be updated individually and must be updated at once, making it difficult to update if the user is not familiar with the TRANSPOSE function.
- This function uses a number of rows and columns to return the value in another a cell reference, in a selected range of cells. It is not readily understood due to the column and row references that are required.
- If the selected range is large, it can become difficult trying to trace the resulting cell reference.
- In some cases using an INDEX may be the only solution. When used in combination with a MATCH function, it can be used instead of a LOOKUP function when there is an unsorted range.
- This function returns the position of a cell within a lookup range of cells.
- There is a second argument, which requires the range to be in a certain order. It can become confusing for a user to understand what the function is looking up unless they are familiar with each argument.
VLOOKUP & HLOOKUP
- These functions are often used before a LOOKUP function is used. However, the LOOKUP is far cleaner and more transparent as it only requires two ranges and a lookup value.
- In VLOOKUP & HLOOKUP, a range of cells are looked up and will return a value based on a column or row number, respectively.
- The main drawback of these functions is if any columns or rows are inserted, moved or deleted within the range, the VLOOKUP & HLOOKUP will return the position of the new column or row in the lookup column number.
- This function is often used to address that some functions may result in a #N/A or #DIV/0 error, which does not necessarily impact the model.
- However, this practice does not follow best practice methodology and should be avoided. When faced with this issue, it is best to use an IF to have values displayed as 0 instead.
- By masking an error with the ISERROR function, the user may overlook a potential error in the model.
- This is an array formula which is too difficult for most people to understand as it involves matrix multiplication.
- Most transaction models do not require the complexity of matrix multiplication.
- NPV assumes that the evaluating cashflow line is annual. Most models would have monthly construction and quarterly operations so using the NPV function will not yield accurate results. In this case, the XNPV function should be used instead.
- When using NPV, the user should understand how to apply the NPV and should clearly state whether it is being evaluated at the start or end of the period.
- It is more transparent to calculate NPV from first principles using the discount rate each period or to use the XNPV.
- IRR generates a different return based on the timing resolution of the cashflows. This means that an IRR for monthly cashflows will be different to the IRR for quarterly cashflows. The timing must be regular in order for the IRR to be correct.
- In most models, the timing resolution changes from monthly to quarterly, so as an alternative, the XIRR should be used.
Excel Sample Workbook
We have put a simple spreadsheet example to demonstrate the above Excel functions. As demonstrated, some applications of the functions above can be replaced by alternative function(s) which could be easier to understand and more transparent.
Worksheet #1 – “LOOKUP & REF”
Worksheet #1 demonstrates applications of OFFSET, INDIRECT, INDEX and VLOOKUP, which could be replaced by the LOOKUP function.
Worksheet #2 – “ISERROR”
Worksheet #2 demonstrates that ISERROR function can be avoided using an IF function.
Worksheet #3 – “RETURN”
Worksheet #3 illustrates the application of XNPV and XIRR to calculate return. These functions should be used instead of NPV and IRR.