5 Financial Modelling Functions For Professionals

By Author: Rajiv G
Total Articles: 36
Comment this article

Many financial analysts and professionals uses Microsoft excel for data management, creating models, analyzing of data for better business decisions. Also, many individuals are looking to know Microsoft excel as career decisions.
So, for them each and every aspect of Microsoft excel must be known. There are some of the financial modelling functions which every financial analysts as well as professionals must know.

Index match –
This is one of the most commonly used functions in excel. This function helps to look up and return the cell value in excel from a selected array of data.
Let’s say, there is a list of 7 countries along with their tax rate column. You want to dynamically view particular tax rate of a particular company. Then to do this, you need to use INDEX MATCH to define the INDEX array containing column of tax rates, the lookup value for the countries you want to find tax rate and a Match lookup array having list of countries.

SumProduct –
Sumproduct function is the sum of multiplication of two different arrays or list of data.
Let’s say, ...
... you have a list of five fruits along with their per kg price and kgs bought and you want to calculate how much money spend.
There are two ways to do that, first is you need to multiply each fruit price with the amount bought and then sum it totally in the end. Other is to use sumproduct function.
This function is used to save time where large sorts of calculations is required like in enterprises and organizations.

PMT –
This financial modeling function is used to calculate the standard loan payment with fixed principal combined with constant interest rates.
This function can be used to calculate monthly loan payment based on interest rates, terms and principal loan amount.
Its syntax is – PMT (rate, nper, pv, [fv], [type])
Where rate is loan interest rate,
Nper is total number of loan payments
Pv is the principal amount
Fv is future value i.e, cash balance that is to be extracted after last payment
Type is used when payments are due.

SUMIF –
This function is used to sum up the values on a range of cells based on a particular criteria.
Let’s say you have a table of monthly revenue from your business. You can use sumif to sum up the total revenue based on particular business.
Its syntax is –
SUMIF(range, criteria, [sum_range])
Where range is the range of cells that need to sum up
Criteria – it can be in form of number, expression, a call reference, text which mentions the cells to be added.
Sum_range is optional and used when cells other than range are specified to be added.

IFERROR –
This function mentions error value if there is any error in the data and to hide the error without using any complicated formulas which is why it is used for presentation purpose.
Let’s say you need to calculate profit margins of products on monthly basis. So, if there would be an error then IFERROR will display that value and it will display not available.
Its syntax is IFERROR(value, value_if_error)
where
Value is the argument which has to checked for errors
Value_if_error is the value which is returned in case of presence of any errors. Some of the error types evaluated are #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

Rajiv G. is a Strategy and Corporate Finance Consultant with 15+ years of experience. Previously, he has worked with the Strategy and Corporate Finance practice at McKinsey & Company. He has advised clients globally in multiple industries on various Strategy and Finance engagements. Prior to this, he has worked as an Equity Research Analyst with CRISIL. He is a CFA and Chartered Accountant.