Webinar
Becoming Excel-lent at Lease Amortization: The $15,000 Excel Formula
When it comes to spreadsheets, Excel is the undisputed champion, so much so, that they’re nearly synonymous. As a company started by accountants, that’s why we created FloQast to work WITH Excel, creating a smooth close process, powerful checklists, and reconciliations that sync with the data you’ve already got in Excel.
As loans and leases become more important — and complex — in your accounting (mostly due to ASC 842) it’s crucial to stay on top of amortization. Excel can help you do that in a big way. In fact, Excel has the power to save you $15,000 in about 15 minutes, if you know how to leverage it. More on that in just a minute.
We brought Jim Cline, founder and developer of ExcelCEO – Microsoft Excel and Access training – to talk about amortization with Excel. Jim literally wrote the book on the powerful ways accountants can use Excel.
One of the beautiful things about Excel is how logical the formulas are. A few simple formulas can be used to create an amortization schedule that can be copied all the way down for the entire amortized life of every asset you own.
The Setup
Watching Jim create this amortization schedule is a bit like watching a sculptor with a big lump of clay. But unlike sculpting, you can learn the basics and create the same exact thing very quickly.
Jim started with a simple table of assets to be amortized, including asset number, cost, payment start date, term, and interest rate. That table is the foundation, as the formula is set to reference those numbers.
Carry the One
Using straightforward formulas, you can pull the numbers from your table to create a full amortization schedule. Each line is based on the one before, so you can fill all the way down until the asset is fully amortized. It’s like learning to “carry the one” in elementary school math, but a whole lot more powerful.
You can build multiple assets, loans, or leases into one schedule so you have everything in one place. All your payments, beginning and ending balances, interest paid, and principal paid for every month of the amortization. Closing the month and reconciling those accounts just got a whole lot easier!
How to Save $1,000 per Minute with Excel
In the webinar, Jim shared a story to illustrate the power of Excel for amortization, and the potential time and cost savings of using this approach.
“Somebody came to me and said ‘Jim, we have got 5,000 vehicles with this company, and we need to have an amortization schedule just exactly like this. But, when it goes down to the very last amortization period, we need for it to switch to the next one, and then the next one, and the next one.’ They said, ‘The leasing company that we’re dealing with, they were going to charge us $15,000 to do that. A big four company is going to charge us $12,000 to do it. Is there any way that you can figure out how to do it all into one schedule?’ It took me about a half-hour to think about it, that’s one of the cool things about some of the things that I write in my book. I give you all of the knowledge that you need to arm yourself with all of these different logical ways of thinking. Let me show you what I did.”
Watch the Master Work
We love Excel as much as anyone — probably more than most — but we realize it’s far from sexy. Just reading about Excel isn’t always the easiest way to make it work for you. Often, you need to watch over the master’s shoulder to see exactly what to do.
Luckily, you’ve got access to our on-demand webinar where Jim shares his screen and you can see every formula, every cell, and every trick he uses to create this epic 5,000 line amortization schedule in about 15 minutes of work.
