Excel Tutorial 9 Developing a Financial Analysis

1
Excel Tutorial 9Developing a Financial Analysis
2
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

3
Objectives

• Determine a payback period
• Calculate a net present value
• Calculate an internal rate of return
• Trace a formula error to its source

4
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

5
Working with Loans and Investments

• PMT(rate, nper, pv, fv0 type0)
• FV(rate, nper, pmt, pv0 type0)
• NPER(rate, pmt, pv, fv0 type0)
• PV(rate, nper, pmt, fv0 type0)
• RATE(nper, pmt, pv, fv0 type0)

6
Calculating a Loan Payment

• The functions to work with loans are the same
  ones you used to work with investments

7
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, fv0 ,type0)
• PPMT(rate, per, nper, pv, fv0 ,type0)

8
Creating an Amortization Schedule
9
Calculating 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)

10
Calculating Yearly Interest and Principal Payments
11
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

12
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

13
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

14
Extrapolating a Series of Values
15
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

16
Calculating Depreciation
17
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

18
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

19
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

20
Using the NPV Function

• NPV(rate, value1 value2, value3, ...)

21
Using the NPV Function
22
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, guess0.1)

23
Calculating the Internal Rate of Return
24
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)

25
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

26
Tracing Error Values