basic excel concepts and formulas

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

Info icon 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 financial model. Cells containing constants may be highlighted with color to differentiate them from cells containing formulas. For financial 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
Image of page 1

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

1. Numeric: Excel stores numbers with 15 digits of precision. Nu- meric data can be formatted in a variety of ways, including integer, decimal, scientific 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
Image of page 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().
Image of page 3

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

Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern