If you are an Accountant for a small or medium sized company with multiple leases (or loans) then you probably experience problems with generating accounting entries every month.
What you need to work out are the principal and interest amounts for each lease and then summarize them. In case of just few loans or leases this isn’t a big issue.
But if you maintain several dozens of amortization schedules then the task can become quite time consuming.
How Capital / Finance Lease Accounting Usually Works
The typical scenario is like that: You maintain separate amortization spreadsheets for each lease. Then at the month-end you take the principal and interest amounts of each lease and sum it up.
And here is where the problem starts. One way to sum up all individual amounts is doing so manually. This, however, takes time.
Your second option is to interlink all spreadsheets and calculate the summary in one master spreadsheet. The hard part here is interlinking all these individual sheets. As we all know, this process in Excel doesn’t work smoothly all the time. Often the numbers don’t flow through.
Sometimes you must open all files at once in order for the numbers to get updated correctly. With dozens of files open at the same time your PC performance goes down. Sometimes it even crashes.
And what about the missing links? How often do you move a file to another place on your Disk and the link gets broken? Now you need to fix the formulas.
Sounds all familiar?
Wouldn’t it be nice to have all Individual and Consolidated lease schedules in a single Spreadsheet?
We thought so, too! Therefore, we developed this:
Multiple Lease Amortization Calculator in Excel
Here is the good news for you:
Our Excel Calculator that can take away all your headaches during the month-ends or year-ends.
With this calculator you can manage all your leases (or loans) in one Excel file, getting individual and consolidated schedules in one place.
To download Free Version: click here
To purchase Full Version: click here
Free Version of our Capital / Finance Lease Calculator includes all functionality, except for locked interest rate. Try it and if it suits your needs then you can purchase the full version.
In case you have any questions about this calculator then please talk to us. We will be glad to explain it.
Current limit of this calculator is 80 leases (or loans). The reason to minimizing the file size. Now it stands at around 2.6 MB. Extending it for more leases means that the Excel file will get bigger.
This should not be a problem, Excel can easily handle files even few dozen MB large. Just the opening and processing times will slightly increase.
What You Need to Run this Calculator
To be able to run the calculator you ideally need to have Microsoft Excel 2010 or later version installed on your computer.
The calculator may also run on Excel 2007. Just the List of Values boxes may not work properly, so you will need to type Loan Term manually instead of picking it from the Drop-down List.
We advise you download the free version and make sure that you can run it on your computer without problems.
Once you make sure that it runs fine then you can buy it. The Premium Version of the Lease Calculator looks and feels exactly the same as the Free Version, just allows you to maintain more leases (or loans)
Using this calculator is pretty easy. If you are familiar with Excel then it should be a piece of cake. But just to be sure that you understand it and are comfortable with all its functions, here we included instructions on how to use it.
Sheet 1 – Data Entry
In this sheet you need to specify all entry parameters for your leases (or loans) as shown in the image below.
Sheet 2 – Individual Schedules
Here you can get details for individual amortization schedules. Simply pick Lease or Loan name in field C4.
Note: Every time you add new lease or change parameters for existing lease, click on “Refresh” button to get the calculation updated.
Sheet 3 – Consolidated Schedule
This sheet will provide summary for Total Monthly Principal and Total Interest Amounts data for all your Leases per period (month).
Note: Again, every time you add new leases (loans) or change parameters of existing ones don’t forget to click on the “Refresh” button to get the data re-calculated.
Sheet 4 – Repayment Calendar
We have included Repayment Calendar to schedule your Lease payments. With this handy report you will never miss a single repayment. It will also help you with planning your Cash Flow.
Note: After updating Entry Data sheet, always hit the “Refresh” button to get the values refreshed.
Sheet 5 – Charts
Last, but not least important for your work are graphs showing Repayments and Outstanding Balance Trends. We have included these graphs to help you with your reporting.
You can print them and attach to your monthly management reporting pack. It will nicely show your financial position where you stand.
Note: As usual, don’t forget to hit “Refresh” button every time you change entry parameters of your Leases or Loans.
We hope you will enjoy our Lease Calculator. If you have any questions or suggestions then please do not hesitate to contact us through our Contact Form.
Please send me the password to unprotect excel free trial sheet
Hi there. Unfortunately, only the paid full version of the calculator is password unprotected.
I have paid for and downloaded Lease Calculator but only get Lease Register page and Macros are disabled. Nothing happens when macros enabled. Upadates have not helped as they want us to repay.We use Xcl 2007 and have Norton installed.
What could we do to get it to work?
The calculator is designed in Excel 2010, so some features may not work properly in Excel 2007. Instead of Marco button you can use standard “Update” button in Excel to refresh the calculations. Please direct your questions to email email@example.com – the comments sections here is not checked too regularly.
will this work through our company server or just 1 computor
It works like any other Excel spreadsheet on as many computers as you need.
This is a terrific spreadsheet – I use it to track many loans (about 60 of them) and it works wonderfully. I had a few questions of the author and he was amazingly responsive (considering how inexpensive the product is)
Could not be more effusive in my praise!
I am in need of something to track my capital leases. From looking at this spreadsheet, it seems like we have to fill out all the information. Most of the capital leases (vehicle lease), has no interest rate disclosed on the lease schedule. Will this spreadsheet able to calculate the interest given all other information is available?
This spreadsheet needs an interest rate as one of the entry parameters. If you don’t know the interest rate, it can be easily calculated in Excel using rate() formula.
Also, would it be possible to add a tax column?
The full version of this spreadsheet is not password protected and can be modified. You will need some Excel skills to do so.
Great worksheet Uncle Finance, it really keeps all of my companies capital leases amortized efficiently. Quick question, since the individual schedule is a pivot table, can I add a column to the entry data for a down payment amount?
Yes, you can add another column, but you need to be careful, not to break formulas. However, the new column will not be automatically included in calculations, it will be considered only as a “remark” column.
Am I able to bring in (link) cells from another tab where i have all the raw data or do i have to enter the data into the provided tabs.
I currently have a worksheet that provides the gross amount, interest rate, term, etc.
Yes, you can link data from another spreadsheet (file). It works like any other Excel workbook.