My mission is to demystify financial models. Models should never be black boxes: they are simply made up of many layers, which themselves must always be clear and logical. Poor function choice is one of the main reasons behind incomprehensible models.
Of course, some functions can easily replace others. However, there are also some needlessly complicated formulae out there caused by poor function choice.
Excel really is like a language: you need to be properly trained and well-practised to be able to recognise what is required in different situations and to use the most efficient phrase. Avoid waffle at all costs: if you ramble you will confuse everybody including yourself.
Excel has 467 functions. I use 23 of these on a regular basis and I wrote in May’s blog about the 7 functions that I generally use for more than 5% of the time.
Here are the remaining 16 functions. Most of these have little quirks that financial modellers should be aware of (for example the fact that NPV and XNPV discount back to different points in time and that neither can deal effectively with a discount rate that varies over time). Also, some of these functions need to be used with care and only in special circumstances (for example OFFSET).
For ratios and results:
IRR, XIRR, NPV, XNPV, AVERAGE, IFERROR
For checks:
ROUND, COUNITIF
For timelines and dates:
EDATE, EOMONTH, YEAR
Other useful functions:
PRODUCT, PPMT, MOD, OFFSET
And lastly, I suppose I should mention IF which I confess to using on very rare occasions. IF is generally a long-winded formula and its overuse is a clear indicator of a black box model. For more information on the alternatives to IF please see my earlier blogs from September, October and November 2017.
Of course, I use other functions from time to time (for example PMT, SUMPRODUCT, VLOOKUP, HLOOKUP, TODAY, MONTH, DAY, AND, DAYS360, YEARFRAC, WORKDAY, WORKDAY.INTL) but financial modelling problems can typically be solved efficiently if you correctly use the 23 I have mentioned in these last two blogs.