COMPREHENSIVE

Excel Tutorial

Developing a Financial Analysis

XPXPObjectives• 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

XPXPObjectives• 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

XPXPWorking 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

XPXPWorking 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

XPXPCalculating 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

XPXPCreating 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

XPXPCreating an Amortization Schedule

New Perspectives on Microsoft Office Excel 2007 8

XPXPCalculating Yearly Interest and Principal Payments• 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

XPXPCalculating Yearly Interest and Principal Payments

New Perspectives on Microsoft Office Excel 2007 10

XPXPProjecting 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

XPXPInterpolating 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

XPXPExtrapolating 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

XPXPExtrapolating a Series of Values

New Perspectives on Microsoft Office Excel 2007 14

XPXPCalculating 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

XPXPCalculating Depreciation

New Perspectives on Microsoft Office Excel 2007 16

XPXPWorking 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

XPXPCalculating 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

XPXPDetermining 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

XPXPUsing the NPV Function• =NPV(rate, value1 [value2, value3, ...])

New Perspectives on Microsoft Office Excel 2007 20

XPXPUsing the NPV Function

New Perspectives on Microsoft Office Excel 2007 21

XPXPCalculating 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

XPXPCalculating the Internal Rate of Return

New Perspectives on Microsoft Office Excel 2007 23

XPXPExploring 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

XPXPTracing 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

XPXPTracing Error Values

New Perspectives on Microsoft Office Excel 2007 26