Earn your certification as a Financial Modeling & Valuation Analyst (FMVA)®. Register today!

Excel Formulas Cheat Sheet

The most important Excel formulas for financial analysis

Excel formulas cheat sheet

If you want to become a master of Excel financial analysis and an expert on building financial models then you’ve come to the right place.  We’ve built an Excel formulas cheat sheet of the most important formulas and functions required to become a spreadsheet power user.

Below is a written overview of the main formulas for your own self study.  However, if you want a video explanation of the formulas, check out our Free Excel Crash Course.

If you’re already a power user, check out our Advanced Excel Course and learn the most powerful combinations of formulas and functions.

 

Excel formulas cheat sheet theme

 

Shortcuts

List of Excel shortcuts – a list of the most important Excel shortcuts to speed up financial modeling.

 

Dates and time

=EDATE – add a specified number of months to a date in Excel

=EOMONTH – convert a date to the last day of month (i.e. 7/18/2018 to 7/31/2018)

=DATE – Returns a number that represents the date (yyyy/mm/dd) in Excel. This formula is useful when working with excel functions that have a date as argument.

=TODAY – insert and display today’s date in a cell

=NETWORKDAYS – Returns the number of whole workdays between two dates.

=YEAR – extract and display the year from a date (i.e. 7/18/2018 to 2018) in Excel

=YEARFRAC – expresses the fraction of a year between two dates (i.e. 1/1/2018 – 3/31/2018 = 0.25)

Convert time to seconds – convert an amount of time to seconds (i.e. 5 minutes to 300 seconds)

 

Navigation

Go To Special – press F5 and find all cells that are hard-codes, formulas and more. Great for auditing.

Find and Replace – press Ctrl + F and you can changes parts of many formulas at once

 

Lookup formulas

INDEX MATCH – a combination of lookup functions that are more powerful than VLOOKUP

=VLOOKUP – a lookup function that searches vertically in a table

=HLOOKUP – a lookup function that searches horizontally in a table

=INDEX – a lookup function that searches vertically and horizontally in a table

=MATCH – returns the position of a value in a series

=OFFSET – moves the reference of a cell by the number of rows and/or columns specified

 

Math functions

=SUM – add the total of a series of numbers

=AVERAGE – calculates the average of a series of numbers

=MEDIAN – returns the median number of a series

=SUMPRODUCT – calculates the weighted average, very useful for financial analysis

=PRODUCT – multiplies all the numbers together.

=ROUNDDOWN – rounds a number to the specified number of digits

=ROUNDUP – the formula rounds a number to the specific number of digits

AutoSum – a shortcut to quickly sum a series of numbers

=ABS – returns the absolute value of a number.

=PI – Returns the value of pi accurate to 15 digits

=SUMIF – sum values in a range that are specified by a condition.

=SUMQ – Returns the sum of the squares of the arguments

 

Financial formulas

=NPV – calculates the net present value of cash flows based on a discount rate

=XNPV – calculate the NPV f cash flows based on a discount rate and specific dates

=IRR – this formula calculates the internal rate of return (discount rate that sets the NPV to zero)

=XIRR – calculates the internal rate of return (discount rate that sets the NPV to zero) with dates

=YIELD – returns the yield of a security based on maturity, face value, and interest rate

=FV – Calculates the future value of an investment with constant periodic payments and a constant interest rate.

=PV – Calculates the present value of an investment.

=INTRATE – the interest rate on a fully invested security

=IPMT – this formula returns the interest payments on a debt security

=PMT – this function returns the total payment (debt and interest) on a debt security

=PRICE – Calculates the price per $100 face value of a periodic coupon bond.

=DB – calculates depreciation based on the fixed-declining balance method

=DDB – calculates depreciation based on the double-declining balance method

=SLN – calculates depreciation based on the straight-line method

 

Conditional functions

=IF – checks if a condition is met and returns a value if yes and if no

=OR – checks if any conditions are met and returns only “TRUE” or “FALSE”

=XOR – the “exclusive or” statement returns true if the number of TRUE statements are odd.

=AND – checks if all conditions are met and returns only “TRUE” or “FALSE”

=NOT – changes “TRUE” to “FALSE”, and “FALSE” to “TRUE”.

IF AND – combine IF with AND to have multiple conditions

=IFERROR – if a cell contains an error you can tell Excel to display an alternative result

 

Other functions and formulas

Sheet Name Code – a formula using MID, CELL and FIND functions to display the worksheet name

Consolidate – how to consolidate information between multiple Excel workbooks

 

Additional resources

Thanks for reading CFI’s Excel formulas cheat sheet.  By now you’re ready to perform even more advanced financial analysis.  To keep practicing, check out these resources below:

  • Excel shortcuts for PC and Mac
  • List of Excel functions
  • Free Excel Course
  • All Excel resources

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!