Finance 3.0 - Social Network for Finance
Smart financial thinking
Top 10 Excel functions to avoid in financial modelling
There are many discussion around which functions are the best and how to apply them, however I don't believe that there is enough discussion around what functions to avoid and why. As part of my role at navigator project finance we have worked through the functions that are more likely to introduce errors and listed them below. The focus of this article is on financial modelling for project finance, however many comments apply to other areas as well.
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.
So, here is my top ten list of functions to avoid.
OFFSET
• 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.
INDIRECT
• 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
• 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.
INDEX
• 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.
MATCH
• 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.
ISERROR
• 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.
MMULT
• 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
• 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
• 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.
This is an extract from an article on www.navigatorpf.com
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.
So, here is my top ten list of functions to avoid.
OFFSET
• 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.
INDIRECT
• 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
• 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.
INDEX
• 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.
MATCH
• 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.
ISERROR
• 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.
MMULT
• 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
• 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
• 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.
This is an extract from an article on www.navigatorpf.com
-
Comment by Abhinav Gaind on December 8, 2008 at 11:56am - Good Article!!
-
Comment by Sachidanand (Sacha) Singh on December 8, 2008 at 12:23pm -
Very useful, thanks a lot.
But I think MMULT() is indispensable for modelling portfoilo variance
-
Comment by Rickard Warnelid on December 8, 2008 at 4:52pm -
Sacha,
You are certainly right! Maybe the title of my blog wasn't clear enough, but the emphasis should be on avoid.
Sometimes you have to use less transparent formulae and the list above are some of those formulae that should be avoided if possible. Clearly MMULT has applications where it is impossible to replace it with other functions.
Thanks for your comment
-
Comment by Bestty on December 9, 2008 at 2:58am -
Hai,
I have been strugling with my excel to prepare my small company financial statements. Is there anyone in here who has developed a simple Excel based Financial model that he/she can share with us?
-
Comment by Niraj Upadhyay on December 9, 2008 at 4:44am -
Very useful.... specially the NPV & IRR parts!!
Thanks!!
-
Comment by Giang NN on December 22, 2008 at 11:08pm -
I'm calculating some Mortgage Function relative OFFSET Function. But I have met some trouble about it. What can I do? Can I replace this function as which other function?
Give me some tips about Offset function used.
-
Comment by John Richter on December 30, 2008 at 10:18pm -
Well done Rickard - I've seen a lot of folks advocate some of the functions you have on your 'avoid' list, and which I may detest even more than your circumspect warnings. Good work on OFFSET and INDIRECT - two functions that inherently Excel can't trace de facto dependents.
TRANSPOSE as well - would be good to let folks know the alternative constructions on which not sure I have strong opinion, but tend to go with SUMIF over V or HLOOKUP approaches.
INDEX, well there, my own opinion is that you are being unfair on the poor thing, but I'd have to point to our own board for a discussion, which I gather is outlawed on this site.
MATCH, again, perhaps unfair. INDEX/MATCH combinations are legitimate means through which many modelers synthetically create a LOOKUP structure that has FALSE option (i.e. exact match).
V and HLOOKUP, yes agree LOOKUP is 'clean' and elegant looking, but TRUE only capability is shunned by many firms in the business. You may wish to consider / note this weakness as a big problem.
ISERROR, totally agreed, one of the few on my outright ban list (even OFFSET I can tolerate if used well)
MMULT, well, has its uses, but I'd side with you as one to avoid in standard financial modeling.
NPV, again, well done, back you up on that.
IRR. Well of course, XIRR as/when frequency change in model (not really often the case). I would argue XIRR is flakier (that 0.00% return with a non-negative first cash flow is a joke) and why would you throw an XIRR (which also has 365-day convention built in) when IRR avoids Analysis Toolpak probs (e.g. language translation) and is simpler?
So with that by way of modest difference of opinion, what about ROUND (always a worry to me) and its variants? Definitely deserves a query as to whether used appropriately. Not keen on SUBTOTAL. I think CHOOSE is worthless (over INDEX). I actually avoid LOOKUP unless TRUE construciton is deliberate.
All I can think off top of my head -- will have to do a more thorough review. Excellent idea for a post / discussion.
-
Comment by John Richter on December 31, 2008 at 11:41am
-
Hmmm, just to add another thought / idea. XNPV is no good at all either. For same reasons as Rikard properly described for NPV, where 'factors being better' and added pain in the neck of it being ATP (which isn't any better in Excel 2007).
And for those of you saying, yeah, but what about cash flows / models with different time frequencies (e.g. monthly investment and semi-annual distributions) that leads to requirement, or at least practical benefit, of XIRR -- same holds for XNPV. Nope, afraid not. NPV calculation, as the math guys will say, is linear, i.e. can be added up ala 'valuation by parts'. Hence can simply separately calculate NPVs (probably technically PVs) of monthly and semi-annual cash flows and add and subtract as necessary.
And now, reading Rikard's original note with a bit more thoroughness (maybe I should actually read the whole thing : -). No, the NPV does not assume 'annual' -- not good advice (Rikard, you should amend this). It isn't anymore annual than IRR (it's the XIRR that is automatically 365-day year annual). NPV simply presumes 'same distance' and what it is is dependent on the periodicity the modeler has assigned -- the discount rate fed to the wretched function needs to align with this.
Which I suppose gets me to two further 'why Richter hates the NPV function':
1. It does an ugly job reacting to empty cells at front of the range, NOT treating them as zeros, but shifting the point of present value date forward in time to point of first uncleared cell. Beware.
2. It can't handle time-varying discount rates, which anyone with a Finance 101 degree is fundamental to any leveraged DCF on corporate valuation where equity hurdles are deemed to vary with leverage. Also, for you Euro-PF types who live and die with the LLCR, not helpful if margin or base rates vary in discounting cash flow available for debt service on the numerator.
3. I fundamentally detest the absense of being able to see/chart the stream of PVs that one will invevitably find with the discount factor approach -- unless modeler gets clever (not) with SUMPRODUCT. Seeing the profile of PV'd cash flows (say distributions to equity) is a gives a good analyst a fundamental impression of how things look.
Enough for this morning I think. Happy to New Year to anyone as sad as me who may read this in the next couple of days.
-
Comment by Nick Crawley on February 11, 2009 at 3:44am -
This is a good article with some great responses. Personally I wouldn't bother amending the NPV 'annual' statement though on the basis that it will be right more often than wrong in 'standard financial modelling' and is a good way to frighten users off of a pretty poor function.
My underlying reasoning here is that if somebody gives you a discount rate to apply for the NPV calculation it is implicitly stating that the 'distance' between the calculations as considered by Excel will be annual. The reason it might cause confusion is in situations where the discount rate, by market practice, is something such as monthly yields for bonds or the like.
My only further comment is that the weakness of LOOKUP far outweigh the benefits of its H and V cousins. Wouldn't dream of using it professionally in a model that would ever 'change' and thats what they all do!!!
-
Comment by Nick Crawley on February 11, 2009 at 3:45am
- My underlying reasoning here is that if somebody gives you a discount rate [it is more likely than not to be annual] to apply for the NPV calculation it is implicitly stating that the 'distance' between the calculations as considered by Excel will be annual.
Sign in
Badge
Continuing Education
 |
Accounting |
 |
Banking Regulations |
 |
Certification Training |
 |
Commercial Bank Management |
 |
Corporate Finance & Valuation |
 |
Treasury Management |
 |
Credit Risk Management |
 |
Financial Derivatives |
 |
Financial Institution Management |
 |
Financial Markets |
 |
Financial Modeling |
 |
Financial Planning |
 |
Market Risk Management |
 |
Operational Risk Management |
 |
Operations & Compliance |
 |
Quantitative Analysis |
 |
Strategic Financial Management |
Newsletter
Linkedin
RSS
Facebook
Widgets
Twitter© 2010 Created by Finance 3.0.
You need to be a member of Finance 3.0 - Social Network for Finance to add comments!
Join Finance 3.0 - Social Network for Finance