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
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 Premium 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 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.