Chat with us, powered by LiveChat
Excel for Data Sciencists

Essential Excel Functions for Building Financial Models

Essential Excel Functions for Building Financial Models

Financial modelling is ALL about the functions! Basically, the whole point of building a financial model is so that when the inputs change, the outputs change as well – so a good financial modeller will know lots of lots of functions. Check out my list of favorite Excel functions.

Essential Excel Function for Financial Modeling

FunctionWhat It Does
SUMAdds up, or sums together, a range of cells.
MINCalculates the minimum value of a range of cells.
MAXCalculates the maximum value of a range of cells.
AVERAGECalculates the average value of a range of cells.
ROUNDRounds a single number to the nearest specified value, usually to a whole number.
ROUNDUPRounds up a single number to the nearest specified value, usually to a whole number.
ROUNDDOWNRounds down a single number to the nearest specified value, usually to a whole number.
IFReturns a specified value only if a single condition has been met.
IFSReturns a specified value if complex conditions have been met.
COUNTIFCounts the number of values in a range that meet a certain single criterion.
COUNTIFSCounts the number of values in a range that meet multiple criteria.
SUMIFSums the values in a range that meet a certain single criterion.
SUMIFSSums the values in a range that meet multiple criteria.
VLOOKUPLooks up a range and returns the first corresponding value in a vertical table that matches exactly the specified input.
HLOOKUPLooks up a range and returns the first corresponding value in a horizontal table that matches exactly the specified input. An error is returned if it cannot find the exact match.
INDEXWorks like the coordinates of a map and returns a single value based on the column and row numbers you input into the function fields.
MATCHReturns the position of a value in a column or a row. Modelers often combine MATCH with the INDEX function to create a lookup function, which is far more robust and flexible and uses less memory than the VLOOKUP or HLOOKUP.
PMTCalculates the total annual payment of a loan.
IPMTCalculates the interest component of a loan.
PPMTCalculates the principal component of a loan.
NPVTakes into account the time value of money by giving the net present value of future cash flows in today’s dollars, based on the investment amount and discount rate.
Financial modelling is ALL about the functions! Basically, the whole point of building a financial model is so that when the inputs change, the outputs change as well – so a good financial modeller will know lots of lots of functions. Check out my list of favorite Excel functions.

 

Download Excel Workbook with Examples Below

Essential Excel Functions for Building Financial Models (26 downloads)

 

Talk to us

Request for Proposal

David K. Kandie is OCL Learning Lead trainer and Chief Learning Officer.

OCL Learning trading as OpenCastLabs Consulting is the leading Microsoft Excel training and solutions development firm in Kenya. OCL Learning is a Microsoft Certified Silver Partner.

 

Share with your followers...
Email this to someone
email
Share on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin
× Chat with us!