# Tutorial Excel Financial Functions

Document Sample

```					   Excel Tutorial 9

Developing a Financial
Analysis

COMPREHENSIVE
Objectives
• Work with financial functions to analyze loans
and investments
• Create an amortization schedule
• Calculate a conditional sum
• Interpolate and extrapolate a series of values
• Calculate a depreciation schedule

New Perspectives on Microsoft Office Excel 2007    2
Objectives
•    Determine a payback period
•    Calculate a net present value
•    Calculate an internal rate of return
•    Trace a formula error to its source

New Perspectives on Microsoft Office Excel 2007   3
Working with Loans and
Investments
• To calculate the present value of a loan or investment, use the PV
function
• To calculate the future value of a loan or an investment, use the
FV function
• To calculate the size of the monthly or quarterly payments
required to pay off a loan or meet an investment goal, use the
PMT function
• To calculate the number of monthly or quarterly payments
required to pay off a loan or meet an investment goal, use the
NPER function
• To calculate the interest of a loan or investment, use the RATE
function

New Perspectives on Microsoft Office Excel 2007                        4
Working with Loans and
Investments
•    =PMT(rate, nper, pv, [fv=0] [type=0])
•    =FV(rate, nper, pmt, [pv=0] [type=0])
•    =NPER(rate, pmt, pv, [fv=0] [type=0])
•    =PV(rate, nper, pmt, [fv=0] [type=0])
•    =RATE(nper, pmt, pv, [fv=0] [type=0])

New Perspectives on Microsoft Office Excel 2007   5
Calculating a Loan Payment
• The functions to work with loans are the same
ones you used to work with investments

New Perspectives on Microsoft Office Excel 2007   6
Creating an Amortization Schedule
• To calculate the amount of interest due in a
specified payment period from a loan, use the
IPMT function
• To calculate the amount of a loan payment used
to pay off the principal of the loan, use the PPMT
function
• =IPMT(rate, per, nper, pv, [fv=0] [,type=0])
• =PPMT(rate, per, nper, pv, [fv=0] [,type=0])

New Perspectives on Microsoft Office Excel 2007        7
Creating an Amortization Schedule

New Perspectives on Microsoft Office Excel 2007   8
Calculating Yearly Interest and
Principal Payments
• One way of calculating totals from several
payment periods is to use the Analysis Tool-Pak
• =CUMIPMT(rate, nper, pv, start, end, type)
• =CUMPRINC(rate, nper, pv, start, end, type)

New Perspectives on Microsoft Office Excel 2007     9
Calculating Yearly Interest and
Principal Payments

New Perspectives on Microsoft Office Excel 2007   10
Projecting Future Income and
Expenses
• An income statement, also known as a profit
and loss statement, shows how much money a
business makes or loses over a specified period
of time

New Perspectives on Microsoft Office Excel 2007     11
Interpolating a Series of Values
• Select the range with the first cell containing the
starting value, blank cells for middle values, and
the last cell containing the ending value
• In the Editing group on the Home tab, click the
Fill button, and then click Series
• Specify whether the series is organized in rows
or columns and the type of series to interpolate.
Check the Trend check box
• Click the OK button to insert the interpolated
series into the middle cells
New Perspectives on Microsoft Office Excel 2007         12
Extrapolating a Series of Values
• Select a range with the first cell containing the starting
value followed by blank cells to store the extrapolated
values
• In the Editing group on the Home tab, click the Fill
button, and then click Series
• Select whether the series is organized in rows or
columns. Select the type of series to extrapolate into
the blank cells. Enter the step value in the Step value
box
• Click the OK button to insert the extrapolated series into
the blank cells
New Perspectives on Microsoft Office Excel 2007                13
Extrapolating a Series of Values

New Perspectives on Microsoft Office Excel 2007   14
Calculating Depreciation
• To calculate a straight-line depreciation, use the SLN
function
• To calculate a declining balance depreciation, use the
DB function
• To calculate a sum-of-years’ digit depreciation, use the
SYD function
• To calculate a double-declining balance depreciation,
use the DDB function
• To calculate a variable depreciation, use the VBD
function

New Perspectives on Microsoft Office Excel 2007              15
Calculating Depreciation

New Perspectives on Microsoft Office Excel 2007   16
Working with Payback Period
• One simple measure of the return from an
investment is the payback period, which is the
length of time required for an investment to
recover its initial cost

New Perspectives on Microsoft Office Excel 2007    17
Calculating Net Present Value
• The time value of money is based on the
assumption that money received today is worth
more than the same amount received later

New Perspectives on Microsoft Office Excel 2007   18
Determining the Return from an
Investment
• To calculate the net present value when the initial investment is made
immediately, use the NPV function with the discount rate and the series of
cash returns from the investment. Subtract the cost of the initial investment
from the value returned by the NPV function
• To calculate the net present value when the initial investment is made at the
end of the first payment period, use the NPV function with the discount rate
and the series of cash returns from the investment. Include the initial cost of
the investment as the first value in the series
• To calculate the internal rate of return, use the IRR function with the cost of
the initial investment as the first cash flow value in the series. For investments
in which there are several positive and negative cash flow values, include a
guess to aid Excel in arriving at a reasonable internal rate of return value

New Perspectives on Microsoft Office Excel 2007                                        19
Using the NPV Function
• =NPV(rate, value1 [value2, value3, ...])

New Perspectives on Microsoft Office Excel 2007   20
Using the NPV Function

New Perspectives on Microsoft Office Excel 2007   21
Calculating the Internal Rate of
Return
• The point at which the net present value of an
investment equals 0 is the internal rate of return
(IRR)
• =IRR(values, [guess=0.1])

New Perspectives on Microsoft Office Excel 2007        22
Calculating the Internal Rate of
Return

New Perspectives on Microsoft Office Excel 2007   23
Exploring other Financial Functions
• For cash flows that appear at unevenly spaced
intervals, you use the XNPV and XIRR functions
– =XNPV(rate, values, dates)
– =XIRR(values, dates, [guess = 0.1])

New Perspectives on Microsoft Office Excel 2007    24
Tracing Error Values
• Select the cell containing an error value
• In the Formula Auditing group on the Formulas tab, click the Error
Checking button arrow and then click Trace Error
• Follow the tracer arrows to a precedent cell containing an error
value
• If the tracer arrow is connected to a worksheet icon, double-click
the tracer arrow and open the cell references in the worksheet
• Continue to trace the error value to succeeding precedent cells.
When you locate a cell containing an error value that has no
precedent cells with errors, you have located the source of the
error

New Perspectives on Microsoft Office Excel 2007                        25
Tracing Error Values

New Perspectives on Microsoft Office Excel 2007   26

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 77 posted: 11/19/2010 language: English pages: 26
Description: Tutorial Excel Financial Functions document sample
How are you planning on using Docstoc?