=DB – calculates depreciation based on the fixed-declining balance method =PRICE – calculates the price per $100 face value of a periodic coupon bond
=PMT – this function returns the total payment (debt and interest) on a debt security =IPMT – this formula returns the interest payments on a debt security =INTRATE – the interest rate on a fully invested security =PV – calculates the present value of an investment =FV – calculates the future value of an investment with constant periodic payments and a constant interest rate =YIELD – returns the yield of a security based on maturity, face value, and interest rate =XIRR – calculates the internal rate of return (discount rate that sets the NPV to zero) with specified dates =IRR – this formula calculates the internal rate of return (discount rate that sets the NPV to zero) =XNPV – calculates the NPV of cash flows based on a discount rate and specific dates =NPV – calculates the net present value of cash flows based on a discount rate =SUMQ – Returns the sum of the squares of the arguments Financial formulas =SUMIF – sum values in a range that are specified by a condition =PI – Returns the value of pi, accurate to 15 digits =ABS – returns the absolute value of a number
=ROUNDUP – the formula rounds a number to the specific number of digitsĪutoSum – a shortcut to quickly sum a series of numbers =ROUNDDOWN – rounds a number to the specified number of digits =PRODUCT – multiplies all of a series of numbers =SUMPRODUCT – calculates the weighted average, very useful for financial analysis =MEDIAN – returns the median average number of a series =AVERAGE – calculates the average of a series of numbers =SUM – add the total of a series of numbers =OFFSET – moves the reference of a cell by the number of rows and/or columns specified Math functions Excel Formulas Cheat Sheet =MATCH – returns the position of a value in a series =INDEX – a lookup function that searches vertically and horizontally in a table =HLOOKUP – a lookup function that searches horizontally in a table =VLOOKUP – a lookup function that searches vertically in a table INDEX MATCH – a combination of lookup functions that are more powerful than VLOOKUP Great for auditing.įind and Replace – press Ctrl + F and you can change parts of many formulas at once. Go To Special – press F5 and find all cells that are hard-codes, formulas, and more. =YEARFRAC – expresses the fraction of a year between two dates (e.g., – = 0.25)Ĭonvert time to seconds – converts an amount of time to seconds (e.g., 5 minutes to 300 seconds) Navigation Excel Formulas Cheat Sheet =YEAR – extracts and displays the year from a date (e.g., to 2018) in Excel
=NETWORKDAYS – Returns the number of whole workdays between two specified dates. =TODAY – insert and display today’s date in a cell
This formula is useful when working with Excel functions that have a date as an argument. =DATE – Returns a number that represents the date (yyyy/mm/dd) in Excel. =EOMONTH – convert a date to the last day of the month (e.g., to ) =EDATE – add a specified number of months to a date in Excel Dates and time Excel formulas cheat sheet List of Excel shortcuts – a list of the most important Excel shortcuts to speed up financial modeling. If you’re already a power user, check out our Advanced Excel Course and learn the most powerful combinations of formulas and functions.
However, if you want a video explanation of the formulas, check out our Free Excel Crash Course. We’ve built an Excel formulas cheat sheet of the most important formulas and functions required to become a spreadsheet power user.īelow is a written overview of the main formulas for your own self-study. 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.