Skip to main content

Excel formulas


Excel formulas cheat sheet

If you want to become a master of Excel financial analysis and building financial models then you’ve come to the right place.  We’ve built a cheat sheet of the most important Excel 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.

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)
=TODAY – insert and display today’s date in a cell
=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
=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

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
=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
=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”
=AND – checks if all conditions are met and returns only “TRUE” or “FALSE”
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
Labels:

Comments

Post a Comment

Popular posts from this blog

Excel lesson 7

How to Create Budget In Excel Spreadsheet with Template "Money is a tool. Used properly it makes something beautiful- used wrong, it makes a mess!" - Bradley Vinson It takes discipline to use money properly. In this tutorial, we are going to look at how we can use Excel to properly manage our personal finances. We will cover the following topics. Why manage Budget? Major components of a personal finance system Using Excel to set personal budgets, record income and expenses Visualizing the data using charts Why manage Budget? Let's face it, the world we live in is fuelled by money. We go to school to get a good job, engage in business and other related activities with the main goal of making money. If we do not manage our personal finances properly, then all of our efforts go to waste. Most people spend more than they earn. In order to be financially successful, one needs to develop a habit of spending less than they earn and invest the surplus in ...
1 comment
Know more>>

Excel lesson 4th

Excel Formulas & Functions: Date, Time, String, VLOOKUP Formulas and functions are the building blocks of working with numeric data in Excel. This article introduces you to formulas and functions. In this article, we will cover the following topics. What is a formula? Mistakes to avoid when working with formulas in Excel What is a function? (Function Wizard) The importance of functions Common functions Numeric functions String functions Date Time functions V Lookup function Tutorials Data For this tutorial, we will work with the following datasets. Home supplies budget S/N ITEM QTY PRICE SUBTOTAL Is it Affordable? 1 Mangoes 9 600     2 Oranges 3 1200     3 Tomatoes 1 2500     4 Cooking Oil 5 6500     5 Tonic Water 13 3900     House Building Project Schedule S/N ITEM START DATE END DATE DURATION (DAYS) 1 Survey land 04/02/2015 07/02/2015   2 Lay Foundation 1...
Post a Comment
Know more>>

Excel lesson 3rd

Excel Data Validation, Filters, Grouping In this tutorial, we are going to cover the following topics. Data validation Data filters Group and Ungroup Ading images to spreadsheets Data validation Data validation is very important in the sense that it helps us avoid mistakes that can be avoided . Let's assume you are recording student exam marks and you know the minimum is 0 and the maximum is 100. You can take advantage of validation features to ensure that only values between 0 and 100 are entered. Add a new sheet in your workbook by clicking on the plus button at the bottom of the worksheet. Add a column for S/N, Name and Score. Your sheet should look as follows S/N Name Score 1 Jane 2 James 3 Jones 4 Jonathan 5 John Click on the DATA tab Select the cells C2 to C6 (The cells that will be used to record the scores) Click on Data validation drop down list. Click on Data validation. You will get the following dialogue window Click...
Post a Comment
Know more>>