FInance Questions

9 – *

CHAPTER 9
Capital Investment Decision Basics
Project classifications
Role of financial analysis
Time value of money
Project evaluation measures
Payback
NPV
IRR
Project scoring
Post audit
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Capital Investment Basics
Capital investment decisions (capital budgeting) involve the process of analyzing proposed new investments in land, buildings, and equipment.
Such decisions:
Typically are long-term in nature
Often involve large expenditures
Usually define strategic direction
Thus, these decisions are very important to businesses.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Project Classifications
Proposed investments (projects) are classified according to purpose and size. For example,
Mandatory replacement
Expansion of existing services
Less than $1 million
$1 million or more
Expansion into new services
Less than $1 million
$1 million or more
How are such classifications used?
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Role of Financial Analysis
For investor-owned businesses, financial analysis identifies those projects that are expected to contribute to owners’ wealth.
For not-for-profit businesses, financial analysis identifies a project’s expected effect on the business’s financial condition. Why is this important?
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Overview of Capital Investment Financial Analysis

Estimate the project’s cash flows.
2. Assess the project’s riskiness.
3. Estimate the project cost of capital (opportunity cost of capital or discount rate).
4. Measure the financial impact.

In this chapter, we focus on Step 4 (measuring the financial impact). The first three steps will be covered in Chapter 10.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Capital Budgeting Example
Assume that Midtown Clinic is evaluating the purchase of a x-ray machine.
Cost = $240,000.
Expected life = 4 years.
Corporate cost of capital = 10%.
The expected cash flows from buying and operating the equipment are listed on the next slide.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

0
$100
1
$105
2
$110
3
$141
4
($240)
Note that the cash flows have been laid out on a time line. Also, the cash flows are merely estimates based on volume, reimbursement rate, and operating cost assumptions.
Project Cash Flows (in thousands)
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Breakeven Analysis
It is very difficult to make judgments about the financial attractiveness of a project by looking at its cash flows.
Breakeven analysis is one way to help interpret the information embedded in the cash flows.
We will focus on time breakeven, which is measured by payback (payback period).
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Payback Illustration
0
$100
1
$105
2
$110
3
$141
4
($240)
Cumulative CFs:
$ 75
($240)
($ 35)
($140)
$216
Payback = 2 + $35 / $110 = 2.3 years.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Advantages of Payback:
1. Easy to calculate and understand.
2. Provides an indication of a project’s risk and liquidity.
Disadvantages of Payback:
1. Ignores time value (discussed next).
2. Ignores all cash flows that occur after the payback period.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Time Value of Money
Time value analysis is necessary because money has time value.
A dollar in hand today is worth more than a dollar to be received in the future. Why?
Because of time value, the values of future dollars must be adjusted before they can be compared to current dollars.
Discounted cash flow (DCF) analysis is the name given to techniques that account for the time value of money.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

What is the FV after 3 years of
a $100 lump sum invested at 10%?

FV = ?

0
1
2
3
10%
-$100
Finding future values (moving to the right along the time line) is called compounding.
For ease, assume interest is paid annually.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

After 1 year:
FV1 = PV + INT1 = PV + (PV × I)
= PV × (1 + I)
= $100 × 1.10 = $110.00.
After 2 years:
FV2 = FV1 + INT2
= FV1 + (FV1 × I) = FV1 × (1 + I)
= PV × (1 + I) × (1 + I) = PV × (1 + I)2
= $100 × (1.10)2 = $121.00.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

After 3 years:
FV3 = FV2 + I3
= PV x (1 + I)3
= 100 x (1.10)3
= $133.10.
In general,
FVN = PV x (1 + I)N .
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Three Primary Methods to Find FVs
Solve the FV equation using a regular (non-financial) calculator.
Use a financial calculator; that is, one with financial functions.
Use a computer with a spreadsheet program such as Excel.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Non-Financial Calculator Solution
$133.10
0
1
2
3
10%
-$100
$110.00
$121.00
$100 x 1.10 x 1.10 x 1.10 = $133.10.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

3 10 -100 0
N I/YR PV PMT FV
133.10
(1) Set your calculator on P/YR = 1, END.
INPUTS
OUTPUT
Notes:
(2) For lump sums, the PMT key is not used. Either clear the calculator before you start or enter PMT = 0.

Financial Calculator Solution
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Financial Calculator Solution

Spreadsheet Solution (Optional Slide)
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

10%

What is the PV of $100 due
in 3 years if I = 10%?
$100
0
1
2
3
PV = ?
Finding present values (moving to the left along the time line) is called discounting. The interest rate applied is called the discount rate.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Solve FVN = PV x (1 + I )N for PV
PV = $100 ÷ (1.10)3
= $100 × 0.7513 = $75.13.
PV = FVN ÷ (1 + I )N.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Time Line Solution
$100
0
1
2
3
10%
$75.13
$82.64
$90.91
$100  1.10  1.10  1.10 = $75.13.
Note that the calculated present value ($75.13), when invested at 10 percent for 3 years, will produce the starting future value ($100).
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Financial Calculator Solution

3 10 0 100

-75.13
Either PV or FV must be negative on most calculators. Here, PV = -75.13. Put in $75.13 today, take out $100 after 3 years.
INPUTS
OUTPUT
N
I/YR
PV
PMT
FV
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Financial Calculator Solution

9 – *

Opportunity Cost Rate
On the present value illustration we needed to apply a discount rate.
The appropriate discount rate is the opportunity cost rate or opportunity cost of capital.
It is the rate that could be earned on alternative investments of similar risk.
In capital investment analyses, the corporate cost of capital typically is used as the benchmark discount rate.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Profitability (ROI) Analysis
Return on investment (ROI) analysis focuses on a project’s financial return.
Financial returns can be measured either in dollar terms or in rate of return (percentage) terms.
Net present value (NPV) measures a project’s time value adjusted dollar return.
Internal rate of return (IRR) measures a project’s rate of (percentage) return.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Net Present Value (NPV)
NPV measures return on investment (ROI) in dollar terms.
NPV is merely the sum of the present values of the project’s cash flows.
The discount rate used in project analysis is called the project cost of capital. If we assume that the illustrative project has average risk, its project cost of capital is the corporate cost of capital, 10%.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Net Present Value (NPV) Calculation
0
$100
1
$105
2
$110
3
$141
4
($240.00)
10%
90.91
86.78
82.64
96.30
$116.63
Thus, the project’s NPV is about $117,000.
Note that financial calculators have functions that perform capital investment analyses.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Sheet1

CHAPTER 3

Solve Lump Sum FV

A B C D

2 3 Number of periods

3 $ 100.00 Present value

4 10.0% Interest rate

6 $ 133.10 =100*(1.10)^3 (entered into Cell A6)

8 $ 133.10 =A3*(1+A4)^A2 (entered into Cell A8)

10 $133.10 =FV(A4,A2,,-A3) (entered into Cell A10)

Solve Lump Sum PV

A B C D

2 3 Number of periods

3 $ 100.00 Future value

4 10.0% Interest rate

6 $ 75.13 =A3/(1+A4)^A2 (entered into Cell A6)

8 $ 75.13 =PV(A4,A2,,-A3) (entered into Cell A8)

Solve for I

A B C D

2 5 Number of periods

3 $ (75.00) Present value

4 $ 200.00 Future value

8 21.7% =RATE(A2,,A3,A4) (entered into Cell A8)

Solve for N

A B C D

2 20.0% Interest rate

3 $ (1.00) Present value

4 $ 2.00 Future value

8 3.8 =NPER(A2,,A3,A4) (entered into Cell A8)

Solve Regular Annuity FV

A B C D

2 3 Number of periods

3 $ (100.00) Payment

4 10.0% Interest rate

8 $ 331.00 =FV(A4,A2,A3) (entered into Cell A8)

Solve Regular Annuity PV

A B C D

2 3 Number of periods

3 $ (100.00) Payment

4 10.0% Interest rate

8 $ 248.69 =PV(A4,A2,A3) (entered into Cell A8)

Solve Regular Annuity FV with EAR

A B C D

2 3 Number of periods

3 $ (100.00) Payment

4 10.3% Interest rate

8 $ 331.80 =FV(A4,A2,A3) (entered into Cell A8)

Solve Annuity Due FV

A B C D

2 3 Nper Number of periods

3 $ (100.00) Pmt Payment

4 5.0% Rate Interest rate

6 $ 331.01 =FV(A4,A2,A3,,1) (entered into Cell A6)

8 $ 331.01 =FV(A4,A2,A3)*(1+A4) (entered into Cell A8)

Solve Annuity Due PV

A B C D

2 3 Nper Number of periods

3 $ (100.00) Pmt Payment

4 5.0% Rate Interest rate

6 $ 285.94 =PV(A4,A2,A3,,1) (entered into Cell A6)

8 $ 285.94 =PV(A4,A2,A3)*(1+A4) (entered into Cell A8)

Solve Perpetuity PV

A B C D

3 $ 100.00 Payment

4 10.0% Interest rate

8 $ 1,000.00 =A3/A2 (entered into Cell A8)

NPV (without initial investment)

A B C D

2 10.0% Interest rate

4 $ 100 Year 1 CF

5 300 Year 2 CF

6 300 Year 3 CF

7 (50) Year 4 CF

10 $530.09 =NPV(A2,A4:A7) (entered into Cell A10)

NPV (with initial investment)

A B C D

2 8.0% Interest rate

3 $ (1,500) Year 0 CF

4 310 Year 1 CF

5 400 Year 2 CF

6 500 Year 3 CF

7 750 Year 4 CF

10 $ 78 =NPV(A2,A4:A7)+A3 (entered into Cell A10)

IRR

A B C D

2 8.0% Interest rate guess

3 $ (1,500) Year 0 CF

4 310 Year 1 CF

5 400 Year 2 CF

6 500 Year 3 CF

7 750 Year 4 CF

10 10.0% =IRR(A3:A7,A2) (entered into Cell A10)

Solve Lump Sum FV (Annual Compounding)

A B C D

2 3 Nper Number of periods

3 $ 100.00 Pv Present value

4 6.0% Rate Interest rate

6 $ 119.10 =100*(1.06)^3 (entered into Cell A6)

8 $ 119.10 =A3*(1+A4)^A2 (entered into Cell A8)

10 $ 119.10 =FV(A4,A2,,-A3) (entered into Cell A10)

Solve Lump Sum FV (Semiannual Compounding)

A B C D

2 6 Nper Number of periods

3 $ 100.00 Pv Present value

4 3.0% Rate Interest rate

6 $ 119.41 =100*(1.03)^6 (entered into Cell A6)

8 $ 119.41 =A3*(1+A4)^A2 (entered into Cell A8)

10 $ 119.41 =FV(A4,A2,,-A3) (entered into Cell A10)

EAR

A B C D

3 3 Nper Number of periods

4 $ (100.00) Pv Present value

5 $ 119.41 Fv Future value

8 6.09% =RATE(A3,,A4,A5) (entered into Cell A8)

Amortization Annuity Payment

A B C D

2 6.0% Rate Interest rate

3 3 Nper Number of periods

4 $ 1,000,000 Pv Present value

8 $ 374,110 =PMT(A2,A3,-A4) (entered into Cell A8)

CHAPTER 4

ROI 1

A B C D

3 1 Nper Number of periods

4 $ (950.00) Pv Present value

5 $ 1,000 Fv Future value

8 5.26% =RATE(A3,,A4,A5) (entered into Cell A8)

ROI 2

A B C D

3 1 Nper Number of periods

4 $ (950.00) Pv Present value

5 $ 2,000 Fv Future value

8 110.53% =RATE(A3,,A4,A5) (entered into Cell A8)

ROI 3

A B C D

3 1 Nper Number of periods

4 $ (950.00) Pv Present value

5 $ 0.01 Fv Future value

8 -100.00% =RATE(A3,,A4,A5) (entered into Cell A8)

CHAPTER 7

Bond Value 1 (15 years to maturity)

A B C D

2 10.0% Interest rate

4 $ 100 Year 1 coupon

5 100 Year 2 coupon

6 100 Year 3 coupon

7 100 Year 4 coupon

8 100 Year 5 coupon

9 100 Year 6 coupon

10 100 Year 7 coupon

11 100 Year 8 coupon

12 100 Year 9 coupon

13 100 Year 10 coupon

14 100 Year 11 coupon

15 100 Year 12 coupon

16 100 Year 13 coupon

17 100 Year 14 coupon

18 1,100 Year 15 coupon + Principal

20 $1,000.00 =NPV(A2,A4:A18) (entered into Cell A20)

A B C D

2 15 Number of payments

3 $ 100.00 Payment (coupon amount)

4 $ 1,000.00 Future value (principal)

5 10.0% Interest rate

8 $ 1,000.00 =-PV(A5,A2,A3,A4) (entered into Cell A8)

Bond Value (14 years to maturity)

A B C D

2 10.0% Rate Interest rate

4 $ 100

5 100 Year 1 coupon

6 100 Year 2 coupon

7 100 Year 3 coupon

8 100 Year 4 coupon

9 100 Year 5 coupon

10 100 Year 6 coupon

11 100 Year 7 coupon

12 100 Year 8 coupon

13 100 Year 9 coupon

14 100 Year 10 coupon

15 100 Year 11 coupon

16 100 Year 12 coupon

17 100 Year 13 coupon

18 1,100 Year 14 coupon + Principal

20 $1,000.00 =NPV(A2,A5:A18) (entered into Cell A20)

A B C D

2 14 Number of payments

3 $ 100.00 Payment (coupon amount)

4 $ 1,000.00 Future value (principal)

5 10.0% Interest rate

8 $ 1,000.00 =-PV(A5,A2,A3,A4) (entered into Cell A8)

Bond Value (14 years to maturity and 5% required rate)

A B C D

2 5.0% Rate Interest rate

4 $ 100

5 100 Year 1 coupon

6 100 Year 2 coupon

7 100 Year 3 coupon

8 100 Year 4 coupon

9 100 Year 5 coupon

10 100 Year 6 coupon

11 100 Year 7 coupon

12 100 Year 8 coupon

13 100 Year 9 coupon

14 100 Year 10 coupon

15 100 Year 11 coupon

16 100 Year 12 coupon

17 100 Year 13 coupon

18 1,100 Year 14 coupon + Principal

20 $1,494.93 =NPV(A2,A5:A18) (entered into Cell A20)

A B C D

2 14 Number of payments

3 $ 100.00 Payment (coupon amount)

4 $ 1,000.00 Future value (principal)

5 5.0% Interest rate

8 $ 1,494.93 =-PV(A5,A2,A3,A4) (entered into Cell A8)

Bond Value (14 years to maturity and 15% required rate)

A B C D

2 15.0% Rate Interest rate

4 $ 100

5 100 Year 1 coupon

6 100 Year 2 coupon

7 100 Year 3 coupon

8 100 Year 4 coupon

9 100 Year 5 coupon

10 100 Year 6 coupon

11 100 Year 7 coupon

12 100 Year 8 coupon

13 100 Year 9 coupon

14 100 Year 10 coupon

15 100 Year 11 coupon

16 100 Year 12 coupon

17 100 Year 13 coupon

18 1,100 Year 14 coupon + Principal

20 $713.78 =NPV(A2,A5:A18) (entered into Cell A20)

A B C D

2 14 Number of payments

3 $ 100.00 Payment (coupon amount)

4 $ 1,000.00 Future value (principal)

5 15.0% Interest rate

8 $ 713.78 =-PV(A5,A2,A3,A4) (entered into Cell A8)

Bond Value (13 years to maturity and 5% required rate)

A B C D

2 5.0% Rate Interest rate

4 $ 100

5 100

6 100 Value 1 Year 1 coupon

7 100 Year 2 coupon

8 100 Year 3 coupon

9 100 Year 4 coupon

10 100 Year 5 coupon

11 100 Year 6 coupon

12 100 Year 7 coupon

13 100 Year 8 coupon

14 100 Year 9 coupon

15 100 Year 10 coupon

16 100 Year 11 coupon

17 100 Year 12 coupon

18 1,100 Value 1 Year 13 coupon + Principal

20 $1,469.68 =NPV(A2,A6:A18) (entered into Cell A20)

Bond Value (Zero coupon)

A B C D

2 10.0% Rate Interest rate

4 $ – 0 Value 1 Year 1 coupon

5 – 0 Year 2 coupon

6 – 0 Year 3 coupon

7 – 0 Year 4 coupon

8 – 0 Year 5 coupon

9 – 0 Year 6 coupon

10 – 0 Year 7 coupon

11 – 0 Year 8 coupon

12 – 0 Year 9 coupon

13 – 0 Year 10 coupon

14 – 0 Year 11 coupon

15 – 0 Year 12 coupon

16 – 0 Year 13 coupon

17 – 0 Year 14 coupon

18 1,000 Value 1 Year 15 coupon + Principal

20 $239.39 =NPV(A2,A4:A18) (entered into Cell A20)

Bond YTM

A B C D

2 10.0% Interest rate guess

4 $ (1,494.93) Bond price

5 100 Year 1 coupon

6 100 Year 2 coupon

7 100 Year 3 coupon

8 100 Year 4 coupon

9 100 Year 5 coupon

10 100 Year 6 coupon

11 100 Year 7 coupon

12 100 Year 8 coupon

13 100 Year 9 coupon

14 100 Year 10 coupon

15 100 Year 11 coupon

16 100 Year 12 coupon

17 100 Year 13 coupon

18 1,100 Year 14 coupon + Principal

20 5.0% =IRR(A4:A18:A2) (entered into Cell A20)

A B C D

2 14 Number of payments

3 $ (1,494.93) Present value (bond price)

4 $ 100.00 Payment (coupon amount)

5 $ 1,000.00 Future value (principal)

8 5.0% =RATE(A2,A4,A3,A5) (entered into Cell A8)

A B C D

2 10.0% Interest rate guess

4 $ (713.78) Bond price

5 100 Year 1 coupon

6 100 Year 2 coupon

7 100 Year 3 coupon

8 100 Year 4 coupon

9 100 Year 5 coupon

10 100 Year 6 coupon

11 100 Year 7 coupon

12 100 Year 8 coupon

13 100 Year 9 coupon

14 100 Year 10 coupon

15 100 Year 11 coupon

16 100 Year 12 coupon

17 100 Year 13 coupon

18 1,100 Year 14 coupon + Principal

20 15.0% =IRR(A4:A18:A2) (entered into Cell A20)

A B C D

2 14 Number of payments

3 $ (713.78) Present value (bond price)

4 $ 100.00 Payment (coupon amount)

5 $ 1,000.00 Future value (principal)

8 15.0% =RATE(A2,A4,A3,A5) (entered into Cell A8)

Bond YTC

A B C D

2 10.0% Rate Interest rate

4 $ (713.78) Bond price

5 100 Year 1 coupon

6 100 Year 2 coupon

7 100 Year 3 coupon

8 100 Year 4 coupon

9 1,200 Year 5 coupon + Prin. + CP

15 21.1% =IRR(A4:A9:A2) (entered into Cell A15)

A B C D

2 5 Number of payments

3 $ (713.78) Present value (bond price)

4 $ 100.00 Payment (coupon amount)

5 $ 1,100.00 Future value (principal)

8 21.1% =RATE(A2,A4,A3,A5) (entered into Cell A8)

Semiannual Compounding PV

A B C D

2 28 Number of payments

3 $ 50.00 Payment (coupon amount)

4 $ 1,000.00 Future value (principal)

5 2.5% Interest rate

8 $ 1,499.12 =-PV(A5,A2,A3,A4) (entered into Cell A8)

Semiannual Compounding YTM

A B C D

2 28 Number of payments

3 $ (1,400.00) Present value (bond price)

4 $ 50.00 Payment (coupon amount)

5 $ 1,000.00 Future value (principal)

8 2.90% =RATE(A2,A4,A3,A5) (entered into Cell A8)

Constant Growth Stock Valuation

A B C D

2 $ 1.82 Last dividend payment

3 10.0% E(g) Expected growth rate

4 16.0% Required rate of return

8 $ 33.37 =A2*(1+A3)/(A4-A3) (entered into Cell A8)

SML

A B C D

2 1.6 b Beta coefficient

3 5.0% RF Risk-free rate

4 12.0% Required return on the market

8 16.2% =A3+(A4-A3)*A2 (entered into Cell A8)

Constant Growth Stock Expected Rate of Return

A B C D

2 $ 33.33 Stock price

3 $ 2.00 Next expected dividend

4 10.0% E(g) Expected growth rate

8 16.0% =A3/A2+A4 (entered into Cell A8)

Nonconstant Growth Stock Valuation

A B C D

2 30.0% Nonconstant growth rate

3 10.0% Constant growth rate

4 16.0%

5 $ 1.82 Last dividend payment

7 $ 2.366 =A5*(1+A2) (entered into Cell A7)

8 $ 3.076 =A7*(1+A2) (entered into Cell A8)

9 $ 3.999 =A8*(1+A2) (entered into Cell A9)

10 $ 4.398 =A9*(1+A3) (entered into Cell A10)

11 $ 73.307 =A10/(A4-A3) (entered into Cell A10)

13 $ 53.85 =NPV(A4,A7:A9)+PV(A4,3,,-A11) (entered into Cell A13)

CHAPTER 9

Semiannual Compounding YTM

A B C D

2 50 Nper Number of payments

3 $ (1,114.69) Pv Present value (bond price)

4 $ 35.00 Pmt Payment (coupon amount)

5 $ 1,000.00 Fv Future value (principal)

8 3.05% =RATE(A2,A4,A3,A5) (entered into Cell A8)

CHAPTER 11

A B C D

2 10.0% Project cost of capital

3 $ (240) Cash flow 0 (000s)

4 100 Cash flow 1 (000s)

5 105 Cash flow 2 (000s)

6 110 Cash flow 3 (000s)

7 141 Cash flow 4 (000s)

10 $ 117 =NPV(A2,A4:A7)+A3 (entered into Cell A10)

Proj IRR

A B C D

2 10.0% Project cost of capital

3 $ (240) Cash flow 0 (000s)

4 100 Cash flow 1 (000s)

5 105 Cash flow 2 (000s)

6 110 Cash flow 3 (000s)

7 141 Cash flow 4 (000s)

10 29.7% =IRR(A2,A3:A7) (entered into Cell A10)

Proj MIRR

A B C D

2 10.0% Project cost of capital

3 $ (240) Cash flow 0 (000s)

4 100 Cash flow 1 (000s)

5 105 Cash flow 2 (000s)

6 110 Cash flow 3 (000s)

7 141 Cash flow 4 (000s)

10 21.4% =MIRR(A3:A7,A2,A2) (entered into Cell A10)

9 – *

Interpretation of the NPV
NPV is the excess dollar contribution of the project to the value of the business.
A positive NPV signifies that the project will enhance the financial condition of the business.
The greater the NPV, the more attractive the project financially.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Discussion Item
What is the meaning of an NPV of $0?
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Internal Rate of Return (IRR)
IRR measures ROI in percentage (rate of return) terms.
It is the discount rate that forces the PV of the inflows to equal the cost of the project. (In other words, it is the discount rate that forces the project’s NPV to equal $0.)
IRR is the project’s expected rate of return.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

IRR Calculation (Cont.)
0
$100
1
$105
2
$110
3
$141
4
($240.00)
77.11
62.46
50.48
49.95
$240.00
29.7%
Thus, the project’s IRR is 29.7%.
$ 0 = NPV.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Sheet1

CHAPTER 3

Solve Lump Sum FV

A B C D

2 3 Number of periods

3 $ 100.00 Present value

4 10.0% Interest rate

6 $ 133.10 =100*(1.10)^3 (entered into Cell A6)

8 $ 133.10 =A3*(1+A4)^A2 (entered into Cell A8)

10 $133.10 =FV(A4,A2,,-A3) (entered into Cell A10)

Solve Lump Sum PV

A B C D

2 3 Number of periods

3 $ 100.00 Future value

4 10.0% Interest rate

6 $ 75.13 =A3/(1+A4)^A2 (entered into Cell A6)

8 $ 75.13 =PV(A4,A2,,-A3) (entered into Cell A8)

Solve for I

A B C D

2 5 Number of periods

3 $ (75.00) Present value

4 $ 200.00 Future value

8 21.7% =RATE(A2,,A3,A4) (entered into Cell A8)

Solve for N

A B C D

2 20.0% Interest rate

3 $ (1.00) Present value

4 $ 2.00 Future value

8 3.8 =NPER(A2,,A3,A4) (entered into Cell A8)

Solve Regular Annuity FV

A B C D

2 3 Number of periods

3 $ (100.00) Payment

4 10.0% Interest rate

8 $ 331.00 =FV(A4,A2,A3) (entered into Cell A8)

Solve Regular Annuity PV

A B C D

2 3 Number of periods

3 $ (100.00) Payment

4 10.0% Interest rate

8 $ 248.69 =PV(A4,A2,A3) (entered into Cell A8)

Solve Regular Annuity FV with EAR

A B C D

2 3 Number of periods

3 $ (100.00) Payment

4 10.3% Interest rate

8 $ 331.80 =FV(A4,A2,A3) (entered into Cell A8)

Solve Annuity Due FV

A B C D

2 3 Nper Number of periods

3 $ (100.00) Pmt Payment

4 5.0% Rate Interest rate

6 $ 331.01 =FV(A4,A2,A3,,1) (entered into Cell A6)

8 $ 331.01 =FV(A4,A2,A3)*(1+A4) (entered into Cell A8)

Solve Annuity Due PV

A B C D

2 3 Nper Number of periods

3 $ (100.00) Pmt Payment

4 5.0% Rate Interest rate

6 $ 285.94 =PV(A4,A2,A3,,1) (entered into Cell A6)

8 $ 285.94 =PV(A4,A2,A3)*(1+A4) (entered into Cell A8)

Solve Perpetuity PV

A B C D

3 $ 100.00 Payment

4 10.0% Interest rate

8 $ 1,000.00 =A3/A2 (entered into Cell A8)

NPV (without initial investment)

A B C D

2 10.0% Interest rate

4 $ 100 Year 1 CF

5 300 Year 2 CF

6 300 Year 3 CF

7 (50) Year 4 CF

10 $530.09 =NPV(A2,A4:A7) (entered into Cell A10)

NPV (with initial investment)

A B C D

2 8.0% Interest rate

3 $ (1,500) Year 0 CF

4 310 Year 1 CF

5 400 Year 2 CF

6 500 Year 3 CF

7 750 Year 4 CF

10 $ 78 =NPV(A2,A4:A7)+A3 (entered into Cell A10)

IRR

A B C D

2 8.0% Interest rate guess

3 $ (1,500) Year 0 CF

4 310 Year 1 CF

5 400 Year 2 CF

6 500 Year 3 CF

7 750 Year 4 CF

10 10.0% =IRR(A3:A7,A2) (entered into Cell A10)

Solve Lump Sum FV (Annual Compounding)

A B C D

2 3 Nper Number of periods

3 $ 100.00 Pv Present value

4 6.0% Rate Interest rate

6 $ 119.10 =100*(1.06)^3 (entered into Cell A6)

8 $ 119.10 =A3*(1+A4)^A2 (entered into Cell A8)

10 $ 119.10 =FV(A4,A2,,-A3) (entered into Cell A10)

Solve Lump Sum FV (Semiannual Compounding)

A B C D

2 6 Nper Number of periods

3 $ 100.00 Pv Present value

4 3.0% Rate Interest rate

6 $ 119.41 =100*(1.03)^6 (entered into Cell A6)

8 $ 119.41 =A3*(1+A4)^A2 (entered into Cell A8)

10 $ 119.41 =FV(A4,A2,,-A3) (entered into Cell A10)

EAR

A B C D

3 3 Nper Number of periods

4 $ (100.00) Pv Present value

5 $ 119.41 Fv Future value

8 6.09% =RATE(A3,,A4,A5) (entered into Cell A8)

Amortization Annuity Payment

A B C D

2 6.0% Rate Interest rate

3 3 Nper Number of periods

4 $ 1,000,000 Pv Present value

8 $ 374,110 =PMT(A2,A3,-A4) (entered into Cell A8)

CHAPTER 4

ROI 1

A B C D

3 1 Nper Number of periods

4 $ (950.00) Pv Present value

5 $ 1,000 Fv Future value

8 5.26% =RATE(A3,,A4,A5) (entered into Cell A8)

ROI 2

A B C D

3 1 Nper Number of periods

4 $ (950.00) Pv Present value

5 $ 2,000 Fv Future value

8 110.53% =RATE(A3,,A4,A5) (entered into Cell A8)

ROI 3

A B C D

3 1 Nper Number of periods

4 $ (950.00) Pv Present value

5 $ 0.01 Fv Future value

8 -100.00% =RATE(A3,,A4,A5) (entered into Cell A8)

CHAPTER 7

Bond Value 1 (15 years to maturity)

A B C D

2 10.0% Interest rate

4 $ 100 Year 1 coupon

5 100 Year 2 coupon

6 100 Year 3 coupon

7 100 Year 4 coupon

8 100 Year 5 coupon

9 100 Year 6 coupon

10 100 Year 7 coupon

11 100 Year 8 coupon

12 100 Year 9 coupon

13 100 Year 10 coupon

14 100 Year 11 coupon

15 100 Year 12 coupon

16 100 Year 13 coupon

17 100 Year 14 coupon

18 1,100 Year 15 coupon + Principal

20 $1,000.00 =NPV(A2,A4:A18) (entered into Cell A20)

A B C D

2 15 Number of payments

3 $ 100.00 Payment (coupon amount)

4 $ 1,000.00 Future value (principal)

5 10.0% Interest rate

8 $ 1,000.00 =-PV(A5,A2,A3,A4) (entered into Cell A8)

Bond Value (14 years to maturity)

A B C D

2 10.0% Rate Interest rate

4 $ 100

5 100 Year 1 coupon

6 100 Year 2 coupon

7 100 Year 3 coupon

8 100 Year 4 coupon

9 100 Year 5 coupon

10 100 Year 6 coupon

11 100 Year 7 coupon

12 100 Year 8 coupon

13 100 Year 9 coupon

14 100 Year 10 coupon

15 100 Year 11 coupon

16 100 Year 12 coupon

17 100 Year 13 coupon

18 1,100 Year 14 coupon + Principal

20 $1,000.00 =NPV(A2,A5:A18) (entered into Cell A20)

A B C D

2 14 Number of payments

3 $ 100.00 Payment (coupon amount)

4 $ 1,000.00 Future value (principal)

5 10.0% Interest rate

8 $ 1,000.00 =-PV(A5,A2,A3,A4) (entered into Cell A8)

Bond Value (14 years to maturity and 5% required rate)

A B C D

2 5.0% Rate Interest rate

4 $ 100

5 100 Year 1 coupon

6 100 Year 2 coupon

7 100 Year 3 coupon

8 100 Year 4 coupon

9 100 Year 5 coupon

10 100 Year 6 coupon

11 100 Year 7 coupon

12 100 Year 8 coupon

13 100 Year 9 coupon

14 100 Year 10 coupon

15 100 Year 11 coupon

16 100 Year 12 coupon

17 100 Year 13 coupon

18 1,100 Year 14 coupon + Principal

20 $1,494.93 =NPV(A2,A5:A18) (entered into Cell A20)

A B C D

2 14 Number of payments

3 $ 100.00 Payment (coupon amount)

4 $ 1,000.00 Future value (principal)

5 5.0% Interest rate

8 $ 1,494.93 =-PV(A5,A2,A3,A4) (entered into Cell A8)

Bond Value (14 years to maturity and 15% required rate)

A B C D

2 15.0% Rate Interest rate

4 $ 100

5 100 Year 1 coupon

6 100 Year 2 coupon

7 100 Year 3 coupon

8 100 Year 4 coupon

9 100 Year 5 coupon

10 100 Year 6 coupon

11 100 Year 7 coupon

12 100 Year 8 coupon

13 100 Year 9 coupon

14 100 Year 10 coupon

15 100 Year 11 coupon

16 100 Year 12 coupon

17 100 Year 13 coupon

18 1,100 Year 14 coupon + Principal

20 $713.78 =NPV(A2,A5:A18) (entered into Cell A20)

A B C D

2 14 Number of payments

3 $ 100.00 Payment (coupon amount)

4 $ 1,000.00 Future value (principal)

5 15.0% Interest rate

8 $ 713.78 =-PV(A5,A2,A3,A4) (entered into Cell A8)

Bond Value (13 years to maturity and 5% required rate)

A B C D

2 5.0% Rate Interest rate

4 $ 100

5 100

6 100 Value 1 Year 1 coupon

7 100 Year 2 coupon

8 100 Year 3 coupon

9 100 Year 4 coupon

10 100 Year 5 coupon

11 100 Year 6 coupon

12 100 Year 7 coupon

13 100 Year 8 coupon

14 100 Year 9 coupon

15 100 Year 10 coupon

16 100 Year 11 coupon

17 100 Year 12 coupon

18 1,100 Value 1 Year 13 coupon + Principal

20 $1,469.68 =NPV(A2,A6:A18) (entered into Cell A20)

Bond Value (Zero coupon)

A B C D

2 10.0% Rate Interest rate

4 $ – 0 Value 1 Year 1 coupon

5 – 0 Year 2 coupon

6 – 0 Year 3 coupon

7 – 0 Year 4 coupon

8 – 0 Year 5 coupon

9 – 0 Year 6 coupon

10 – 0 Year 7 coupon

11 – 0 Year 8 coupon

12 – 0 Year 9 coupon

13 – 0 Year 10 coupon

14 – 0 Year 11 coupon

15 – 0 Year 12 coupon

16 – 0 Year 13 coupon

17 – 0 Year 14 coupon

18 1,000 Value 1 Year 15 coupon + Principal

20 $239.39 =NPV(A2,A4:A18) (entered into Cell A20)

Bond YTM

A B C D

2 10.0% Interest rate guess

4 $ (1,494.93) Bond price

5 100 Year 1 coupon

6 100 Year 2 coupon

7 100 Year 3 coupon

8 100 Year 4 coupon

9 100 Year 5 coupon

10 100 Year 6 coupon

11 100 Year 7 coupon

12 100 Year 8 coupon

13 100 Year 9 coupon

14 100 Year 10 coupon

15 100 Year 11 coupon

16 100 Year 12 coupon

17 100 Year 13 coupon

18 1,100 Year 14 coupon + Principal

20 5.0% =IRR(A4:A18:A2) (entered into Cell A20)

A B C D

2 14 Number of payments

3 $ (1,494.93) Present value (bond price)

4 $ 100.00 Payment (coupon amount)

5 $ 1,000.00 Future value (principal)

8 5.0% =RATE(A2,A4,A3,A5) (entered into Cell A8)

A B C D

2 10.0% Interest rate guess

4 $ (713.78) Bond price

5 100 Year 1 coupon

6 100 Year 2 coupon

7 100 Year 3 coupon

8 100 Year 4 coupon

9 100 Year 5 coupon

10 100 Year 6 coupon

11 100 Year 7 coupon

12 100 Year 8 coupon

13 100 Year 9 coupon

14 100 Year 10 coupon

15 100 Year 11 coupon

16 100 Year 12 coupon

17 100 Year 13 coupon

18 1,100 Year 14 coupon + Principal

20 15.0% =IRR(A4:A18:A2) (entered into Cell A20)

A B C D

2 14 Number of payments

3 $ (713.78) Present value (bond price)

4 $ 100.00 Payment (coupon amount)

5 $ 1,000.00 Future value (principal)

8 15.0% =RATE(A2,A4,A3,A5) (entered into Cell A8)

Bond YTC

A B C D

2 10.0% Rate Interest rate

4 $ (713.78) Bond price

5 100 Year 1 coupon

6 100 Year 2 coupon

7 100 Year 3 coupon

8 100 Year 4 coupon

9 1,200 Year 5 coupon + Prin. + CP

15 21.1% =IRR(A4:A9:A2) (entered into Cell A15)

A B C D

2 5 Number of payments

3 $ (713.78) Present value (bond price)

4 $ 100.00 Payment (coupon amount)

5 $ 1,100.00 Future value (principal)

8 21.1% =RATE(A2,A4,A3,A5) (entered into Cell A8)

Semiannual Compounding PV

A B C D

2 28 Number of payments

3 $ 50.00 Payment (coupon amount)

4 $ 1,000.00 Future value (principal)

5 2.5% Interest rate

8 $ 1,499.12 =-PV(A5,A2,A3,A4) (entered into Cell A8)

Semiannual Compounding YTM

A B C D

2 28 Number of payments

3 $ (1,400.00) Present value (bond price)

4 $ 50.00 Payment (coupon amount)

5 $ 1,000.00 Future value (principal)

8 2.90% =RATE(A2,A4,A3,A5) (entered into Cell A8)

Constant Growth Stock Valuation

A B C D

2 $ 1.82 Last dividend payment

3 10.0% E(g) Expected growth rate

4 16.0% Required rate of return

8 $ 33.37 =A2*(1+A3)/(A4-A3) (entered into Cell A8)

SML

A B C D

2 1.6 b Beta coefficient

3 5.0% RF Risk-free rate

4 12.0% Required return on the market

8 16.2% =A3+(A4-A3)*A2 (entered into Cell A8)

Constant Growth Stock Expected Rate of Return

A B C D

2 $ 33.33 Stock price

3 $ 2.00 Next expected dividend

4 10.0% E(g) Expected growth rate

8 16.0% =A3/A2+A4 (entered into Cell A8)

Nonconstant Growth Stock Valuation

A B C D

2 30.0% Nonconstant growth rate

3 10.0% Constant growth rate

4 16.0%

5 $ 1.82 Last dividend payment

7 $ 2.366 =A5*(1+A2) (entered into Cell A7)

8 $ 3.076 =A7*(1+A2) (entered into Cell A8)

9 $ 3.999 =A8*(1+A2) (entered into Cell A9)

10 $ 4.398 =A9*(1+A3) (entered into Cell A10)

11 $ 73.307 =A10/(A4-A3) (entered into Cell A10)

13 $ 53.85 =NPV(A4,A7:A9)+PV(A4,3,,-A11) (entered into Cell A13)

CHAPTER 9

Semiannual Compounding YTM

A B C D

2 50 Nper Number of payments

3 $ (1,114.69) Pv Present value (bond price)

4 $ 35.00 Pmt Payment (coupon amount)

5 $ 1,000.00 Fv Future value (principal)

8 3.05% =RATE(A2,A4,A3,A5) (entered into Cell A8)

CHAPTER 11

A B C D

2 10.0% Project cost of capital

3 $ (240) Cash flow 0 (000s)

4 100 Cash flow 1 (000s)

5 105 Cash flow 2 (000s)

6 110 Cash flow 3 (000s)

7 141 Cash flow 4 (000s)

10 $ 117 =NPV(A2,A4:A7)+A3 (entered into Cell A10)

Proj IRR

A B C D

2 10.0% Project cost of capital

3 $ (240) Cash flow 0 (000s)

4 100 Cash flow 1 (000s)

5 105 Cash flow 2 (000s)

6 110 Cash flow 3 (000s)

7 141 Cash flow 4 (000s)

10 29.7% =IRR(A2,A3:A7) (entered into Cell A10)

Proj MIRR

A B C D

2 10.0% Project cost of capital

3 $ (240) Cash flow 0 (000s)

4 100 Cash flow 1 (000s)

5 105 Cash flow 2 (000s)

6 110 Cash flow 3 (000s)

7 141 Cash flow 4 (000s)

10 21.4% =MIRR(A3:A7,A2,A2) (entered into Cell A10)

9 – *

Interpretation of the IRR
If a project’s IRR is greater than its cost of capital, then there is an “excess” return that contributes to the equity value of the business.
In our example, IRR = 29.7% and the project cost of capital is 10%, so the project is expected to enhance Midtown Clinic’s financial condition.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Discussion Items
What is the meaning of an IRR of 0%?
Of an IRR of 10%?
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Some Thoughts on Project Analysis
Although NPV and IRR generally are perfect substitutes, there are yet other ROI measures that can be used; i.e., the Profitability Index.
A thorough analysis will consider all profitability measures, plus examine input variable breakevens.
However, the key to effective project analysis is the ability to forecast the cash flows with some confidence.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Project Scoring
Project measures thus far have focused on financial value.
Other factors can be incorporated into the analysis by using project scoring, which is a matrix that considers factors such as patient, staff, and physician value in addition to financial value.
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

Post Audit
The post audit is a formal process for monitoring a project’s performance over time.
It has several purposes:
Improve forecasts
Develop historical risk data
Improve operations
Reduce losses
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives

9 – *

This concludes our discussion of Chapter 9 (Capital Investment Decision Basics).
Although not all concepts were discussed in class, you are responsible for all of the material in the text.
Do you have any questions?
Conclusion
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
Copyright © 2013 by the Foundation of the American College of Healthcare Executives
A
B
C
D
1
2
3
Nper
Number of periods
3
100.00
$

Pv
Present value
4
10.0%
Rate
Interest rate
5
6
133.10
$

=100*(1.10)^3 (entered into Cell A6)
7
8
133.10
$

=A3*(1+A4)^A2 (entered into Cell A8)
9
10
133.10
$

=FV(A4,A2,,-A3) (entered into Cell A10)
A
B
C
D
1
2
3
Nper
Number of periods
3
100.00
$

Fv
Future value
4
10.0%
Rate
Interest rate
5
6
75.13
$

=A3/(1+A4)^A2 (entered into Cell A6)
7
8
75.13
$

=PV(A4,A2,,-A3) (entered into Cell A8)
9
10
ABCD
1
210.0%Project cost of capital
3(240)$ Cash flow 0 (000s)
4100 Cash flow 1 (000s)
5105 Cash flow 2 (000s)
6110 Cash flow 3 (000s)
7141 Cash flow 4 (000s)
8
9
10117$ =NPV(A2,A4:A7)+A3 (entered into Cell A10)
ABCD
1
210.0%Project cost of capital
3(240)$ Cash flow 0 (000s)
4100 Cash flow 1 (000s)
5105 Cash flow 2 (000s)
6110 Cash flow 3 (000s)
7141 Cash flow 4 (000s)
8
9
1029.7%=IRR(A2,A3:A7) (entered into Cell A10)

Turn in your highest-quality paper
Get a qualified writer to help you with

“ FInance Questions ”

Get high-quality paper

Guarantee! All work is written by expert writers!

Still stressed from student homework?

Get quality assistance from academic writers!

Order now