The post AASB in Excel: The Ultimate Guide first appeared on Leases16.

The post AASB in Excel: The Ultimate Guide appeared first on Leases16.

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

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:

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.

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:

The post AASB in Excel: The Ultimate Guide first appeared on Leases16.

The post AASB in Excel: The Ultimate Guide appeared first on Leases16.

]]>The post AASB16: Incremental Borrowing rate Key considerations first appeared on Leases16.

The post AASB16: Incremental Borrowing rate Key considerations appeared first on Leases16.

]]>One of the key challenges for implementing AASB16 – Leases is the determination of the incremental borrowing rate. This is a new concept that aligns well with the spirit of what AASB16 is attempting to achieve. The standard defines the incremental borrowing rate as is 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. If leases are to be brought back on balance sheet as an interest-bearing liability then determining an appropriate rate to borrow the corresponding asset for the term is more than reasonable.

The first instinct most people have is to use their current cost of debt as their incremental borrowing rate. Clearly, this is unlikely to be correct – particularly if your current debt facilities are unsecured or are secured with assets that do not resemble your leased assets or the terms are significantly different. The incremental borrowing rate can have a material impact on the asset and liability you recognise at the outset of your lease, so it is worth at least attempting to determine an incremental borrowing rate that approximates what the standard requires.

One challenge that must simply be acknowledged is that the rate will inevitably have some level of fantasy in it. This is a necessity due to the nature of the lending market, particularly (but not peculiarly) in Australia. No bank will lend money for 25 years to a corporate entity, secured or not. It’s simply not how the market works. However this could quite possibly be what is required for a 15+5+5 property lease, depending on your expectations at the outset. So it is not realistic to determine a precise rate, but it is at least worth attempting to determine an incremental borrowing rate that approximates what the standard requires.

Per the definition, there are three characteristics of the incremental borrowing rate under AASB16 that need to be considered to determine an appropriate incremental borrowing rate – your cost of credit, the security being offered, and the term over which the money is being “borrowed”.

Your cost of funds should be the starting point for your determination of your incremental borrowing rate. However, you should give some consideration to how long ago you agreed that rate, whether any security was provided, and also whether the borrowing is fixed or floating.

If you are 3 years into a 5 year borrowing, the margin on your borrowings is unlikely to be the same now as it was when you agreed it with the bank. You should consider prevailing interest rate and borrowing conditions when determining your incremental borrowing rate. Furthermore, the incremental borrowing rate, where you determine it, is just one rate. This implies that you really need to be considering a fixed rate proxy – in a normal curve environment this will result in a higher fixed rate than the floating rate.

Security is an important contributor to a company’s cost of funds as it provides a way for the bank to mitigate its risk. The faster an asset depreciates, the less security it provides and therefore the higher rate it will attract. All else being equal, the interest rate on kitchen equipment will be higher than for a motor vehicle which will be higher than yellow machinery that will be higher than property. So if you have different assets leased, an incremental borrowing rate attached to each of those asset classes would be appropriate.

The term of the borrowing is the one area of determining your incremental borrowing rate that will require the most judgement and has the potential to have the largest impact on the rate. Leases, particularly on property, often extend well beyond the term that banks are willing to lend money in Australia. So in that circumstance there is little you can do but guesstimate – a rule of thumb observed in the past was for the increase in margin to be 15 basis points increment for each year increment. This works out to about 7 years, but likely reduces from there.

In conclusion, precision in determining your incremental borrowing rate for your leases is unlikely but that does not mean that you should just adopt you current borrowing rate – that may result in a materially incorrect asset and liability recognised. Rather, you should start with your current borrowing cost and make the following adjustments:

- Adjust for market movements from your most recent refinance.
- Adjust for floating to the fixed base rate.
- Adjust for the security being offered. Different security will result in different adjustments to the original rate.
- Adjust for the term.

Your banker is likely to be the most appropriate person to speak to regarding these adjustments. If you make them you will have a much more defensible position when you speak to your auditors than if you simply attempt to use your current borrowing cost.

The post AASB16: Incremental Borrowing rate Key considerations first appeared on Leases16.

The post AASB16: Incremental Borrowing rate Key considerations appeared first on Leases16.

]]>The post AASB16 in Excel? 3 Reasons that's a bad idea first appeared on Leases16.

The post AASB16 in Excel? 3 Reasons that’s a bad idea appeared first on Leases16.

]]>Depending on your reporting year, AASB16 is either approaching rapidly or is still a little way away. For the majority of Australian companies whose year end is 30 June, adoption of AASB16 will occur on 1 July 2019. In our conversations with accountants and treasurers, it is clear that as the transition period approaches the mind is getting very focused as the reality of the work required to manage leases under the new standard settles in. We’ve heard from many companies who plan to run their lease portfolios in Excel – but we only hear this from those who are still some way from transition. No one who has spent significant time looking at the standard is genuinely considering running their AASB16 obligations in Excel – I’ll explain why it’s a bad idea in this article.

AASB16 is the new Lease Accounting standard that applies a significant change to the way that operating leases are accounted for. Through a process of financial engineering, the reported nature of operating leases will convert from operating cost to be recognised on balance sheet as an asset and a liability.

At the outset, I should make it clear that as a general rule I am not anti-Excel – quite the contrary. Those who know me know that I am capable with Excel – I even used to sell spreadsheet templates that I created! I have built an entire lifecycle solution to AASB16 in Excel, and we initially considered releasing it as an MVP, but in doing so it became increasingly clear that this just wouldn’t work.

So, based on our experience, here are the 3 reasons we believe trying to solve AASB16 with spreadsheets is a bad idea:

Building an initial solution to AASB16 is conceptually easy in Excel. It involves just a couple of steps:

- Forecast the cashflows,
- Discount the cashflows back to calculate your asset/liability,
- Amortise your asset and create a debt repayment schedule, and
- Calculate your journals from step 3.

The spreadsheet required is not particularly large in the first instance, and there are a couple of nuances – such as the assumptions around compounding of interest. Over time, however, the spreadsheet will grow as variations required due to changes such as CPI indexing will require the creating of new schedules. In our experience this is where the difficulty in a spreadsheet-based approach is most problematic. As your model grows to 20+ sheets and tens thousands of cells the possibility of introducing errors also increases.

The introduction of errors in spreadsheets is particularly problematic, as governance is one of Excel’s key challenges. With many team members over time accessing the spreadsheet, the chance of error increases exponentially. If you have a property lease spanning 15+ years, it is guaranteed that at some point someone with absolutely no understanding of the spreadsheet will be working on it. Furthermore, when an error is introduced, it may be a significant period of time before it is uncovered, and rolling the spreadsheet back will be time intensive and frustrating.

“What?! But Excel is free!”, I can hear you exclaim. Excel is inexpensive – you already have it installed on your computer. Despite this, your time is not free, nor is it inexpensive. It will take a significant amount of time to establish the spreadsheets for your AASB16 solution, more time to check them, and then more time again to run the leases through their life. Add to that the time taken to unpick and rollback errors, and your Excel solution to AASB16 is significantly more expensive that you initially estimate.

We built the Leases16.com lease accounting platform as a direct competitor to Excel in the management of your AASB16 obligations. When you take your time into account, our simple inexpensive pricing model will ensure that managing your leases through our platform is less expensive than attempting to do it yourself.

*If you are interested in trying out the platform, we are giving Beta testers free access for 3 months – just email us at hello@leases16.com to get the code.*

The post AASB16 in Excel? 3 Reasons that's a bad idea first appeared on Leases16.

The post AASB16 in Excel? 3 Reasons that’s a bad idea appeared first on Leases16.

]]>