Transcript Document

Excel Tutorial 9
Developing a Financial
Analysis
COMPREHENSIVE
Objectives
XP
• 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
•
•
•
•
XP
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
XP
• 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
•
•
•
•
•
XP
=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
XP
• 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
XP
• 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
XP
8
Calculating Yearly Interest and
Principal Payments
XP
• One way of calculating totals from several
payment periods is to use the Analysis Tool-Pak
add-in
• =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
XP
10
Projecting Future Income and
Expenses
XP
• 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
XP
• 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
XP
• 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
XP
14
Calculating Depreciation
XP
• 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
XP
16
Working with Payback Period
XP
• 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
XP
• 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
XP
• 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
XP
• =NPV(rate, value1 [value2, value3, ...])
New Perspectives on Microsoft Office Excel 2007
20
Using the NPV Function
New Perspectives on Microsoft Office Excel 2007
XP
21
Calculating the Internal Rate of
Return
XP
• 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
XP
23
Exploring other Financial Functions
XP
• 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
XP
• 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
XP
26