- What is lease liability?
- How to create the lease amortization schedule and calculate your lease liability
- Create five-column spreadsheet
- Enter number periods and cash payments
- Enter expense formula
- Fill expense column
- Enter liability reduction formula
- Enter liability balance formula
- Fill remaining liability balance
- Perform “What-If Analysis” on liability balance
- Set liability balance value to 0 with goal seek
- Click “OK”

**lease liability**in the same step, using Excel.

This information is important for all companies, not just for companies that plan on continuing to use Excel spreadsheets for lease accounting. Even if you plan to use lease accounting software for compliance with the new standard, you can use the information in this blog to ensure that your chosen software provider is performing this calculation accurately. Transitioning from ASC 840 to ASC 842 can be difficult, but there are resources that can help you gain an understanding of the methods laid out below for our calculations.

## What is the lease liability?

The lease liability is defined as the present value of your future lease payments. This is calculated as the initial step in accounting for a lease under FASB ASC 842, and this amount is then used to calculate the ROU (right-of-use) asset, that is recorded in addition to the liability for operating leases and capital leases.

Alessee’sobligation to make thelease paymentsarising from alease, measured on a discounted basis.

## How to create the lease amortization schedule and calculate your lease liability

**Download our free present value calculator to follow along:**

Here are the steps to follow to calculate the present value of lease payments and the lease liability amortization schedule using Excel when the payment amounts are different, starting with an example:

Calculate the present value of lease payments for a 10-year lease with annual payments of $1,000 with 5% escalations annually, paid in advance. Assume the rate inherent in the lease is 6%.

### Step 1: Create an Excel spreadsheet with these five columns

Create a new Excel spreadsheet and title five columns with the following headers: Period, Cash, Expense, Liability Reduction, and Liability Balance, as shown below:

### Step 2: Enter number periods and cash payments

Enter the number periods starting from 0 to 9, and enter the cash payments in each period. Because payments are made in advance, the first payment of $1,000 is made in period 0. The annual payments then escalate at a 5% rate. Please see illustration below:

### Step 3: Enter the expense formula

Enter “0” for expense in period 0 (because payments are made in advance). In expense for period 1, enter the cell for the period 0 liability balance and multiply by 6%. See below.

### Step 4: Fill the expense column

Copy the formula for expense in period 1 down for the remaining expense columns.

### Step 5: Enter the formula for liability reduction

The formula for each liability reduction image is the corresponding cash minus the corresponding expense. See below.

Copy the formula down the entire liability reduction column.

### Step 6: Enter the formula for liability balance

Enter “0” for the liability balance in the line above period 0. In liability balance for period 0, enter the cell for the above cell’s liability balance minus the liability reduction in period 0. What you will end up with is the previous liability balance, reduced by the current liability reduction (see below).

### Step 7: Fill the remaining liability balance column

Copy the formula for liability in period 0 down for the remaining liability balance columns.

### Step 8: Perform “What-If Analysis” on the liability balance

Select the liability balance for period 9. In the top bar in Excel, go to the “Data” tab, then the “What-if Analysis” Tab, then select “Goal Seek.”

### Step 9: Set liability balance value to 0 by using goal seek

In the dialog box that follows, make sure the “Set cell” is set to the cell representing the liability balance for period 9, in the “To Value” enter 0, and in the “By Changing Cell” enter the cell representing the liability balance for the period above period 0. See below.

### Step 10: Click “OK”

After you click ok, Excel will prepare your amortization schedule for you automatically. See below.

Based on this, the present value of a 10-year lease with payments of $1,000 annually, 5% escalations, and a rate inherent in the lease of 6% is $9,586.

### Summary

This schedule will help you make your journal entries for the entire life of the lease, if you’re using Excel. If you’re using a lease accounting software, the information above will help you cross-check the calculations performed by your provider so you can ensure accuracy.

Dear Sir,

My question is regarding interest expenses regarding a 10-years lease contract. We have made advance payment for two years (2019 & 2020).

Currently we have recorded Dr.Prepaid rent and Cr.Cash.

If we move to IFRS16, We will do Cr.Prepayment and Dr.ROU?

So, my question is when we need to start record interest expenses?. My understanding is it shoud start record from 2021 because prepayment is up to 2020.

Please kindly reply me the correct recording.

Thank you in advance.

Best Regards,

Myint Myo

Mr. Myo,

Thank you for your question. Yes, as your question deals with the IFRS 16 standard you would, upon transition, credit prepaid rent and debit your ROU asset. Similarly, you would need to start recording interest expense upon transition. If you need any further clarification please let us know.

How to use the goal seek to calculate the incremental borrowing rate in ifrs 16

Hi Rady,

Thank you for taking the time to contact us. This article is demonstrating how to build an amortization table using the effective interest method in Excel. We show you how to use the goal seek tool in Excel, but this is only applicable after the rate inherent in the lease or the incremental borrowing rate is determined. The incremental borrowing rate and the inherent rate are used to present value the lease liability, and the effective interest rate is the actual interest paid taking into account the compounding of interest over time.

Best,

Kiley

We have paid a fixed amount of payment for our leases without any additional cost. My question is how do we get the incremental interest rate when we are calculating the PV ?

Hi Samuel,

Thanks for reaching out! ASC 842 and IFRS 16 define the incremental borrowing rate similarily as the rate a bank would charge for obtaining a collateralized loan with like terms and dollar value to your lease. The calcuation for the incremental borrowing rate is quite complex and we suggest seeking assistance from your finance and/or treasury department(s) to determine the correct rate for your organization.

Regards,

Kiley