AASB in Excel: The Ultimate Guide
This article will outline and walk through a solution to AASB16 in Excel. Grab a coffee and get comfy, this guide will be complete from start to finish. If you’re looking to solve AASB in Excel, you will not need to look any further than this page.
Our solution that we walk through in this article will be different from others that you might find floating around the internet for one very important reason: we will calculate the required entries precisely. Every other article we’ve come across makes mathematical approximations that don’t really work in real life. Interest, for instance, is not charged at 1/12th of an annual cost. If it’s going to be done, we believe it should be done correctly!
AASB16 – Leases is the new accounting standard to be adopted in financial years beginning from 1 January 2019 which brings operating leases back onto the balance sheet as assets and liabilities. Fundamentally, the new standard removes any financial statement-driven incentive (i.e. being “off balance sheet”) to operating leases. This new standard will not eliminate leasing as a funding product, but it will recognise leasing as a funding product – there is logic to the new standard!
As you are on the Leases16.com website, you will know that we have created a calculation engine for AASB16 that takes cashflows and turns them into accounting entries. Why then, you may wonder, would we provide a solution for people to learn how to solve their problem in Excel? Well, as we’ve written [link] we don’t think Excel is the best way to run your AASB16 solution but we are also pragmatic. As accountants, we have a strong background in Excel and use it to solve all sorts of challenges. If we only had one or two leases and plenty of time to ensure that we don’t make any mistakes then we would probably use Excel also! Well, we probably would until we realised that using Lease16 would actually be less expensive, all things considered!
There are a number of steps to solving AASB16 from start to finish. This guide assumes that you have done all of the pre-work for your leases – identification of your leases, understanding and documenting the known cash flows, determinations around option periods, and determining your company’s incremental borrowing rate .
We will focus on a building lease throughout this example, as this is probably the most common type of operating lease here in Australia and the structure of these leases is fairly standard. However, if you have other types of operating leases this guide will still be useful for you as you will find out that the only difference between operating leases for the purpose of AASB16 is the structure of the cash flows – a task simply solved in Excel.
To be specific, we are going to solve AASB16 for the following lease:
- Property lease beginning 1 July 2019
- 10 + 5 + 5 term (10 years initial term, two 5 year options). Assume at the outset we expect to exercise the first option only.
- Lease payments beginning at $5,000 per month, paid on the first of the month in arrears
- CPI + 3% annual increments
- Expected remediation costs of $25,000 at the completion of the lease
- An administration fee of 1% of payments, paid monthly
- Legal fees of $2,000 to close the lease.
With those details, we have all the details usually available to solve this AASB16 problem in Excel (note, not all the details required…). The steps we are going to follow are as follows:
- Build the cash flow profile.
- Determine the Liability via discounting of the cash flows.
- Determine the Right of Use Asset with respect to the Liability and any required adjustments.
- Create an Asset depreciation schedule.
- Create a liability payback schedule.
- From the above, determine the requisite accounting entries.
Conveniently, we will create a new sheet in our Excel workbook for most of the above steps.
So, now we have a plan so let’s get going.
Step 1: Build the Cash Flow Profile
It should be noted up front, that what we are going to build here is a calculation engine. We will not be focusing on automation, or modeling best practices – both of which we would spend time implementing were we going to actually solve AASB16 in Excel.
So, to recap, we have a property lease with the following details:
- Start Date: 1 July 2019
- 10 + 5 + 5 term (10 years initial term, two 5 year options). Assume at the outset we expect to exercise the first option only. Therefore, the term of the lease will be 15 years, finishing on 30 June 2034.
- Lease payments beginning at $5,000 per month, paid on the first of the month in advance. Therefore, our final payment will be on 1 June 2034.
- CPI + 3% annual increments . The cash flows will have to increase by 3% yearly beginning on 1 July 2019. We can only increment by what we know.
- Expected make good costs of $25,000 at the completion of the lease. On the final day of the lease we will assume we pay the remediation costs.
- An administration fee of 1% of payments, paid monthly . This will have to be calculated from the lease payments. As the payment is not tied to use of the property, we will need to include this in the lease costs and therefore the asset and liability values.
- Legal fees of $2,000 to close the lease. Closing fees are an adjustment to the right-of-use asset, not the liability. This cost will be an adjustment to the asset, not the liability.
So, in terms of building this initial sheet out, let’s create a column for each cash flow. Our headings in our cashflow table will be as follows:
These headings are detailed as follows:
Cash Flow Date: Simply, the dates on which cash flows happen throughout the term of the lease.
Lease Payments: The amount of the lease payment. On the 1st of July each year, this amount will be increased by 3%.
Make Good: This column will have just 1 cash flow on the final day of the lease. The amount, per above, will be $25,000.
Admin Fee: This column will simply be a calculated field with the formula of Lease Payments * 1%.
Cashflow Amount: The sum of the previous columns
Day Count: We will calculate the number of days to the cash flow from inception. Fortunately, Excel is very good at working with dates.
Discount: This is the discount factor based on the incremental borrowing rate (to be determined).
Discount Cashflow: This column is calculated as the Cashflow Amount multiplied by the Discount.
We will deal with the discount details in the next chapter.
For our immediate purposes here though, we have calculated the cash flow schedule fully based on the above. The file can be accessed here:
Step 2: Determine the Liability via discounting of the cash flows
Ok, so now that you have created a cash flow schedule, you need to determine what the present value of those cash flows is. This will determine your Liability value on day 1. The only further input you will need to calculate your discounted value is the incremental borrowing rate.
Incremental borrowing rate (IBR)
The incremental borrowing rate, officially, is defined as follows:
“The rate of interest that a lessee would have to pay to borrow over a similar term, and with a similar security, the funds necessary to obtain an asset of a similar value to the right-of-use asset in a similar economic environment.”
AASB 16
On first glance that is pretty straight forward, but when you start to think about it a little more deeply, there is quite a lot to unpack. Deloitte wrote a good piece on the incremental borrowing rate which illustrates the depth of what is required as follows:
Calculating the discounted value
So, once you have determined the IBR applicable for the asset or portfolio of assets, you are ready to calculate the present value of your future cash flows.
For demonstration purposes, we will use an IBR of 5%.
In order to calculate the discount factor for each of the cashflows, we will use the following formula for each of the cash flows:
= 1 / (1 + IBR) ^ (days from inception/365)
This is calculating the annual compounding discount factor of the individual cash flows. The next step is to simply multiply each cash flow by its discount factor, which will give you the present value of the cash flow.
The sum of the present values of the cash flows is your opening Liability value.
The Excel spreadsheet has been updated: