Evaluating the Financial Impact of Loans and Investments

1
Chapter 6

• Evaluating the Financial Impact of Loans and
  Investments

2
Chapter Introduction

• Understand Interest and the time value of money
• Explore the Excel time value of money functions
• Examine the accounting measures of profitability
• This concept should be familiar from ACC 201 and
  ACC 202

3
Introduction to Interest Calculations

• When you borrow money you pay interest
• When you loan money, you receive interest
• When you make a payment
• part of the payment is applied to interest
• Part of the payment is applied to principal

4
Types of Interest

• Simple interest
• Interest is paid only on the principal
• Many certificates of deposit work this way
• Compound interest
• Interest is added to the principal each period
• Interest is calculated on the principal plus any
  accrued interest
• Compounding can occur on different periods
• Annually, quarterly, monthly, daily

5
The PMT Function (Introduction)

• PMT is used to calculate the periodic payment on
  a loan
• The interest rate must be fixed
• There may be a residual value on the note at the
  end of the periods
• This is often referred to as a balloon payment
• An auto lease, for example, would have a residual
  note value

6
The PMT Function (Arguments 1)

• The first argument contains the interest rate per
  compounding period
• The second argument contains the number of
  periods
• The third argument contains the present loan
  value
• The fourth argument contains the future value
• If the loan is paid off at the end of the
  periods, the value is 0
• The final argument indicates when payments are
  made
• 0 (the default) indicates the end of the period
• 1 indicates the beginning of the period

7
The PMT Function (Arguments 2)
8
The PMT Function (Example)
9
Other Time Value of Money Functions

• Here we are just solving the same equation for a
  different varaible
• RATE determines the interest rate
• NPER determines the number of periods
• PMT determines the payment
• PV determines the present value of a transaction
• FV determines the future value of a transaction

10
The RATE Function (Introduction)

• Determines the interest rate per period based on
• The number of periods
• The payment
• The present value
• The future value
• The type

11
The RATE Function (Arguments)
12
The RATE Function (Example)
13
The NPER Function (Introduction)

• Determines the number of periods based on
• The interest rate
• The payment
• The present value
• The future value
• The type

14
The NPER Function (Arguments)
15
The NPER Function (Example)
16
The FV Function (Introduction)

• Determines the future value of a lump sum
• Its possible for FV to account for regular cash
  flows (periodic payments) per period

17
The FV Function (Arguments)
18
The FV Function (Example)
19
The PV Function (Introduction)

• Determines the present value of a cash flow
• Like FV, regular inflows or outflows are supported

20
THE PV Function (Arguments)
21
The PV Function (Example)
22
The IPMT Function (Introduction)

• Use IPMT to calculate the interest applicable to
  a particular period
• Use the initial balance for the present value no
  matter the period
• Use PPMT to calculate the principal applicable to
  a particular period
• The arguments to both functions are the same

23
The IPMT Function (Arguments)
24
The IPMT Function (Example)
25
The CUMIPMT Function (Introduction)

• CUMIPMT calculates the cumulative interest
  between two periods
• CUMPRINC calculates the cumulative principal
  between two periods
• The arguments to both functions are the same
• Functions require the analysis tool pack add-in
• The process to insert add-ins differs in Excel
  2007

26
The CUMIPMT Function (Arguments)
27
Introduction to Depreciation

• Depreciation allows the cost of an asset to be
  recovered over its useful life
• There are different depreciation methods and
  recovery periods based on the type of asset
• IM NOT A TAX ACCOUNTANT

28
Straight Line Depreciation

• An asset is depreciated equally over its useful
  life
• The cost of an asset at the end of its useful
  life is called the salvage value

29
The SLN Function

• The function accepts three arguments
• The first is the cost of the asset
• The second is the salvage value
• The third is the assets life
• You could also do this using simple arithmetic

30
The SLN Function (Example)
31
The DDB Function

• Its another depreciation method
• The depreciation for the first year (by default)
  is double that of the straight line depreciation
  method

32
The DDB Function (Arguments)
33
Other Depreciation Functions

• The Fixed-Declining Balance (DB) function
• (cost total depreciation from prior periods)
  rate
• The Variable-Declining Balance (VDB) function
  allows you to create custom depreciation factors

34
Measures of Profitability (Net Present Value)

• Net present value
• Future cash flows are discounted back to current
  dollars
• Discount rate typically takes into account
• The actual interest rate
• Additional interest to account for the level of
  risk
• Determining this value can be subjective
• Your book refers to this as the hurdle rate
• If the result is positive, the project is
  profitable
• The larger the positive number, the more
  profitable the project
• If the result is negative, the project is not
  profitable

35
The NPV Function

• Its used to calculate the net present value
• First argument contains the interest rate
• Second argument stores a range
• The range contains the cash flows
• Outflows should be negative
• Inflows should be positive

36
A Note on NPV

• The NPV function assumes that the first value
  (value 1) is in the first year
• Thus the cash flow is one year from now
• To fix the problem, move the first year out of
  the NPV function so that the cash flow is
  immediate instead of one year from now

37
The NPV Function (Example 1)
38
Measures of Profitability (Internal Rate of
Return)

• Simply put, its the discount (hurdle) rate at
  which net present value is equal to zero
• Both NPV and IRR have essentially equivalent
  utility
• Excel calculates IRR by guessing the correct
  discount rate for the NPV formula
• A NUM error occurs if Excel cannot guess the
  value
• The rate of return is the value where the net
  present value is 0
• NPV(IRR(values)) 0

39
Measures of Profitability (Return on Investment)

• ROI is the sum of all cash flows excluding the
  initial investment
• Accounting ROI does not consider the time value
  of money
• This value is divided by the initial project
  investment
• Note that there is no Excel ROI function
• Just SUM the range containing the cash flows and
  divide that value by the value of the initial
  investment

40
Measures of Profitability (Payback Method)

• The payback method is used to determine when cash
  flows exceed the initial investment
• Its the year (or period) when the cumulative
  cash flow is greater than 0
• The payback method does not discount cash flows
  so the view is not entirely realistic