MOST USEFUL M.S. EXCEL FORMULAS FOR PROFESSIONALS

In my experience working in finance the following are really common, grouped by category:

Present Value & Rate of Return

·       XIRR() - Finds the internal rate of return for a set of cash flows and cash flow dates, using Act/365 day-counting and semi-annual compounding.

·      XNPV() - Finds the net present value for a set of cash flows and cash flow dates, using Act/365 day-counting and semi-annual compounding. A user provides the annualized yield in decimal format.

·      IRR() - Finds the internal rate of return for a set of cash flows, assuming equivalent length time periods between each cash flow.

 

Mortgages

·      PMT() - Solves for the fully amortizing payment of a mortgage assuming payments are separate by equal length time periods (i.e. using 30/360 day-counting for monthly payment).

·   NPER() - Calculates the number of equal length time periods it would take to fully amortize a mortgage at a given fixed payment amount.

 

Array Range Manipulations

·      OFFSET() - Creates an array range of a user defined size from any given input array range. This is used for a powerful technique in Excel sometimes called "Dynamic Ranging".

·       INDIRECT() - Returns the value or values associated with a cell or array range specified using text. Since Excel has a special way of representing cell addresses, the text can be constructing dynamically to vary the output of this function. Again, this can be very powerful.

·   SUMPRODUCT() - On the surface, this function merely takes the dot product of two arrays. However, underneath the hood, it can be used to do any of the next four functions (e.g. SUMIF(), SUMIFS(), COUNTIF(), COUNTIFS()). And it can be used to create dynamic ranges or even pivoted tables of normal form data. This is one of the most powerful functions in Excel when used appropriately.

·         SUMIF() - Takes the sum of elements in an array, if a certain provided condition is met.

·        SUMIFS() - Takes the sum of elements in an array, if certain provided conditions are met.

·         COUNTIF() - Counts the elements in an array, if a certain provided condition is met.

·         COUNTIFS() - Counts the elements in an array, if certain provided conditions are met.

·      SMALL() - Returns the n-th smallest element of an array range, where n is provider by the user.

·         LARGE() - Returns the n-th largest element of an array range, where n is provider by the user.

·         TRANSPOSE() - Inverts the rows and columns of any input array range.

 

Lookup & Matching Functions

·         INDEX() - Finds an element horizontally and/or vertically in an array range using integer addresses starting at [1,1] = [row, column].

·         MATCH() - Returns an integer corresponding to where an element is located in an given one dimensional array with options to specify either an exact match or something close. If nothing is found, returns #N/A.

·         VLOOKUP() - Combines INDEX() and MATCH() to find an element vertically in an array range.

·         HLOOKUP() - Combines INDEX() and MATCH() to find an element horizontally in an array range.

 

Boolean Logic & Error Handling

·         AND(), OR() - Returns TRUE or FALSE subject to boolean "and" or "or" definitions for any number of conditions.

·         IF() - A basic conditional that does one thing if TRUE and another if FALSE.

·         IFERROR(), IFNA() - Performs a formula unless a certain errors are encounter, then takes a different action.

·         ISERROR(), ISNA(), ISBLANK() - Returns TRUE or FALSE depending on if a cell contains an error or some other specific criteria.

 

As mentioned above, a real mastery of Excel means doing manipulations quickly and without the use of a mouse. The "Copy Paste Special" feature (Ctrl + Alt + V) is invaluable in contributing to this, since it can be use to copy only the formulas or only the values of the buffered data copied.

Another excellent resource is the "Goal Seek" feature, which is a simplified version of the Excel Solver package. The "Goal Seek" feature can be accessed quickly by pressing Alt, then A, W, G (in that order).