Lease Versus Buy Analysis in Excel

Are you trying to decide whether to buy or lease a product?

I’ve been right where you are before as a small business owner. Making the right decision can be difficult, and it’s important to understand all the factors involved. I am here to help make this process easier by walking you through how to perform a lease versus buy analysis in Excel.

With my step-by-step guide, you’ll have all the information needed to make an informed decision that is best for your business. You will gain insight into opportunity cost, inflation, taxes, and more to confidently choose what works best for your bottom line.

Key Takeaways

Lease versus buy analysis is an important decision that many individuals and businesses face when considering acquiring assets. Both options have their advantages and disadvantages, so it is crucial to weigh them carefully before making a decision.

You can easily evaluate the decision using my free Lease Versus Buy Analysis Excel template along with the step-by-step guide found below.

What is Lease Versus Buy Analysis?

When considering whether to lease or purchase an asset (car, computer, building, etc), a Lease versus Buy Analysis is the perfect way to determine which option will deliver maximum value. By comparing the cash flows of both scenarios – leasing and buying – you can make an informed decision that ensures the optimal use of your company’s funds.

Delivering Maximum Value

But how do you deliver maximum value? It comes down to opportunity cost. Purchasing an asset involves upfront investment, potentially offset by a loan. However, the asset is likely to be worth something when you are done using it.

Conversely, leases require a much lower (sometimes nonexistent) upfront payment. Higher effective interest rates and no value when you return the asset can offset this benefit.

So how do you decide which scenario adds the most value? By using a Net Present Value analysis, of course! Convert both scenarios into a set of cash flows and use Excel to value them.

Why Is Lease Versus Buy Analysis Important?

Leasing vs buying is a strategic decision-making tool that can help companies make the most of their finances. By comparing cash flow over different time frames, organizations can discover whether a lease or purchase makes more economic sense in terms of returns and savings. Leverage this approach to maximize the use of funds and ensure your bottom line stays healthy!

The Process

Step 1: Create an Inputs Tab

First, we will create an input tab as discussed in Organizing Excel Like a Pro. The inputs should be the following Economic, Lease, and Buy inputs summarized and Excel-ready.

Economic Inputs

• Inflation Rate – A standard assumption is 3%, but you can find a more detailed schedule on Statista.
• Discount Rate – This is the rate used in cash flow analysis to account for the time value of money.
• Tax Rate – This is the average tax rate for your company with a standard assumption of 20%.

Lease Inputs

• Lease period – How long will the lease last
• Lease Payments – The lease will spell out upfront and ongoing payments due, which provides an effective interest rate.
• Maintenance Costs – Most leases require the leaseholder to pay certain maintenance and operating expenses (oil changes, utilities, etc.). Estimate the cost and timing of these expenses. You may also have to pay for wear and tear at the end of the lease term.
• Upfront Costs – Some leases require a downpayment that you will need to consider
• End of lease fees – Some leases require payments at the end of the lease
• Early termination – Some leases require an early termination fee if you return the equipment before the end of the term
• Tax Deductions – Leases can be deducted as you pay the monthly expense when the equipment is used for business purposes.

Buy Inputs

• Investment Costs – These are the upfront costs of the asset plus sales tax and freight.
• Cost of Capital – Both the cost of capital for your business to assess the discount rate and the cost of debt if you choose to finance the asset.
• Maintenance/Operating Costs – As the owner, you are responsible for all maintenance and operating expenses. Estimate the cost and timing of these expenses.
• Capital Improvements – You may need to complete renovations and other improvements for long-term assets.
• Monetization/Disposal Value – Most assets will have some value on the date of sale. This is sometimes referred to as disposal value or terminal value.
• Tax Deductions – Purchases are capitalized as assets and can be depreciated over the vehicle’s life when used for business purposes. Using MACRS, you can accelerate depreciation and often save money.

Step 2: Build Amortization and Depreciation Tabs

Next, you must create amortization tables for any loan to calculate interest and principal payments. We can skip this piece since we will pay for the asset out of available cash. Regardless of payment method, you must create a depreciation table for Tax depreciation.

Step 3: Layout the Cash Flow Analysis

Now, lay out the two discounted cash flow analyses. The first will be for the lease and the second for buying. Don’t forget to account for the Tax shield and, of course, gain/(loss) on the assets.

Step 4: Compare the Net Present Value and Make a Recommendation

Compare the Buy NPV to the Lease NPV. Whichever one has the higher NPV is the winner! That said, always ensure that the cash is available to purchase. Otherwise, you must find a loan or lease regardless of the NPV.

Bonus Step: Run Scenarios

Since this analysis is dynamic, you can adjust the inputs tab for different deal terms and see ways to maximize value. For a side-by-side comparison, duplicate all of the tabs.

Real-World Example

Lease Calculator Excel Workbook

Example: Lease or Buy Fleet Vehicle

Step 1: Create an Inputs Tab

• First, we will create an input tab as discussed in Organizing Excel Like a Pro. The inputs should be the information to collect (above) summarized and Excel-ready.

Step 2: Build Amortization and Depreciation Tabs

Next, you must create amortization tables for any loan to calculate interest and principal payments. We can skip this piece since we will pay for the asset out of available cash. Regardless of payment method, you must create a depreciation table for Tax depreciation.

Step 3: Layout the Cash Flow Analysis

Now, lay out the two discounted cash flow analyses. The first will be for the lease and the second for buying. Don’t forget to account for the Tax shield and, of course, gain/(loss) on the assets.

Step 4: Compare the Net Present Value and Make a Recommendation

In this example, the NPV for leasing is ($24K) while the NPV for Purchasing is ($17K). Purchasing is the clear winner. That said, always ensure that the cash is available to purchase. Otherwise, you must find a loan or lease regardless of the NPV.

Bonus Step: Run Scenarios

Since this analysis is dynamic, you can adjust the inputs tab for different deal terms and see ways to maximize value. For a side-by-side comparison, simply duplicate all of the tabs.

Tips and Tricks

Finding Data And Assumptions

An analysis is only as good as the data and assumptions you put into it. So where can you find solid data? Your financial system is the first place to look if you have an existing business. Historical data is one of the best inputs for analysis. You can also work with your operations teams to understand what it will take to deliver a certain level of performance.

You will have to dig deeper for a new business or product line with historical info. Economic data and market research are your best bets. This can include digging into resources like the Consumer Product Index (CPI) for inflation or studying your competitors.

Step Back And Do A Gut Check

As you get into the weeds of your analysis, it is important to step back and ask yourself, “Does this make sense?” Think about how the cash flows look year-over-year and sequentially. Do you have the cash flow available to support this plan? Do the NPVs seem reasonable and in line with how assets perform today?

It is critical to sanity-check your work to ensure you put out a great product

Frequently Asked Questions

Is It Better To Buy Than Lease?

This is a complicated question that depends on the specifics of your situation.

Consideration 1: If you can buy an asset with cash on hand, it may be more beneficial in the short term because you can immediately write off depreciation expenses, and often postpone taxes owed until you sell the asset.

Consideration 2: If you cannot pay for the asset outright and must finance it, leasing may be more beneficial because financing costs will usually be lower than what a lender would charge for a loan.

Consideration 3: If you require an asset for longer than its useful life, buying may be better since leased assets have to either be returned or purchased at the end of their term.

What Are The Downsides To Leasing?

Caution should be taken when leasing an asset.

Downside 1: Leases may have a higher overall cost over the life of the lease than if had purchased the asset outright, as leases typically involve regular payments over time.

Downside 2: You will not own the asset at the end of your lease term, and caution should be taken when leasing an asset. Leases may have a higher overall cost over the lease’s life than if purchased the asset outright, as leases typically involve regular payments over time, and you will not own the asset at the end of your lease term.

Can I Use This Process To Evaluate Leasing A Car?

Absolutely! This same process can be applied to evaluate whether you should lease a car. The key elements are the same:

• Determine how much you would pay if you purchased the car outright (the list price of the car plus any additional fees and taxes).

• Calculate the down payment and monthly payments for leasing and buying over the same period.

• Compare this cost to the current market value.

• Calculate the NPV for both buying and leasing.

You can decide which option is best for your situation by comparing these numbers.

Is A Down Payment Required When Leasing?

Yes, a down payment is usually required when leasing a car, truck, or other equipment. The amount of the down payment depends on you or your business’s credit score, monthly payments, and other factors.

If you have excellent credit, you may be able to secure a lease with no money down or just pay a minimal amount at signing. However, if your credit score is not so great, you may need to pay a significant amount for the security deposit and down payment.

How Can I Calculate Monthly Payments For A Lease?

You can use our handy Excel Lease calculator! This makes it easy to calculate the monthly payment on a car, truck, or any equipment.

How Can I Get A Lower Monthly Payment?

Hoping to get a lower monthly payment on your lease? Here are some tips:

1. Opt for a longer-term lease – A longer-term lease can reduce the amount of interest you accrue and thus make your lease payments and monthly cost more manageable.

2. Negotiate with the dealer or lessor – Before signing any contract, make sure to negotiate the price.

3. Consider a sale-leaseback option – If you own your equipment, you may be able to use it as collateral for a sale-leaseback transaction. This type of lease allows you to make lower monthly payments since the lessor assumes ownership and takes the depreciation hit instead of you.

4. Stepping into a lease without understanding all the fees and other costs involved can be a costly mistake. Make sure to ask your dealer or lessor about every fee associated with leasings, such as maintenance, taxes, and insurance.

The easiest way to end up with a higher monthly payment is to have bad credit. Lessors will charge higher monthly payments to offset the risk of not getting paid.

How Can I Get A Lower Down Payment?

You may also be able to negotiate a smaller down payment if you have good credit or are willing to commit to a longer-term lease.

How Can I Calculate Residual Value For A Lease?

We have an Excel Residual Value calculator too! This will help you understand the benefit or risk of early termination fees and whether or not to purchase the equipment at the lease end.

Have any questions on lease versus buy or Microsoft Excel? Are there other topics you would like us to cover? Leave a comment below and let us know! And make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.