Car Lease Spreadsheet
- Last Updated: Wednesday, 17 February 2021
When shopping for a new car, consumers have a number of choices. One of those options includes leasing a car. In this article, we're going to provide a car lease spreadsheet, which is an Excel workbook that can be used to calculate monthly car lease payments.
The spreadsheet is as sophisticated as any other online version found on the Internet. Since we're providing this as a free downloadable tool, it was designed to deliver more flexibility than many of the other calculators on the web.
The remainder of this article will be dedicated to providing detailed instructions. This will reduce any confusion over the use of the spreadsheet. We'll also explain some of the terminology that might be encountered when evaluating a car lease. Finally, as appropriate, we've included some examples, or suggested values, to use in the spreadsheet.
Car Lease Spreadsheet Instructions
The spreadsheet can be downloaded by clicking on the following link: Car Lease Calculator. Once the spreadsheet has been opened in Excel (it should also work in LibreOffice or OpenOffice Calc), users will notice the tool already contains example information. All users need to do is overwrite the example values with the car lease information they're evaluating.
When a cell value is "blue," this indicates the need for an input. The remaining cells are used for spreadsheet calculations or explanations.
Gross Capitalized Cost
The first value that is going to be calculated is the gross capitalized cost of the vehicle. The important inputs include:
- MSRP: this is the Manufacturers Suggested Retail Price or sticker price of the car. This value can be found on the vehicle manufacturer's website if unknown.
- Base Cap Cost: the base capitalized cost is the negotiated price the user would be willing to pay for the car. This value is usually less than the MSRP, unless the car is experiencing extremely high demand and low supply.
- Additional Lease Costs: dealer acquisition fees are nearly always included in a contract. Other items, such as , might also be purchased as part of the deal. For example, someone might want to purchase an extended warranty for a 48-month lease on a car when the manufacturer is offering a standard 36-month warranty.
Capitalized Cost Reductions
The next step in calculating the lease payment is to figure out how much capital, or money, the user is willing (or required) to pay upfront. This is known as the capitalized cost reduction. The larger the upfront payment, the lower is the monthly lease payment. Generally, there are two ways to reduce capital:
- Down Payments: this is cash, usually in the form of a bank check, which a lessee is willing to put down on the car. Many leases will specify a minimum down payment.
- Trade-In Allowance: if a car is owned, and it's traded in as part of the lease, then take that allowance on this line in the calculator.
Lease Terms and Taxes
In this section, users are going to provide some of the lease terms they've been offered by the dealership or perhaps have seen in an advertisement.
- Residual Value: in case the residual value of a car isn't known, we've provided a calculation based on the lease term or length. If the actual residual value is known, just overwrite the calculated value.
- Money Factor: this is the equivalent of an APR on a loan. In fact, multiplying the money factor by 24 reveals the effective interest rate on the lease. Likewise, if the interest rate is divided by 24, this reveals the money factor.
- Lease Term: this is the length of the lease, stated in months.
- Sales Tax: this is the rate of sales tax charged in the user's state.
Calculation of Lease Cost
This section of the spreadsheet computes the total of all out-of-pocket payments associated with the lease. This is the total amount of money that needs to be provided as part of the agreement.
- Depreciation: this is the loss in value of the car over the term of the agreement. For a car lease, depreciation is simply the net capitalized cost minus the car's residual value.
- Finance or Rent Charge: this is the rent charged on the car over the life of the lease. This considers the car's value, the money factor, and the term of the lease.
- Sales Tax on Lease: if the state collects a sales tax, payment is required on car leases too.
- Total Lease Payment: this is all of the money that will be paid as part of the lease. The value includes sales tax, rent charges, depreciation, trade-ins, and down payments.
Monthly Car Lease Payments
This is the part most users are interested in: An estimate of the monthly car lease payments. This output isn't going to be reviewed line-by-line because the concepts have already been explained. This section converts the lease costs into monthly payments by dividing those values by the term of the lease.
It's easy enough to print out a copy of these instructions, and refer to them when using the car lease spreadsheet. The tool can be very useful when playing "what if" scenarios, to see how changing an assumption affects the monthly payments. Keep in mind this tool is not intended to replace the calculators used by dealerships. The intention here is to provide consumers with quality information, so they are better prepared when they negotiate with leasing companies or dealerships.
Online Car Lease Calculators
If an online car lease calculator is preferable, we can help there too. In fact, we have a complete line of car loan and lease calculators that users can access for free. Included in that offering is an online car lease calculator, which follows the same format as the above spreadsheet. All of our online tools come complete with instructions too.
About the Author - Car Lease Spreadsheet