123ArticleOnline Logo
Welcome to 123ArticleOnline.com!
ALL >> Education >> View Article

5 Financial Modelling Functions For Professionals

Profile Picture
By Author: Rajiv G
Total Articles: 36
Comment this article
Facebook ShareTwitter ShareGoogle+ ShareTwitter Share

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.

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.

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.

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)
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!.

More About the Author

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.

Total Views: 83Word Count: 557See All articles From Author

Add Comment

Education Articles

1. Online Abacus Classes In Abu Dhabi - Swiflearn
Author: Priyanshu Rawat

2. Get Social Studies Homework Help From Experts
Author: Vnaya Education

3. Undergraduate Courses
Author: Bpec

4. Difference Between Android App Development And Ios App Development
Author: dzone kajal

5. Data Analytics Consulting Services For Business Insights
Author: ExcelR Solutions

6. How To Stay Healthy While Studying Online
Author: Todd Manning

7. Online Abacus Classes In Halifax - Swiflearn
Author: Priyanshu Rawat

8. Things To Remember Before Starting Abroad Education Consultancy
Author: Stamp Visa

9. Abacus Classes In Bowral - Swiflearn
Author: Priyanshu Rawat

10. Career Launcher’s Workshops To Train Teachers On Mcq Evaluation Skills Directed Under New Cbse Exam Pattern Of Nep 2020
Author: Daksh Kaur

11. Knowledge Science Course, Information Science Training & Certification,be Taught Information Science
Author: ExcelR Solutions

12. How To Pass Aptitude Reasoning Tests
Author: Pass Psychometric

13. Business Analytics On-line Certificates Course In India
Author: ExcelR Solutions

14. Digital Marketing For Local Business
Author: yogeshsashi

15. Overview Of Social Media
Author: yogeshsashi

Login To Account
Login Email:
Forgot Password?
New User?
Sign Up Newsletter
Email Address: