# Chapter 20 Lease Financing Spreadsheet Problem - Excel

Document Sample

```					            A              B             C              D            E              F            G            H             I           J
1   Chapter 8                     UNDERSTANDING HEALTHCARE FINANCIAL MANAGEMENT, 5ed                                                   8/7/06
2
3                                                          Lease Financing
4
5   This spreadsheet model performs some of the calculations contained in Chapter 8. We recommend that you use the model in the
6   following manner:
7
8   1. First, recognize that you do not have to use this model at all to understand lease financing. However, if you do use
9   the model and experiment with it, this will increase your understanding of the concepts, and it will surely help when you
10   use spreadsheet models for other purposes, especially any problem sets or cases assigned for this course.
11
12   2. Start by reading the chapter in its entirety.
13
14   3. Now place the text alongside your computer with this model on the screen. When you come to an explanation of a calculation
15   in the text, see if the model has a matching calculation.
16
17   4. We assume that you know the basics of Excel, but you have not encountered some of its features or you may need a
18   refresher or two. So, we have built in explanations of how to do some of the functions in the model. As a result, you will learn
19   more about Excel at the same time you learn about lease financing.
20
21   5. Throughout this model, page numbers of the matching text calculations are provided in pink. Input data are in red on a
22   yellow background, and output data are in green on a beige background. You are encouraged to change the input data to learn
23   more about the calculations in the model.
24
25
26
27
28   FINANCIAL STATEMENT EFFECTS (PAGE 266)
29
30   Lease payments are shown as operating expenses on a firm's income statement. Under certain conditions, neither the
31   leased assets nor the liabilities under the lease contract appear on the firm's balance sheet. For these reasons, leasing
32   is often called "off balance sheet financing." This can be illustrated by looking at the hypothetical firms, B and L. These
33   two firms are identical in every way, except Firm B has decided to buy its new assets, while Firm L has chosen to lease.
34
35                        Before the Increase in Assets
36                               Firms B and L
37   Cur. assets            \$50                  Debt                     \$50
38   Fixed assets            50                  Equity                    50
39   Total assets          \$100                  Total D&E               \$100                           Debt ratio =      50%
40
41
42   Amount of asset increase                 \$50
43
A               B             C             D            E              F            G            H              I            J
44                          After the Increase in Assets
45                                     Firm B
46    Cur. assets             \$50                 Debt                   \$100
47    Fixed assets            100                 Equity                   50
48    Total assets           \$150                 Total D&E              \$150                            Debt ratio =      67%
49
50                                   Firm L
51    Cur. assets             \$50                  Debt                   \$50
52    Fixed assets             50                  Equity                  50
53    Total assets           \$100                  Total D&E             \$100                            Debt ratio =      50%
54
55
56
57    EVALUATION BY THE LESSEE
58
59    Any prospective lease must be evaluated by both the lessee and the lessor. The lessee must determine whether leasing the
60    asset will be less costly than buying the asset, and the lessor must determine if the lease provides an adequate return.
61
62    The Group plans to acquire equipment that costs \$200,000. It falls into the MACRS five-year class.
63    Although the equipment has an eight-year useful life, it would be sold after four years for \$20,000.
64    The Group can lease the equipment for four years at an annual rental rate of \$57,000, payable at the
65    beginning of each year. The lease contract includes maintenance, which would cost the Group \$2,500
66    at the beginning of each year if the equipment is purchased. A loan to purchase the equipment would
67    have an interest rate of 10%. The Group is in the 40% tax bracket.
68
69    Net purchase price               \$200,000
70    Residual value                    \$20,000
71    Annual maintenance exp.            \$2,500
72    Tax rate                             40%
73    Loan interest rate                   10%
74    Annual rental charge              \$57,000
75
77    input data to calculate after-tax cost of debt and the depreciation schedule.
78
79        After-tax cost of debt =           6%
80
81        Year               1           2              3            4              5            6
82      Dep. Rate          20%         32%            19%          12%            11%           6%
83      Dep. Exp.        \$40,000     \$64,000        \$38,000      \$24,000        \$22,000       \$12,000
84
85    DOLLAR COST ANALYSIS (PAGE 272)
86
87
88     Cost of Owning                    0             1             2              3            4
89    Net purchase price              (\$200,000)
90    Maintenance cost                  (\$2,500)      (\$2,500)      (\$2,500)       (\$2,500)
91    Maintenance tax savings              1,000        1,000         1,000          1,000
92    Depreciation tax savings                         16,000        25,600         15,200       9,600
93    Residual value                                                                            20,000
94    Tax on residual value                                                                      5,600                  Note that the NPV function was used
95    Net cash flow                   (\$201,500)      \$14,500       \$24,100        \$13,700     \$35,200                  to calculate the PV of the cash flows.
96                                                                                                                      Because the function assumes that the
97           PV cost of owning =      (\$126,987)                                                                        first cash flow occurs at Year 1, the
98                                                                                                                      range in the function starts with Year 1
99     Cost of Leasing                   0              1            2              3                                   and the Year 0 flow is added separately.
100   Lease payment                    (\$57,000)     (\$57,000)     (\$57,000)      (\$57,000)
101   Tax savings from lease            \$22,800       \$22,800       \$22,800        \$22,800
A              B             C             D               E             F            G            H            I           J
102   Net cash flow                    (\$34,200)     (\$34,200)       (\$34,200)     (\$34,200)
103
104           PV cost of leasing =    (\$125,617)
105
107   PV cost of leasing              (\$125,617)
108   PV cost of owning               (\$126,987)
109
110                        NAL =         \$1,370
111
112   PERCENTAGE COST ANALYSIS (PAGE 275)
113                                                                                                                   We handled the NPV function cash
114                                      0              1              2             3            4                   flow timing problem differently here.
115   Leasing cash flow                (\$34,200)     (\$34,200)       (\$34,200)     (\$34,200)          \$0              We included all the cash flows in the
116   Owning cash flow                 (201,500)       14,500          24,100        13,700       35,200              range and then multiplied the result
117   Incremental cash flow            \$167,300      (\$48,700)       (\$58,300)     (\$47,900)    (\$35,200)             by (1+I) to move the resulting value
118                                                                                                                   to Year 0.
119                        NAL =         \$1,370
120                        IRR =           5.6%                  Here we used the IRR function. It is
121                                                              similar to the NPV function but does
122                                                              not have a timing problem.
123   EVALUATION BY THE LESSOR (PAGE 277)
124
125   Assume that all the data above apply to the lessor except the opportunity cost of capital, which is
126   assumed to be 9% versus the 10% used above in the evaluation for the lessee.
127
128
129   Lessor's opportunity cost            9.0%                              After-tax cost =      5.4%     Change the lessor's opportunity cost
130                                                                                                         of capital to 10%. What is the resultant
131                                      0              1              2             3            4         NPV? Is the lease deal now beneficial
132   Net purchase price              (\$200,000)                                                            to both parties? What does this new
133   Maintenance cost                  (\$2,500)      (\$2,500)        (\$2,500)      (\$2,500)                situation illustrate about leasing?
134   Maintenance tax savings              1,000        1,000           1,000         1,000
135   Depreciation tax savings                         16,000          25,600        15,200        9,600
136   Lease payment                       57,000       57,000          57,000        57,000
137   Tax on lease payment               (22,800)     (22,800)        (22,800)      (22,800)
138   Residual value                                                                             20,000
139   Tax on residual value                                                                       5,600
140   Net cash flow                   (\$167,300)      \$48,700         \$58,300       \$47,900     \$35,200
141
142                        NPV =           \$815
143                        IRR =           5.6%
144
145
146
147   LEVERAGED LEASES (PAGE 280)
148
149   Loan amount                      \$100,000
150   Loan interest rate                   9.0%
151
152                                      0              1              2             3            4
153   Unleveraged cash flow           (\$167,300)      \$48,700         \$58,300       \$47,900     \$35,200
154   Loan amount                      \$100,000
155   Interest                                         (9,000)         (9,000)        (9,000)     (9,000)
156   Interest tax savings                              3,600           3,600          3,600       3,600
157   Principal repayment                                                                       (100,000)
158   Net cash flow                    (\$67,300)      \$43,300         \$52,900       \$42,500     (\$70,200)
159
A   B       C          D   E   F   G   H   I   J
160       NPV =       \$815
161       IRR =       9.1%
162
163
164                                                      End of Model
K   L
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
K          L
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
the NPV function was used
te the PV of 95 cash flows.
the
96
he function assumes that the
flow occurs 97 Year 1, the
at
he function 98
starts with Year 1
ear 0 flow is99
100
101
K         L
102
103
104
105
106
107
108
109
110
111
112
ed the NPV113function cash
ng problem 114
differently here.
115
ded all the cash flows in the
116
d then multiplied the result
117
o move the resulting value
118
119
120
121
122
123
124
125
126
127
128
opportunity129cost
130
What is the resultant
131
eal now beneficial
at does this132
new
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
K       L
160
161
162
163
164
End of Model

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 59 posted: 1/4/2011 language: English pages: 8
Description: Chapter 20 Lease Financing Spreadsheet Problem document sample