Transcript Microsoft Excel - Kansas State University

Notes Regarding the use of
Microsoft Excel in the world of
Finance and Investment
Management
Excel Formatting Rules
 Numbers greater than 999 should be
 Use a
for all numbers
containing decimals. If any currency values on a spreadsheet
display cents (more than 0 cents), then all currency values on a
spreadsheet should be formatted to display 2 decimal places.
 R
. Centering is
sometimes ok. Never left-align columns of numbers.
 Labels should be
that they describe.
Move the label so that it is directly over the numbers it describes.
NEVER move the numbers so that they are under the label.
 Currency values need to display with a $ sign using the
style (and not
which happens when you click the $ sign button).
 Create formulas using cell references whenever possible. Don’t
hard-code your formulas using real numbers. Creating formulas
using cell references lets you create the interactive formula, so
that when you change the contents of one cell, the formula result
will automatically adjust itself.
 Relative vs Absolute vs Mixed Cell References

If a cell, column or row is common to several formulas,
you can
$A$1
or
$A1 or
A$1
 Parenthesis are used to specify that you want to
 It is best not to type in your Excel functions straight
from the keyboard (unless you know EXACTLY what
is required and how it should be entered. It is best to
use the Function Wizard to insert Excel functions.
Excel Functions
 PMT: Used to calculate the periodic payment on a
loan (car loan, home loan)


Most payments are made monthly.
PMT function requires three arguments
• Interest rate per period (annual rate divided by 12 for monthly rate)
• NPER: number of payment periods (# of yrs * 12 months/year)
• Amount of the loan (entered into the function as a negative
number so your function result ends up positive)
Excel Functions
 The IF function is used in spreadsheet decision making,
and it requires 3 arguments.




The condition being tested for (the cell and the actual test)
What to do if the condition is true
What to do if the condition is false
It yields one result if the condition is true, and a different
result if the condition is false.
 The VLOOKUP function determines where
within a specified table, a value (either numeric
or text) is found, and then it retrieves a entry
from the table that corresponds to the value, and
it requires 3 arguments.



The value to be looked up
The table range where that
value can be found
The column containing the
corresponding value.
Entered as a number (2,3, etc.) in your function
When creating the table used by your VLOOKUP, the table
value breakpoints (in the first column) should be arranged in
alphabetical order or low number to high number order.