1 basic excel financial functions

Fi i l M d li NFinancial Modeling Notes

Basic Excel Financial FunctionsBasic Excel Financial Functions

Professor Iordanis Karagiannidis

© 2010 Iordanis Karagiannidis

Excel Functions Excel Functions are preformatted formulas that allow you to

perform arithmetic and other operations very quickly and i tlconveniently

There are more than 300 functions in excel, in 9 major categoriescategories

Can combine more than one functions together

You can be quite good knowing around 50 or soYou can be quite good knowing around 50 or so

You can also build your own functions in Visual Basic for yApplications (VBA) - Later


How to insert an excel function Click on the fx icon on the formula bar

S l f i f h f l Select a function from the formulas menu:

Type directly in the excel cell the ‘=‘ sign followed by the function name


Future Value (FV) Function Calculates the future value of a single cash flow or a series of

equal cash flows (annuities). 1 NiPVFV

Annuity

FlowCash Single

11

1

N

N

iiPVFV

iPVFV

periods ofnumber rate,interest periodic Ni


Present Value (PV) Function Calculates the present value of a future cash flow or a series

of equal future cash flows (annuities 1 NiFVPV

Annuity

FlowCash Single

11

1

N

N

iiFVPV

iFVPV

periods ofnumber rate,interest periodic Ni


Other Similar Financial Functions PMT Function – Solves for the annuity PMT that is

equivalent to a single future of present cash flow NPER F ti S l f th b f i d NPER Function – Solves for the number of periods RATE Function – Solves for the interest rate

Notes on financial functions For some of them you need to input cash flows as negative if

h b byou want the output to be a positive number Can use the function when you have non-annual compounding

but you have to make sure that all variables (number of periods, interest rate, etc.) refer to the same time period (year, month, quarter etc.)


The NPV Function in Excel Used to find the Present Value of a series of uneven cash flows.

(we are going to use this function a lot!!!) Two major inputs: a) interest rate, b)series of cash flows Accepts up to 254 cash flows

You can input cash flow one by one or as a range or cells Cash flows have to be sequentialq Cash flows can be negative or zero

The NPV Function calculates the present value of all the series of pcash flows entered, one period prior to the occurrence of the first cash flow

NPV Function does not correspond to the standard use of the term “Net Present Value”


The IRR Function in Excel Calculates the internal rate of return (IRR) One major input: series of cash flows Cash flow must be selected as a range Cash flow must be selected as a range First cash flow has to be negative Cash flows have to be sequential Cash flows can be negative or zero

The IRR is the rate of return that makes the Net Present The IRR is the rate of return that makes the Net Present Value equal to zero.

IRR Function does correspond to the standard use of the term “Internal Rate of Return”


The XNPV and XIRR Functions What happens if cash flow do not occur in fixed time

intervals? use the XNPV and XIRR functions

Similar to the NPV and IRR functions, however there are some differences Input both cash flows and the specific dates the cash flows occur Input both cash flows and the specific dates the cash flows occur

(dates do not need to be at even intervals) The XNPV function calculates the present value of all cash

flows at the time of the first cash flow and not one period prior to the first cash flow as NPV function does

The XIRR functions outputs an annualized return The XIRR functions outputs an annualized return XIRR=(1+DailyIRR)365 - 1


Other Functions to Know

Logical: IF, TRUE, FALSE, AND, OR

Math: INT, MOD, ROUND, SUM, SUMIF

Statistical: AVERAGE, COUNT, COUNTA, MIN, MAX

Lookup and Reference: CHOOSE, HLOOKUP, INDEX, MATCH, OFFSET, VLOOKUP

D d Ti DATE DAY MONTH NOW YEAR Date and Time: DATE, DAY, MONTH, NOW, YEAR

Information: ISERROR, ISNUMBER, ISTEXT, ISBLANK

T t LEFT LEN MID RIGHT Text: LEFT, LEN, MID, RIGHT


More Advanced Functions to know

Math and Trigonometry: ABS, CAILING, FLOOR, ROUNDUP, ROUNDDOWN, SUMIF, SUMPRODUCT

Statistical: COUNTIF

k d f CT Lookup and Reference: INDIRECT

Date and Time: DAYS360

T F i LOWER PROPER TEXT TRIM UPPER Text Functions: LOWER, PROPER, TEXT, TRIM, UPPER, VALUE

Financial Functions: XIRR XNPV Financial Functions: XIRR, XNPV


1 basic excel financial functions

Documents