A Lotus 1-2-3 Template

The question of whether to purchase, finance, or lease an automobile continues to confront and confound individuals and business people whenever they consider the acquisition or replacement of an automobile. Cash flow considerations, tax write-offs, and potential hidden profits in the financing or lease payments are every purchasers' concerns.

To assess the alternatives, a model was developed to discount all cash flows to the date of acquisition. A template of this model was then prepared for use with Lotus 1-2-3.

The model has six variations:

Personal Use

1. Cash Purchase;

2. Finance;

3. Lease;

Business Use

1. Cash Purchase;

2. Finance;

3. Lease.

The variations are necessary to accommodate the dissimilar tax treatment afforded personal and business uses of an automobile coupled with the purchase, finance, or lease options facing the customer.

Applications

To demonstrate realistic applications, a survey of automobile prices, financing rates and lease terms was taken in june 1989. The DATA INPUTS, shown in Exhibit 1 omitted, are representative of the survey's findings and in conformity with the IRS regulations in effect on June 1, 1989.

The DATA INPUTS anticipate the acquisition of an automobile on January 1, 1990, with a market value of $15,239 and the intention to use it for five years. At the end of that period (December 31, 1994), it is anticipated that the automobile will have a resale value of $5,100.

The automobile can be purchased for cash or financed at an interest rate of 11.25%, compounded monthly. The finance terms would require a down payment of $1,524 and 60 monthly payments of $299.91.

The same automobile may be leased with terms stipulating a refundable security deposit of $310, followed by 60 monthly payments of $310.

The cash flows are discounted at 9.5%, which is the rate of return that could be earned on an assumed lump sum annuity investment that is made to provide for either the finance or lease payments.

The Model

To compare the alternatives, the model used in the example discounts all cash outflows and inflows to their present value on January 1, 1990. The model has three major sections: DATA INPUTS, where the relevant variables are entered; PRESENT VALUE CALCULATIONS, not shown because of space limitations, where cash flows are reduced to their present values; and the SUMMARY, shown in Exhibit 2 omitted, where the present values of all the cash flows are brought together and the Discounted Net Cost is derived.

Maintenance, repairs, gasoline, motor vehicle fees and insurance are excluded in all instances, because they would not vary. The relevant tax treatment is explained in the notes.

The model will be more easily understood, if the cash flows that are included and discounted in the SUMMARY (Exhibit 2) are reviewed first.

The Discounted Cash Flows

The Personal Use, Cash Purchase is the least complex arrangement of the model. There is one cash outflow, the Cash Purchase; and one inflow, the Disposition

Proceeds.

The Personal Use, Finance arrangement accounts for a one-time cash outflow, the Down Payment; and a series of cash outflows, the Monthly Finance payments. It also considers two one-time cash inflows, the Tax Benefit of the interest expense incurred in 1990 and the Disposition Proceeds.

In the Personal Use, Lease model the one-time Security Deposit, and the series of Monthly Lease Payments are the cash outflows. The one inflow is the Refund of the Security Deposit.

The Business Use models provide for, as cash inflows, the yearly Tax Benefit of deductible interest, depreciation and lease expenses. An outflow is the yearly Tax Liability incurred when the value of the leased auto exceeds $12,800. Should there be a capital gain or loss on disposition, the Tax Consequence of a capital gain would be an outflow, that of a loss would be an inflow.

Getting Started

The full template on a 5 1/4 inch floppy disk with a hard copy of the cell formulas is available from the author. (See the Note at the end of this article for details.)

Step 1: Complete Exhibit 1, DATA INPUTS, by entering values into the specified cell addresses. The Depreciation and Lease Income Inclusion schedules, Cells E17..I18 need only be completed if business use is contemplated. The inputs are arranged in alphabetical order by topic, rather than logical order, for ease of reference.

The tax rate will vary depending on the taxpayer's income level and status, individual versus corporate. The tax rate, depreciation and lease income inclusion amounts, of course are subject to change.

The interest expense and assumed interest income are transferred from amortization tables which are not part of the template.

Step 2: The transfer of data from the DATA INPUTS section of the template to the PRESENT VALUE CALCULATIONS section, not shown, where the Discounted Cash Flows are computed.

Step 3: The transfer of the Discounted Cash Flows from the PRESENT VALUE CALCULATIONS section to the SUMMARY, and the compilation of the Discounted Net Cost.

The formulas to execute these tasks reside in rows 53 through 185. They are not shown. However, they can be viewed from the template by executing /RFT.

Step 4 : Use the template. Once the DATA INPUTS are completed, the Discounted Net Cost will be derived when the "Enter" key is depressed.

By changing the values of the DATA INPUTS, the user will be able to readily compare the almost infinite number of cash purchase, finance, and lease offers that are available.

Conclusion

Any number of alternate proposals are easily analyzed. Only changes in the Data Inputs need be made for the new Discounted Net Cost to be calculated.

To accommodate incremental periods, the template can be expanded by adding additional columns and adjusting the formulas to account for the greater number of inputs.

Acquisition decisions for assets other than automobiles can also be easily analyzed. The DATA INPUTS are essentially the same with the exception of the lease income inclusion.

NOTE : For a copy of the disk and a hard copy of the cell formulas write to: Vincent S. Scerbinski, CPA, 31 Richard Road, Edison, N.J. 08820 Please include $5.00 for Disk, Postage and Handling.