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.

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

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: 644Word Count: 557See All articles From Author

Add Comment

Education Articles

1. Gavin Mccormack Journey As An Education Changemaker
Author: selinclub

2. What Makes Dubai An Ideal Destination For Global Business Conferences?
Author: All Conference Alert

3. D365 Functional Course In Ameerpet | Dynamics 365 Course
Author: Hari

4. Best Sre Certification Course | Sre Training Online In Bangalore
Author: krishna

5. Best Google Cloud Ai Training In Ameerpet | Visualpath
Author: visualpath

6. Azure Ai Engineer Course In Bangalore | Azure Ai Engineer
Author: gollakalyan

7. What To Expect At The Vermont Dmv Driving Test
Author: Ravinder Malik

8. Key Highlights Of Punyam Academy’s Iso 9001 Lead Auditor Training Course
Author: Emma

9. Ai With Aws Training | Ai With Aws Online Training Bangalore
Author: naveen

10. Salesforce Devops Training | Salesforce Devops With Copado
Author: himaram

11. How Does Cpr Affect High-risk Professions Like Healthcare, Sports, And More?
Author: Christopher Bayer

12. Best Bba Colleges In Hyderabad For Students Seeking A Corporate Career
Author: SSDC

13. Why We Charge A Training Fee At Pydun Technology
Author: Pydun Technology Private Limited

14. Informatica Idmc | Informatica Online Training In Hyderabad
Author: gollakalyan

15. Best Snowflake Course | Snowflake Training In India
Author: Pravin

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