Excel in Quantitative Methods Flashcards
Why Financial Modelling in Excel?
To improve our understanding and assist in decision making, we often resort to models in finance. These models attempt to represent a financial decision making situation, by adopting a series of assumptions and examining their implications.
In industrial applications a range of different tools are applied in order to model differing financial decisions. However, common to a majority of firms is the use of spreadsheet applications such as Microsoft Excel in building quick, easy to use models. The use of spreadsheets such as excel has many benets, including ease of understanding, rapid implementation and cross-platform application. However, it is worth noting that for robust, business critical modelling, programming languages such as Matlab or C++ are often appropriate.
Give some examples of common applications of Microsoft Excel in financial modelling
Personal Finance - Budgeting, tax calculations, keeping track of an investment portfolio etc.
Trading - Often used on trading desks (in tandem with software such as Bloomberg or Datastream) to monitor trading positions and real-time P&L.
Investment Decision Making - Modelling cash flows using NPV, IRR etc. Building fundamental models of equities or bonds.
Risk Management - Measuring, monitoring and understanding of risk associated with an investment portfolio.
Financial Modelling Steps (9 steps)
In order to create a clear, repeatable transparent financial model a number of systematic steps should be applied (both in Excel and
other formats):
1 Define and structure the problem
2 Define the input and output variables of the model
3 Determine the users of the model and how regularly it will be applied
4 Understand financial and mathematical aspects of the model
5 Design the model
6 Create the spreadsheets (or write the code)
7 Test the model
8 Protect and back-up the model
9 Document the model
What do you know about excel formats?
Excel files can be saved in a number of widely used versatile formats
.xlsx This format is the default in the most recent versions of Excel (2007, 2010, 2013).
.xls This format was most common in older versions of excel (pre 2007) but newer versions of excel are backwards compatible.
.csv `Comma Delimited Values’ format is sometimes used, due to versatility in importing and exporting data between alternative programmes.
.txt Similar to .csv the .txt format is sometimes used, due to versatility in importing and exporting data between alternative programmes.
Text functions in excel
Excel’s text functions can be useful to manipulate numerical fields by turning them into text, operating on the text fields and turning text back into a numerical field. Some of the more useful functions are:
LEN: Counts the length of a string.
MID: Returns a specic number of characters from a text string, starting at the position you specify, based on the number of characters you specify. It is written as MID:(Text; Start Number ; Number of Characters).
REPLACE: Replaces a portion of text, starting at a specied point and is formed as REPLACE:(Old Text; Start; Number Characters; New Text).
SEARCH / FIND: Determines the starting position of a text fragment within a string, written as FIND(Find text; Within text; [Start Point]).
TEXT: Formats a numerical value and converts it to text.
VALUE: Converts a text string that represents a number into a properly formatted number.
ISBLANK: Determines if a cell has content.
Information functions in Excel
Excel has a number of information functions, many of which return true or false (1 or 0 is used within a formula) is a condition holds. Some examples include:
ISTEXT: Tests whether a cell contains text.
ISNUMBER: Tests whether a cell contains a number.
ISERROR: Tests whether a cell contains any error value such as #N=A; #VALUE!; #REF; #DIV =0!; #NAME?; #NULL!.
ISERR: Tests for any error value expect #N=A.
ISNA: Tests exclusively for the #N=A error.
ISLOGICAL: Tests if a cell contains a logical value (TRUE or FALSE).
Mathematical functions in excel
Excel has a vast array of mathematical functionality, allowing user to perform advanced calculations.
1. exp() - Find the value of the exponential function, exp() or e(). For example, exp(1) returns the value of e (2.7183).
2. ln() - Find the natural logarithm of a number. Note, the number must be positive otherwise the function returns #NUM!.
For example, ln(2.7183) = 1
3. sqrt() - Calculate the square root of a positive number.
4. fact() - Calculate the factorial of a number, which equals 1 x 2 x 3 x 4 … number . For example, fact(6) = 720.
5. power( , p) - Raise a number to a power p. Can also be written as p. For example, power (5, 2)mail = 25
6. Average - Calculates the average of a set of numbers.
7. Count - Counts the number of cells that contain numbers (Note: Counta counts the number of non-empty cells.
8. Min and Max - Find the minimum and maximum of a set of values.
9. Subtotal - Calculates the sum of a range of cells, ignoring other subtotal functions so avoiding double counting.
10. Product - Multiplies each of a set of values together.
11. Sum - Adds a set of numbers.
12. Sumproduct - Multiplies the corresponding elements of two ranges and forms their sum.
13. Sumif - Adds the value of cells in a given range according to whether a specic criterion is met in another range. Excel 2007 also has a Sumifs function in which a range is summed
according to multiple criteria being met.
14. Countif - Counts the number of cells that meet a specied criterion.
15. Averageif - Finds the average of a set of cells where a specied criterion is met (Excel 2007).
16. Small( , n) and Large( , n) - Find the nth smallest or largest number in a set of numbers.
Investment Analysis Functions
Excel offers a range of time value of money functions that are primarily used for investment analysis
- NPV The NPV function calculates the net present value of equally spaced cash flows at a given discount rate
- XNPV Calculates the net present value of a series of cash flows that are not equally spaced in time.
- IRR The IRR function calculates the internal rate of return of equally spaced cash flows and has a number of applications, such as in project evaluation and yield analysis.
- XIRR Calculates the internal rate of return of a series of cash flows that are not equally spaced in time.
Annuity Functions
Excel provides a number of functions to perform different calculations associated with constant annuities, which can be applied to both loans and investments. These include:
1. PV The PV function calculates the present value of a constant annuity.
2. FV The FV function calculates the future value of a constant annuity.
3. PMT This function calculates the constant annuity payments required to pay o a loan or accumulate a certain amount of money.
4. NPER In order to calculate how many constant annuity payments are required to pay o a loan or save a certain amount of money, we use the NPER function.
Conditional Functions
Often times it is necessary to evaluate a formula in Excel, conditional upon certain criteria. There a number of methods to evaluate conditional statements:
1. AND Returns ‘True’ if all arguments are true and ‘False’ if one or more argument is false.
For example,
AND(1 + 1 = 2, 2 + 3 = 5) = TRUE,
AND(1 + 2 = 2, 2 + 3 = 5) = FALSE.
2. OR Returns ‘True’ if any argument is true and ‘False’ if all arguments are false. For example,
AND(1 + 1 = 2; 2 + 3 = 5) = TRUE,
AND(1 + 2 = 2; 2 + 3 = 5) = TRUE.
3. NOT Reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.
For example, NOT(1 + 1 = 2) = FALSE.
The IF functions tests if a certain logical expression, then returns one value is the logical expression is true and another if it nds it to
be false.
The statement is written as:
- IF (Logical test, Value if true, Value if false):
For example,
IF (100 > 400, 1, 0) gives value 0:
The IF statement can also be combined with the AND/OR/NOT statements and applied to cell references, for example
IF (AND(A1 > 100; B1 > 400), 1, 0)
Lookup and Reference Functions
Potentially one of the most important capabilities of Excel are lookup and reference functionality, allowing users to manipulate data in unlimited ways.
1. MATCH Find the relative position of a specied value in an array, written as MATCH(Lookup Value, Lookup Array, [Match Type]).
2. VLOOKUP Searches the left column of an array for a specied value and nd the row in that column that contains the value. It then provides the value that is at a specied column within the array. It is written as
- VLOOKUP(Lookup Value, Array, Column Number , [Match Type])
Note: There is also a HLOOKUP function. - INDEX Looks up the value in a specied row and column of a matrix and can also be used in a reference form, where it returns a reference to certain cells rather than the value in the
cell. It is formed as INDEX(Array, RowNumber , [ColumnNumber ]).
5. OFFSET Provides the value in a cell that is a specied number of rows and columns from a specied reference cell or range. It is written as
OFFSET(Reference, Number Rows, Number Columns).
6. INDIRECT Returns the reference specied by a text string.
Excel Shortcuts
Many Excel functions can be performed using keyboard shortcuts.
Some core shortcuts include:
CTRL + C to copy a cell
CTRL + V to paste into a cell
CTRL + X to cut a cell
CTRL + Z to undo
CTRL + 1 to display the format cells menu
F4 to implement absolute cell references when editing a formula
CTRL+SHIFT+ARROW to select the range from the current cell to the end of range in the direction of the arrow
CTRL+ARROW to move to the end of the current range, without selecting.
F2 to edit a formula
F7 check spelling
CTRL+F Open `Find’ text box
F3 to paste a list of all named ranges/cells.
F5 to go to a cell or range (useful when named ranges are used)
Home to go to the rst column; CRTL + Home to go to cell A1
Pivot Tables
Pivot Tables can be used to produce aggregation reports which summarize dierent aspects of a database by category.
The data range should be selected (including the eld names) before using Insert -> Pivot Tables and following the step-through wizard, which is self-explanatory.
Row and column labels (including multiple labels to create subcategories) can be placed on the Pivot Table by dragging from the eld list, or right clicking on the elds. The Pivot Table tools
can be used to change aspects of the table, including the Field Setting (Sum, Average, Count etc.) as well as the refresh if base data changes.
Excel Shortcuts
Many Excel functions can be performed using keyboard shortcuts.
Some core shortcuts include:
CTRL + C to copy a cell
CTRL + V to paste into a cell
CTRL + X to cut a cell
CTRL + Z to undo
CTRL + 1 to display the format cells menu
F4 to implement absolute cell references when editing a formula
CTRL+SHIFT+ARROW to select the range from the current cell to the end of range in the direction of the arrow
CTRL+ARROW to move to the end of the current range, without selecting.
F2 to edit a formula
F7 check spelling
CTRL+F Open `Find’ text box
F3 to paste a list of all named ranges/cells.
F5 to go to a cell or range (useful when named ranges are used)
Home to go to the rst column; CRTL + Home to go to cell A1
