Title: HCM 570 Financial Management in Healthcare August 14-December 18, 2004
1HCM 570 Financial Managementin
HealthcareAugust 14-December 18, 2004
- Joseph Callaghan, Ph.D.
- Oakland University
- Accounting and Finance
2Mathematics and Tools of Finance
- Basics
- Intermediate
- Case 11
3Time Value Analysis
- Future and present values
- Lump sums
- Annuities
- Uneven cash flow streams
- Solving for i and n
- Investment returns
- Amortization
4Time 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. - Time value analysis constitutes the techniques
that are used to account for the time value of
money.
51
2
0
3
i
CF0
CF1
CF3
CF2
Tick marks designate ends of periods. Time 0 is
the starting point (the beginning of Period 1)
Time 1 is the end of Period 1 (the beginning of
Period 2) and so on.
6Time Line Illustration 1
1
0
2
5
5
500
- What does this time line show?
7Time Line Illustration 2
1
0
2
10
10
?
-100
- What does this time line show?
8What is the FV after 3 years ofa 100 lump sum
invested at 10?
0
1
2
3
10
-100
FV ?
- Finding future values (moving to the right along
the time line) is called compounding. - For now, assume interest is paid annually.
9After 1 year
FV1 PV INT1 PV (PV x i) PV x (1
i) 100 x 1.10 110.00.
After 2 years
FV2 FV1 INT2 FV1 (FV1 x i) FV1
x (1 i) PV x (1 i) x (1
i) PV x (1 i)2 100 x (1.10)2 121.00.
10After 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 .
11Three 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, Lotus 1-2-3, or Quattro Pro.
12Non-Financial Calculator Solution
0
1
2
3
10
-100
133.10
110.00
121.00
100 x 1.10 x 1.10 x 1.10 133.10.
13Financial Calculator Solution
- Financial calculators are pre-programmed to solve
the FV equation - FVN PV x (1 i)n.
- There are four variables in the equation FV, PV,
i and n. If any three are known, the calculator
can solve for the fourth (unknown).
14Using a calculator to find FV (lump sum)
INPUTS
3 10 -100 0 N I/YR PV PMT
FV 133.10
OUTPUT
(1) For lump sums, the PMT key is not used.
Either clear before the calculation or enter PMT
0.
Notes
(2) Set your calculator on P/YR 1, END.
15What is the PV of 100 duein 3 years if i 10?
0
1
2
3
10
100
PV ?
Finding present values (moving to the left along
the time line) is called discounting.
16 Solve FVn PV x (1 i)n for PV
PV FVN / (1 i)n.
PV 100 / (1.10)3 100(0.7513) 75.13.
17Financial Calculator Solution
INPUTS
3 10 0 100
-75.13
N
I/YR
PV
PMT
FV
OUTPUT
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.
18Opportunity Cost Rate
- On the last illustration we needed to apply a
discount rate. Where did it come from? - The discount rate is the opportunity cost rate.
- It is the rate that could be earned on
alternative investments of similar risk. - It does not depend on the source of the
investment funds. - We will apply this concept over and over in this
course.
19Opportunity Cost Rate (Cont.)
- The opportunity cost rate is found (at least in
theory) as follows. - Assess the riskiness of the cash flow(s) to be
discounted. - Identify security investments that have the same
risk. - Estimate the expected return that could be earned
on the security investment. - When applied, the resulting PV provides a return
equal to the opportunity cost rate. - In most capital finance situations, bench-mark
opportunity cost rates are known.
20Solving for i
Assume that a bank offers an account that will
pay 200 after 5 years on each 75 invested.
What is the implied interest rate?
INPUTS
5 -75 0 200 N I/YR PV
PMT FV 21.7
OUTPUT
21Solving for n
Assume an investment earns 20 percent per
year. How long will it take for the investment
to double?
INPUTS
20 -1 0 2
3.8
N
I/YR
PV
PMT
FV
OUTPUT
22Types of Annuities
Three Year Ordinary Annuity
0
1
2
3
i
PMT
PMT
PMT
Three Year Annuity Due
0
1
2
3
i
PMT
PMT
PMT
23What is the FV of a 3-year ordinary annuity of
100 invested at 10?
0
1
2
3
10
100
100
100
110 121 FV 331
24Financial Calculator Solution
INPUTS
3 10 0 -100 331.00
I/YR
N
PMT
FV
PV
OUTPUT
Here there are payments rather than a lump sum
present value, so enter 0 for PV.
25What is the PV of the annuity?
0
1
2
3
10
100
100
100
90.91
82.64
75.13
248.68 PV
26Financial Calculator Solution
INPUTS
3 10 100 0
N
I/YR
PV
PMT
FV
OUTPUT
-248.69
27What is the FV and PV if theannuity were an
annuity due?
0
1
2
3
10
100
100
100
?
?
28Switch from End to Begin mode on a financial
calculator. Repeat the annuity calculations.
First find PVA3 273.55.
INPUTS
3 10 100 0 -273.55
N
I/YR
PV
PMT
FV
OUTPUT
Then enter PV 0 and press FV to find FV
364.10.
29Perpetuities
- A perpetuity is an annuity that lasts forever.
- What is the present value of a perpetuity?
- PV (Perpetuity) .
- What is the future value of a perpetuity?
PMT
Perp fv
i
30Uneven Cash Flow Streams Setup
4
0
1
2
3
10
100
300
300
-50
90.91
247.93
225.39
-34.15
530.08 PV
31Uneven Cash Flow StreamsFinancial Calculator
Solution
- Input into the cash flow register
- CF0 0
- CF1 100
- CF2 300
- CF3 300
- CF4 -50
- Enter i 10, then press NPV button to get
530.09. - NPV means net present value.
32Investment Returns
- The financial performance of an investment is
measured by its return. - Time value analysis is used to calculate
investment returns. - Returns can be measured either in dollar terms or
in rate of return terms. - Assume that a hospital is evaluating a new MRI.
The projects expected cash flows are given on
the next slide.
33MRI Investment Expected Cash Flows(in thousands
of dollars)
4
0
1
2
3
310
400
500
750
-1,500
- Where do these numbers come from?
34Simple Dollar Return
0
1
4
2
3
-1,500
310
400
500
750
310
400
500
750
460 Simple dollar return
35Discounted Cash Flow (DCF) Dollar Return
0
1
4
2
3
8
-1,500
310
400
500
750
287
343
397
551
78 net present value (NPV)
Where did the 8 come from?
36DCF Dollar Return (Cont.)
- The key to the effectiveness of this measure is
that the discounting process automatically
recognizes the opportunity cost of capital. - An NPV of zero means the project just earns its
opportunity cost rate. - A positive NPV indicates that the project has
positive financial value after opportunity costs
are considered.
37Rate of (Percentage) Return
0
1
2
3
4
10
-1,500
310
400
500
750
282
331
376
511
0.00 NPV, so rate of return 10.0.
38Rate of Return (Cont.)
- In capital investment analyses, the rate of
return often is called internal rate of return
(IRR). - In essence, it is the percentage return expected
on the investment. - To interpret the rate of return, it must be
compared to the opportunity cost of capital. In
this case 10 versus 8.
39Intra-Year Compounding
- Thus far, all examples have assumed annual
compounding. - When compounding occurs intra-year, the following
occurs. - Interest is earned on interest more frequently.
- The future value of an investment is larger than
under annual compounding. - The present value of an investment is smaller
than under annual compounding.
400
1
2
3
10
-100
133.10
Annual FV3 100 x (1.10)3 133.10.
0
1
2
3
0
1
2
3
4
5
6
5
-100
134.01
Semiannual FV6 100 x (1.05)6 134.01.
41Effective Annual Rate (EAR)
- EAR is the annual rate which causes the PV to
grow to the same FV as under intra-year
compounding. - What is the EAR for 10, semiannual compounding?
- Consider the FV of 1 invested for one
- year. FV 1 x (1.05)2 1.1025.
- EAR 10.25, because this rate would
- produce the same ending amount
- (1.1025) under annual compounding.
42The EAR Formula
q
iStated
EAR 1 - 1.0
q
2
0.10
1 - 1.0
2
(1.05)2 - 1.0 0.1025 10.25.
Or, use the EFF key on a financial calculator.
43EAR of 10 at Various Compounding
EARAnnual 10. EARQ (1 0.10/4)4 -
1.0 10.38. EARM (1 0.10/12)12 - 1.0
10.47. EARD(360) (1 0.10/360)360 - 1.0
10.52.
44Using the EAR
4
0
1
2
3
5
6 6-month periods
5
100
100
100
Here, payments occur annually, but compounding
occurs semiannually, so we cannot use normal
annuity valuation techniques.
45First Method Compound Each CF
5
6
4
2
0
1
3
5
100
100.00
100
110.25
121.55
331.80
46Second Method Treat as an Annuity
- Find the EAR for the stated rate
EAR (1 ) - 1 10.25.
2
0.10 2
- Then use standard annuity techniques
3 10.25 0 -100
INPUTS
N
I/YR
PV
FV
PMT
331.80
OUTPUT
47Amortization
Construct an amortization schedule for a 1,000,
10 annual rate loan with 3 equal payments.
48Step 1 Find the required payments.
0
1
2
3
10
PMT
PMT
PMT
-1,000
3 10 -1000 0
402.11
INPUTS
N
I/YR
PV
FV
PMT
OUTPUT
49Step 2 Find interest charge for Year 1.
INTt Beginning balance x i. INT1 1,000 x
0.10 100.
Step 3 Find repayment of principal in Year 1.
Repmt PMT - INT 402.11 - 100
302.11.
50Step 4 Find ending balance at end of
Year 1.
End bal Beg balance - Repayment 1,000 -
302.11 697.89.
Repeat these steps for Years 2 and 3 to complete
the amortization table.
51 BEG PRIN END YR BAL PMT INT PMT BAL
1 1,000 402 100 302 698 2 698 402 70 332 36
6 3 366 402 37 366 0 TOTAL 1,206.34 206.34 1,
000
Note that annual interest declines over time
while the principal payment increases.
52402.11
Interest
302.11
Principal Payments
0
1
2
3
Level payments. Interest declines because
outstanding balance declines. Lender earns 10
on loan outstanding, which is falling.
53Context in MS Office
- Word processing (text-oriented)
- MS Word
- Database (data storage-oriented)
- MS Access
- Presentation (slide-oriented)
- MS PowerPoint
- Web Development (web-page-oriented)
- MS FrontPage
- Numerical Analysis (processing-oriented)
- MS Excel
54Context in MS Office (cont.)
- Oriented is key since all perform similar tasks
but to different degrees - Integration across applications important
- Need should drive choice among apps
- In business setting, planning and setup are
important - Training is costly, so cost/benefit prevails
55Purposes of Excel
- Data entry/storage
- Access to data by
- File Import
- Database Query
- Data entry
- Data analysis
- What if analysis
- Functions/Wizards/Macros (Programming)
- Presentation/Reports/Graphics
56Grid/Cells
- Referencing the grid columns/row intersect
- Fundamental unit is the cell
- A1, B3, etc. relative referencing is default
- in front of either is absolute reference
- Affects what happens when you copy formulas
- Formulas
- A1B1
- , /, , -,
- Typical precedence
- Functions
- E.g. Sum(cell reference to range affected)
57Data Entry
- Cells contain
- Text
- Numbers
- Formulas, referencing other cells
- Functions, referencing other cells
- Divide work (and spreadsheet)
- Data
- Analysis
- Presentation
58Objects
- Workbook (corresponds to .xls file)
- Worksheet (many per Workbook)
- Special ones for Graphical and statistical output
- Cells (many per Worksheet)
59Practice (d-click table)
60Microsoft Office Excel 2003
- Tutorial 2 Working With Formulas and Functions
61Use Excels functions
- You can easily calculate the sum of a large
number of cells by using a function. - A function is a predefined, or built-in, formula
for a commonly used calculation. - Each Excel function has a name and syntax.
- The syntax specifies the order in which you must
enter the different parts of the function and the
location in which you must insert commas,
parentheses, and other punctuation - Arguments are numbers, text, or cell references
used by the function to calculate a value - Some arguments are optional
62Work with the Insert Function button
- Excel supplies more than 350 functions organized
into 10 categories - Database, Date and Time, Engineering, Financial,
Information, Logical, Lookup, Math, Text and
Data, and Statistical functions - You can use the Insert Function button on the
Formula bar to select from a list of functions. - A series of dialog boxes will assist you in
filling in the arguments of the function and this
process also enforces the use of proper syntax.
63Math and Statistical functions
64Define functions, and functions within functions
- The SUM function is a very commonly used math
function in Excel. - A basic formula example to add up a small number
of cells is A1A2A3A4, but that method would
be cumbersome if there were 100 cells to add up. - Use Excel's SUM function to total the values in a
range of cells like this SUM(A1A100). - You can also use functions within functions.
Consider the expression ROUND(AVERAGE(A1A100),1)
. - This expression would first compute the average
of all the values from cell A1 through A100 and
then round that result to 1 digit to the right of
the decimal point
65Copy and paste formulas and functions
- Copying and pasting a cell or range of cells is a
simple, but highly effective means for quickly
filling out a large worksheet. - To copy and paste a cell or range
- Select the cell or range to be copied and then
click the Copy button on the standard toolbar - Select the cell or range into which you want to
copy the selection and then click the Paste
button on the standard toolbar - Once you are finished pasting, press the Esc key
to deselect the selection
66Copy and paste effects on cell references
- Copied formulas or functions that have cell
references are adjusted for the target cell or
range of cells. - For example, if cell G5 contains the formula
F5B5/B7, and you copy and paste this formula to
cell G6, the formula in cell G6 will be
F6B6/B8. - This may or may not be correct for your
worksheet, depending upon what you are trying to
do. - You can control this automatic adjusting of cell
references through the use of relative and
absolute references.
67Problems using copy and paste with formulas
- When Excel does not have enough room to display
an entire value in a cell, it uses a string of
these symbols to represent that value. - For example, the formula in cell J5 is
F5-(H5I5) and this was pasted into cell J6 by
updating the cell references there to
F6-(H6I6). - Cell G5 has the formula F5B5/B7 and cell G6
contains F6B6/B8. This is where things went
wrong. Sometimes this automatic update is very
useful and other times it does not give you the
desired result for your worksheet. - In this case, cells B5 and B7 should be
referenced in the formula in column G in all 240
payment period rows, but in column J, you want
the cell references to be automatically updated.
You can control this result using relative and
absolute references.
68Use relative references
- A relative reference is a cell reference that
shifts when you copy it to a new location on a
worksheet. - A relative reference changes in relation to the
change of location. - If you copy a formula to a cell three rows down
and five columns to the right, a relative
reference to cell B5 in the source cell would
become G8 in the destination cell.
69Use absolute references
- An absolute reference is a cell reference that
does not change when you copy the formula to a
new location. - To create an absolute reference, you preface the
column and row designations with a dollar sign
(). - For example, the absolute reference for B5 would
be B5. - This cell reference would stay the same no matter
where you copied the formula.
70Use mixed references
- A mixed reference combines both relative and
absolute cell references. - You can effectively lock either the row or the
column in a mixed reference. - For example, in the case of B5, the row
reference would shift, but the column reference
would not - In the case of B5, the column reference would
shift, but the row reference would not - You can switch between absolute, relative and
mixed references in the formula easily in the
edit mode or on the formula bar by selecting the
cell reference in your formula and then pressing
the F4 key repeatedly to toggle through the
reference options.
71Open the Insert Function dialog box
- To get help from Excel to insert a function,
first click the cell in which you wish to insert
the function. - Click the Insert Function button. This action
will open the Insert Function dialog box. - If you do not see the Insert Function button, you
may need to select the appropriate toolbar or add
the button to an existing toolbar.
72The Average Function
- The average function is necessary to calculate
the average of a range of cells. - Like any other formula, the average function may
be copied across cells.
73Date Functions
74Excel's date functions
- Excel stores dates as integers, where the integer
value represents the number of days since January
1, 1900. - For example, the integer value for the date
January 1, 2008 is 39448 because that date is
39,448 days after January 1, 1900 - You typically do not see these numbers, because
Excel automatically formats them to appear in a
date format. - This method of storing dates allows you to work
with dates the same way you work with numbers. - Excel's commonly used date functions are DATE,
DAY, MONTH, NOW, TODAY, WEEKDAY and YEAR.
75The TODAY and Now functions
- The TODAY and NOW functions always display the
current date and time. - You will not normally see the time portion unless
you have formatted the cell to display it. - If you use the TODAY or NOW function in a cell,
the date in the cell is updated to reflect the
current date and time of your computer each time
you open the workbook.
76Use a formula to enter the date
77Excel's financial functions
- Financial functions are very useful to calculate
information about loans. - Common functions are FV, IPMT, PMT, PPMT and PV.
- All these financial functions will use similar
arguments that differ based upon which function
you are using. - Think of the arguments as members of an equation
- The arguments represent the values of the
equation that are known and the function provides
the solution for a single variable, or unknown,
value
78Use the financial functions
- The FV function calculates the future value of an
investment based on periodic, constant payments
and a constant interest rate per period. - The IPMT function provides the interest payment
portion of the overall periodic loan payment. - The PMT function calculates the entire periodic
payment of the loan. - The PPMT function calculates just the principal
payment portion of the overall periodic payment. - The PV function calculates the present value of
an investment.
79Financial Function descriptions
80Recognize optional arguments
- In the preceding figure, note how rate and nper
are arguments for each function. - For some of the functions, the final two
arguments of each function are in brackets. These
represent optional arguments, meaning if you do
not enter anything, the default values for these
arguments will be used. - For example, note the PMT function has fv and
type as its final two arguments, which are
optional. The assumed values, if no others are
supplied, are 0 for both - Arguments without brackets do not have default
values, so you must supply values or cell
references in order for the function to be able
to return a value.
81Use the Insert Function dialog box to enter
function arguments
82Create logical functions
- A function that determines whether a condition is
true or false is called a logical function. - Excel supports several logical functions such as
AND, FALSE, IF, NOT, OR and TRUE. - A very common function is the IF function, which
uses a logical test to determine whether an
expression is true or false, and then returns one
value if true or another value if false. - The logical test is constructed using a
comparison operator that compares two expressions
to determine if they are equal, not equal, if one
is greater than the other, and so forth. - The comparison operators are , gt, gt, lt, lt, and
ltgt - You can also make comparisons with text strings.
You must enclose text strings within quotation
marks.
83Using the If function
- The arguments for the IF function are
- IF(logical_test,value_if_true,value_if_false)
- For example, the function IF(A110,20,30) tests
whether the value in cell A1 is equal to 10 - If it is, the function returns the value 20,
otherwise the function returns the value 30 - Cell A1 could be empty or contain anything else
besides the value 10 and the logical test would
be false therefore, the function returns the
value 30 - To insert an IF function, click the Insert
Function button and search for the IF function,
then click OK. - When the Function Arguments dialog box appears,
simply fill in the arguments.
84Case 11