basic excel concepts and formulas

# basic excel concepts and formulas - A. B. Freeman School of...

This preview shows pages 1–4. Sign up to view the full content.

A. B. Freeman School of Business MBA Professor Paul A. Spindt Financial Modeling Spring 2006 A FEW THINGS ABOUT EXCEL 1 Concepts Constants and Formulas In Excel, cells can contain either constants or formulas. You cannot tell which just by looking at the display (usually), since Excel normally displays the result of a formula in a cell, rather than the formula itself. 1 Constants may be numeric, logical, date, or text depending on the type of data entered in the cell. Formulas may produce numeric, logical, date, or text output. It is good spreadsheet practice to segregate constant cells and formula cells when building a ﬁnancial model. Cells containing constants may be highlighted with color to diﬀerentiate them from cells containing formulas. For ﬁnancial modeling: 1. Model parameters , or value assumptions, are entered as constants. 2. Structural assumptions are entered as formulas. 3. Outcomes are the results of formulas. Data Types Excel data types include 1 To get Excel to reveal formulas, press Control-Accent (the Accent key is the one above the tab key on the left side of the keyboard.) 1

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
1. Numeric: Excel stores numbers with 15 digits of precision. Nu- meric data can be formatted in a variety of ways, including integer, decimal, scientiﬁc notation, date, time, currency, and even as text. How a cell containing numeric data is formatted determines what is displayed. The number 15,000, for example, can be displayed as 15,000, \$15,000.00, or January 25, 1945, depending on how the cell containing the number is formatted. 2. Logical: Logical values in Excel are TRUE and FALSE. 3. Text: In Excel, text data is any combination of non-numeric char- acters and spaces. Numeric characters may be included in the string. 4. Error: Error values in Excel are produced in a cell when Excel encounters a problem. Examples include #N/A (value not avail- able), #REF! (improper cell reference), and #DIV/0 (attempt to divide by zero). Annuity Functions An annuity is a series of constant payments made over a set run of contiguous periods. A level payment mortgage loan, for example, is an annuity. Annuity arithmetic is based on the following equation: V 0 (1 + r ) n + pmt (1 + r × type ) ± (1 + r ) n - 1) r ! + V n = 0 where: V 0 is the present value (PV) of the annuity; V n is the future value (FV) of the annuity; 2
n is the number of periods (nper) in the life of the annuity; r is the interest rate per period (rate) for the annuity; pmt is the periodic payment (pmt) of the annuity; and type is 0 if payments are in arrears (at the end of each period) and 1 if payments are made in advance (at the beginning of each period). Excel contains a number of annuity functions which solve the annuity equation for one variable in terms of the other ones. For example, FV() solves for V n given rate, nper, pmt or V 0 , and type (default is 0). Other functions are PV(), RATE(), and PMT(). Payments can be broken down into interest and principle portions using IPMT() and PPMT(). Two important things about these annuity functions:

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 03/09/2008 for the course FINC 777 taught by Professor Spindt during the Spring '08 term at Tulane.

### Page1 / 10

basic excel concepts and formulas - A. B. Freeman School of...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online