How to Calculate It in Excel "Lease Liability Amortization Schedule"

-
 


    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.
    A lessee’s obligation to make the lease payments arising from a lease, measured on a discounted basis.

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

    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.

    Comments

    Post a Comment

    Popular posts from this blog

    Using T-Code GGB1 (Substitution Maintenance) to create FI Substitutions

    -
    Image
    Substitution in SAP FI is done to insert/infuse values into existing set of value(s) in fields. There are many cases where functional consultant don’t want to clutter up things using User-exits and SAP Enhancements. This is one of the best tool/method provided for keeping all the things/coding tidy as far as simple Substitutions are concerned. For complex Substitution, of-course User-exit (within the Substitution) needs to be written down as per requirements. In this step-by-step document, we will traverse through a simple Substitution case involving Customer User-Exit showing the flexibility of SAP Substitution. Our Example Case: Substitute ‘Additional asset description’ field (ANLA-TXA50) value with 15 digit code maintained in Key word for G/L accounts in G/L account master. 1. Execute Tcode  GGB1  and define the Substitution. Insert step in it. Here you can define the prerequisite based upon your requirements. In our case lets say we want it in all cases so we ...
    Read more

    SAP PSM-FM (Fund Management-BCS) Integration with other SAP Module

    -
    Image
      Financial and Asset Accounting Integration LESSON OVERVIEW This lesson will explain the process to integrate Funds Management, core processes within purchasing and the uses of Asset Accounting components. LESSON OBJECTIVES After completing this lesson, you will be able to: Define the business processes that are integrated in Funds Management Explain the ways in which the Controlling, Project System, Asset Accounting, Plant Maintenance, and Sales and Distribution application components can be integrated with Funds Management Compare the different core processes that take place within purchasing, and within payroll and trip cost accounting Troubleshoot the individual steps to be followed and a detailed explanation of how they are inter-linked and how assessed value is adjusted         Figure 146: Financial and Asset Accounting Integration         Figure 147: Integration FI - Funds Management A commitmen...
    Read more

    SAP FICO (SAP Finance and SAP Controlling)

    -
    Image
    In this post I'll try to explain what the definition of SAP FI (Finance) and SAP CO (Controlling) Companies that implement SAP SAP FICO is a core functional component in SAP ERP Central Component that allows an organization to manage all of its financial data. The purpose of SAP FICO is to help companies generate and manage financial statements for analysis and reporting and help with effective business planning and decision-making. SAP FICO consists of two modules, SAP Finance (FI) and SAP Controlling (CO), each of which is used for specific financial processes. SAP FI deals with overall financial reporting and accounting while SAP CO focuses more narrowly on planning and monitoring costs. SAP FI and SAP CO were originally released as separate modules but are now so tightly integrated that many people refer to them as simply one module. SAP FI modules SAP FI enables companies to generate financial statements -- such as balance sheets or profit...
    Read more