Vehicle Leases work similarly as Car Loans; however there are two major differences.
The first difference is the ownership. If you take a car loan then you take the ownership from day one. The bank or credit institution sometimes may place financial interest so they can reclaim the car if you default on your loan.
The second difference is the Buyout or Residual Value. Car loans normally go towards a full repayment. On the other side, car leases often come with a buyout value.
It means that at the end of the lease term your leasing company either takes the car back or you can purchase it for agreed Buyout or Residual Value.
We have created an Excel calculator to cover that scenario.
To download Demo version: click here
To purchase Full version: visit this page
The free version is fully functional calculator with only one restriction – you can’t change the interest rate.
You can download the free version, try it and see if it meets your needs.
If you decide to buy the full version, there are no restrictions in it.
Multiple Leases Calculator
Our calculator is developed to accommodate multiple leases at once. This can be very helpful especially for companies which have several capital or finance leases and need to perform monthly accounting.
The main challenge with the lease accounting is the monthly reporting. Every accounting period you need to calculate the principal and interest values for all your leases and then consolidate it.
Many accountants do this task by maintaining individual lease spreadsheets and then manually summing up the numbers.
This practice is sufficient when you have just handful of leases. But once their number grows to let’s say 10, 20 or more then it can create some headache. Maintaining individual lease spreadsheets can be quite time consuming.
Our multiple lease schedule calculator will let you enter all leases into one master spreadsheet. From there, it will generate individual schedules as well as consolidated schedule.
So, instead of maintaining multiple sheets and manually adding them up each and every month, now you can simply have our calculator do it all for you. Possible time-saving potential is enormous.
Uses of our Lease Payment Calculator
The headline of this article says that the calculator is for vehicles. That’s right; the most common use will be for auto or car leases. However, it can be used for any type of capital or finance leases.
If you manage leases for personal vehicles, trucks, machinery and other commercial assets then you can use our lease calculator for all of them.
Simply enter into the Asset description field the name of your loan, lease or asset and you are good to go! There are endless possibilities for which this excel calculator can be used.
Number of Leases
The number of leases by default is set to 80. If you need more, this can be easily achieved.
To do so, simply extend the master data table (“Entry Data Tab” described below) and copy the last row to as many new rows as you need.
Number of Combined Repayment Periods
The combined number of repayment periods in this calculator is set to 9000. Again, this is not the maximum the calculator can handle. This number has been chosen to keep the excel file to a reasonable size for download.
Similarly as in the previous point – if you need more, you can easily extend it. Let’s say you have 100 leases, each with monthly payments for 10 years. You will need 100 x 10 years x 12 months = 12,000 periods.
To adjust the calculator, go to the “calculation” tab (it’s hidden, so unhide it first) and simply copy and paste the last row to the following 3000 rows (or more).
This change will obviously make the excel file size bigger, but it shouldn’t matter too much once you save the file on your hard drive.
We tested this calculator for large volumes of data and it should be capable to handle even couple of tens of thousands, perhaps even hundreds of thousands payment periods.
It will affect only one thing – the calculations and pivot tables refresh times. But, compared to manual calculations you have been doing so far, it still offers incomparable time savings.
Data Outputs & Reports
Our Lease Payment Calculator includes 5 tabs. If you need more specialised reports, it’s possible to add more.
Entry Data Tab
As mentioned above, first you need to enter all lease details into the master data table. Once this is done, you are just few clicks away from final data.
After entering all lease data into master data (or entry data) sheet, click on the button “Refresh”. It will refresh all below mentioned built-in pivot tables.
Individual Schedule Tab
It is a pre-define pivot table to generate individual Lease Schedules. Just select from the drop down box any lease or loan you want and that’s it! In an instant it will populate a Lease Schedule for that loan.
Please note, that if the lease has a Buyout or Residual Value specified then in the last period you will see a big jump in Principal and Total Repayment Amount.
Our calculator assumes that at the end of lease you will pay-off the Residual Value and assume the full ownership of your leased asset.
The individual schedule is showing payments for each payment date.
Consolidated Schedule Tab
This Tab includes consolidated values of Principal, Interest, Total Payment and Closing Balance of all your loans and leases.
The structure is very similar to the individual schedule, however, showing the summary data. The difference here is that this report presents values per month and not individual Payment Dates.
Repayment Calendar Tab
In this report you can choose a month and it will show you all Payments due in that month.
Being able to see all individual Lease Payments per month should help you with Planning and Budgeting of your Cash Flow.
Charts Tab
The last tab offers a review of your financial position in a graphical way.
First Chart will show you the cumulative amount of all Payments as a monthly trend. You will be able to see irregularities or spikes caused for example by differences in Repayment Frequencies.
For example, if some leases are paid weekly, other monthly or fortnightly then in some months you may see them converging, causing the spikes. It should help you prepare for it well in advance.
Second chart is featuring the Total Balance Owed and its monthly trend. Reviewing this chart will let you know how your Total Lease Outstanding Balance is progressing over time.
Conclusion
We hope that our Lease Payment Calculator in Excel will help you manage your Loan or Lease portfolio more effectively!
If you have any questions, please let us know it the comments below or contact us through the Contact Form.