Calculating the weighted average remaining lease term is more than an accounting exercise; it’s part of the ASC 842 lease accounting footnote disclosure requirement. Understanding and reporting this figure accurately is necessary for meeting the standard’s demands for transparency in lease obligations.
In this article, we’ll guide you through a step-by-step approach to calculating this term using Excel, making it straightforward and practical for your financial reporting needs.
Related reading: Interested in learning more about the ASC 842 Journal Entries? Check out these guides:
Weighted Average Remaining Lease Term – The Formula
Let’s start with the formula first:
To explain the formula simply, the weighted average remaining lease term is determined by multiplying each lease’s remaining term by its remaining payments, adding these figures together, and then dividing the result by the total of all remaining lease payments.
This calculation gives us a single average lease term that accounts for the size or importance of each lease, which can be useful for financial analysis, especially in real estate and retail industries.
Weighted Average Remaining Lease Term – Excel Calculation
The Excel Formula
Let’s begin by understanding the overall formula used. In the Excel sheet provided, there are two columns labeled “Remaining lease term” and “Remaining Lease Payment”, with each row representing an individual lease. We’ll first apply the “SUMPRODUCT” function to calculate the total of multiplying the “Remaining lease term” (Column C) by the “Remaining Lease Payment” (Column D). Afterward, we take this total and divide it by the sum of all the “Remaining Lease Payment” values (Column D).
As a result, the weighted average remaining lease term is 1.32 years for the 5 leases.
Calculating Remaining Lease Term for Individual Lease
You can quickly calculate the remaining lease term for each lease in Excel by deducting the year-end reporting date (12/31/2024) from the lease end date (06/30/2026). Divide the result by 365 to convert the remaining term into years.
Calculating Remaining Lease Payment for Individual Lease
This calculation is simple. To find the remaining lease payment amount for each lease, multiply the monthly payment ($21,111) by the number of months left on the lease (18 months). If you have an amortization schedule, you can just total the remaining payments listed there. Remember, this total remaining payment does not represent the remaining liability, as it does not account for interest.
Why Does Weighted Average Remaining Lease Term Matter?
To answer the question, let’s look at what it means first. Using the example above, we have 5 leases with a weighted average remaining lease term of 1.32.
What this means: on average, each lease has about 1.32 years left before it expires, considering each lease’s size or value (represented by the lease payment). Without considering the “weighted” aspect, it’s difficult to determine the actual time before the leases expire since they have different end dates—some may have eighteen months left, while others may expire in six months.
Why does it matter? The weighted average remaining lease term is a very important financial indicator that’s assessed in many different situations. To list a few:
- Lease Renewal Strategy and Financial Planning: Management may initiate lease renewal discussions or begin looking for new properties ahead of the current leases expiring in approximately 1.32 years. This also helps with the financial planning for the company as the business can forecast a potential lease expense change in about 1.32 years.
- Investment Analysis: Investors might view a portfolio with a short weighted average remaining lease term, like 1.32 years, as more risky. For example, there is a potential for rent to go up shortly, which could affect the cash flows very soon.
- Credit Risk Assessment: Banks and creditors could see short-term lease obligations as a risk factor if they believe the company will face an increased cash outflow soon, such as when the renewal will occur in as soon as 1.32 years.
- Potential M&A: A potential buyer looking to acquire the company could use the 1.32-year average lease term to negotiate a lower purchase price due to the near-term cash outflows and liabilities associated with lease renewals or terminations.
- Contract Terms: Suppliers and partners might consider the company’s lease duration when determining the length of their contracts. For example, a company supplying vending machines for the workplace might suggest a contract that matches the 1.32-year remaining term of the company’s lease.
This number is more than a mere calculation—it’s a significant business metric that reflects a company’s financial health and future lease strategy.
- Calculating the weighted average remaining lease term is essential for meeting the ASC 842 standard, which requires clear disclosure of lease commitments in financial reports. This ensures that everyone with a stake in the company has a clear understanding of its lease liabilities.
- The weighted average remaining lease term is calculated using a clear formula: multiply the remaining term by the remaining lease payments for each lease, sum these products, and then divide by the total of the remaining lease payments. This gives a single, weighted figure representing the average time span of a company’s lease commitments.
- The weighted average remaining lease term is a critical figure. It affects lease renewal strategies, financial planning, and risk assessment. If this term is short, like 1.32 years in our example, the company might need to think about renewing leases soon or changing its financial plans to account for possible shifts in leasing costs.