Excel Formulas for Finance PDF

Excel Fundamentals: Formulas for Finance Important Notes on Key Functions Use statistical functions to perform basis statistical analysis on data sets. Functions in the SUMIFS function family can handle multiple criteria, but only work for AND logic. They are not able to use OR logic. The...

Excel Fundamentals: Formulas for Finance Important Notes on Key Functions Use statistical functions to perform basis statistical analysis on data sets. Functions in the SUMIFS function family can handle multiple criteria, but only work for AND logic. They are not able to use OR logic. The XLOOKUP function has more options and it is less prone to errors than older HLOOKUP and VLOOKUP functions. Dates are actually serial numbers which can be formatted to appear in various date formats. The NPV & IRR functions can only be used on cash flows that have even and regular spacing between dates. The XNPV & XIRR functions must be used for cash flows that have uneven or irregular spacing between dates. Use the CONCATENATE function or '&' operator to join text strings with cell contents. The TEXT function can be used within a CONCATENATE function to modify the format of a number. Statistical Functions Navigation Keystroke MIN Returns smallest number in set of values. Zoom Out / In CTRL ALT - / = MAX Returns largest number in set of values. Switch Between Tabs CTRL PG UP / PG DN AVERAGE Returns average or arithmetic mean. Select Multiple Cells SHIFT Arrow MEDIAN Returns median or number in the middle. Select to End CTRL SHIFT Arrow LARGE Returns the specific largest entry in data. Select Row SHIFT Spacebar SMALL Returns the specific smallest entry in data. Select Column CTRL Spacebar Delete Row / Column CTRL - Aggregation Functions Insert Row ALT IR IF Returns one value if true or second value if false. Insert Column ALT IC SUMIF 1 Will perform aggregation if condition is met. SUMIFS 2 Will perform aggregation if conditions are met. Lookup Functions Common Keystroke HLOOKUP Looks horizontally & returns values in a table array. Copy CTRL C ROWS Returns the number of rows in an array. Paste CTRL V VLOOKUP Looks vertically & returns values in a table array. Paste Special CTRL ALT V or ALT ES COLUMNS Returns the number of columns in an array. Fill Right CTRL R IFERROR Returns value if expression is an error. Fill Down CTRL D XLOOKUP Searches match array & returns from second array. Find CTRL F Replace CTRL H Date Functions AutoSum ALT = TODAY Returns the current date formatted as a date. EDATE Changes date to same day but in a different month. EOMONTH Changes date to last day in a different month. Formatting Keystroke YEARFRAC Returns the fraction of a year between two dates. Format Cells CTRL 1 YEAR, MONTH, DAY Returns the year, month or day from a date. Date Format CTRL 2 DATE Assembles a date from a year, month and day. Strikethrough CTRL 5 Return Functions SUMPRODUCT Returns the sum of the products. Helpful for WACC. NPV Gives the net present value of evenly spaced cash flows. Function Keys Keystroke XNPV Gives net present value of unevenly spaced cash flows. Edit Cell F2 IRR Gives internal rate of return for evenly spaced cash flows. Repeat Last Command F4 XIRR Gives internal rate of return for unevenly spaced cash flows. Lock Reference F4 (From Inside Cell) Labeling & Other Functions CONCATENATE / & Joins text strings and cell contents into one text string. TEXT Converts a value in text to a specific number format. ROUND Rounds a number off to a specified number of digits. For Mac users: ALT = Option and CTRL = Command. Family includes SUMIF, COUNTIF, AVERAGEIF. ⁽¹⁾ Family includes SUMIFS, COUNTIFS, AVERAGEIFS, MINIFS, MAXIFS. ⁽²⁾ Plural functions above can use AND logic, but not OR logic. Excel Fundamentals: Formulas for Finance