Spreadsheet Models for Managers
Demonstrations for Session 10
Capital Leases II
We have two demonstrations (
2000-4 or
2007+)
for this session:
Modeling a Capital Lease (2000-4) or (2007+)
[Sheet: Winder]
This problem is a warm-up for problems involving the Lease
Characteristic Array. In this problem we compute the effects on the three financial statements of a single lease event.
In effect, we're computing the rows of the Lease Characteristic Array.
General Kinematics purchases a coil winder under a capital lease contract. The winder is worth $100,000. The term of the
lease is six years, and the interest rate is 9% per year. The useful life of the winder is also six years, at which time GK
is obliged to buy it for $1,500. GK believes that this will be the scrap value of the winder at that time. Lease payments
are quarterly.
(a)
Find the lease payment.
We first create cells to hold the name parameters Rate, Periods, InitialValue and
ScrapValue. The formula for payment size, expressed in terms of these parameters, is inserted into a named cell
PaymentSize:
=PMT(Rate/4,Periods,-InitialValue)
(b)
Find the quarterly effect on the Income Statement
This is determined by two quantities: the depreciation, and the interest expense. We can compute interest expense using
the ipmt worksheet function:
=IPMT(Rate/4,PeriodNumber,Periods,-InitialValue)
Here we have used the name PeriodNumber to refer to the row of integers that indicate the index of that
column's period.
To find the depreciation, we assume straight-line (constant rate) depreciation of the initial value less the scrap value
of the winder. So we can create a cell called Depreciation, and insert in it the formula
=(InitialValue-ScrapValue)/Periods
To complete the calculation of the effect of depreciation expense on the Income Statement, we have to create a row of
cells, each one of which contains the formula
=Depreciation
(c)
The quarterly effect on cash flow is just a row of cells each one of which contains the formula
=-PaymentSize
(d)
The quarterly effect on the balance sheet is a net asset value equal to the initial value of the winder less cumulative
depreciation, and a liability equal to the remaining principal to be paid on the winder.
To compute cumulative depreciation, make a running sum of the depreciation stream. Thus, the assets line of the effect on
the balance sheet is just the InitialValue minus this cumulative depreciation, which appears in the row
captioned "Assets: machinery."
To compute the liabilities, we have to compute the principal remaining to be paid. That is just the InitialValue of the
winder minus the cumulative principal paid.
The principal part of the lease payment is just
=PPMT(Rate/4,PeriodNumber,Periods,-InitialValue)
So in the line "CumPrincipal", we compute a running sum of the principal payments. We then use that in the line
"Liabilities: Cap Lease."
Modeling a Sequence of Capital Lease Events (2000-4) or (2007+)
[Sheet: PC]
General Kinematics is expanding. You're leasing personal computers for new hires as the company expands. Each PC costs
$1200. You're given the hiring stream, as shown in the row "HireStream". Find the effect of these leases on
cash flow, capital equipment assets, and depreciation expense. This problem is different from previous examples, because
this time, we lease the equipment instead of buying it. The lease terms are:
- 5 year lease term
- Depreciation 5 years, straight line
- Interest rate 9% per year
We'll take the approach of using the Lease Characteristic Array. Once we have found the LCA, we'll just convolve it with
the HireStream.
The rows of the LCA are in no special order, but let's compute them in the order shown on the worksheet. We know that
we'll have some named parameters: Rate, PCCost, DepreciationTerm,
Periods.
The row DepreciationPQ uses our familiar approach that
exploits the Ripple Principle for depreciation computations.
Let's compute the effects of a $1000 purchase. We'll then rescale by the amounts of the actual purchase when we convolve.
So the block labeled "For a $1000 PC" computes these intermediate results.
There are several good reasons for taking this approach. First, using a round number like $1000 (or in other situations,
$1,000,000 or $1,000,000,000, etc.) enables us to examine the result and easily detect errors. If we were to use
the exact price of the item in question, then detecting an error in, say, the interest row would be much more difficult.
Second, using a round number makes the LCA easily re-scalable. That is, we can use it for any lease item whatsoever, simply
by applying a rescaling factor of <price-of-item>/1000. If we compute the LCA for some other difficult-to-remember
amount, we might make a mistake. If we compute the LCA for an amount that might change when we later update some prices,
then we would have to change the rescaling factor, too, or build it into every usage of the LCA. Using a constant 1000
is easy and safe, once you understand what it's doing.
The row PrincipalPayment is just the principal part of the lease payments, as in the Winder example. The row
CumPrincipal is a running sum of PrincipalPayment. The row CumDepreciation is a
running sum of DepreciationPQ. We need all these quantities by analogy with the Winder example. Now we're ready
to compute the LCA.
The first row, PCPayment, is just the lease payment itself, computed with PMT. This line goes directly
to the Cash Flow statement. The second row, Assets, is the initial value of the asset (1000) less the cumulative
depreciation. The third row, Liabilities, is the initial value of the asset (1000) less the principal that's already paid
off, CumPrincipal. The fourth row, PCDepreciation, is the depreciation in the current quarter. And
the fifth row, InterestPayment, is the interest portion of the lease payment, which we compute with IPMT.
Finally, we convolve with the hire stream, and multiply by PCCost/1000 to scale the result properly.
NewEquipmentPQ is actually PCCost times the HireStream, so the result is the same as
the formula we actually used:
{=Convolve(LeaseCharacteristic,NewEquipmentPQ)/1000}
For more about my spreadsheet consulting
and training services, visit SpreadsheetAce.com.
f you use Excel to model businesses, business processes, or
business transactions, this course will change your life. You'll learn how to create tools for yourself that will amaze
even you. Unrestricted use of this material is available in two ways.
