Calculating Present Value of Lease Payments

Leasing is a common practice for businesses of all sizes, offering flexibility and financial advantages. However, to accurately account for leases and comply with accounting standards like ASC 842, calculating the Present Value of Lease Payments (PV) is essential. While Excel is a commonly used tool for this task, there are better technologies to ensure compliance. In this article, we’ll walk you through the steps to calculate the Present Value of Lease Payments in Excel and highlight the importance of accuracy in lease calculations. We’ll also explore why switching to an established provider is a smart move for lease accounting.

How to Calculate the Present Value of Lease Payments in Excel

Excel is a versatile tool for various financial calculations, including determining the present value of lease payments. Follow these steps to perform the calculation:

Step 1: Organize Data

Before diving into calculations, ensure that you have all the necessary lease data organized. This should include:

• Interest rate per period (rate)
• Total number of payment periods (nper)
• Payment amount for each period (pmt)

Having this information at your fingertips will make the calculation process much smoother.

Step 2: Use the PV Function

In an empty cell, use the Excel formula for calculating the present value. The formula typically used is:

=PV(rate, nper, pmt)

• Rate: Enter the interest rate per period. Ensure that the rate is consistent with the payment frequency (e.g., annual rate for annual payments).
• NPER: Input the total number of payment periods over the lease term.
• PMT: Enter the payment amount for each period. Make sure to include any relevant negative sign (for outflows).

After inputting these values, Excel will calculate the present value of lease payments, which represents the total value of future lease payments in today’s dollars.

Step 3: Repeat as Needed

If you have multiple lease agreements or different payment schedules, you can repeat the above steps for each lease to calculate their respective present values.

Cons of Using Excel: Changes in Lease Payment Schedule

The PV function in Excel is easy to use, but it is very limited in function. It cannot accommodate changes in the payment schedule during the lease term. That is why most users will utilize the NPV function instead. While it accommodates changes, each payment must be entered individually, even if the payments are unchanged, as well as periods where the payment amount is zero.

Both PV and NPV only deal with full periods (usually based on a month, although other periods can be selected).  Sometimes, though, a partial period is required in the calculation of NPV, for example when the payment is not at the beginning or end of the calendar month. That can be done with Excel, but this requires creating a complex model.  Also, the payment methodology (beginning or end of period, see below) is important for making PV work with lease accounting schedules. This is an extra step in the PV or NPV functions, one not often used. This can create errors which are difficult to reconcile.

The Importance of Lease Calculations in Maintaining Compliance

Accurate lease calculations are crucial for several reasons, ranging from financial transparency and regulatory compliance to effective decision-making and risk management. Here are some of the most common reasons why having accurate lease calculations is important:

• Financial Transparency: Accurate calculations ensure that your financial statements accurately represent your organization’s financial position, helping stakeholders make informed decisions.
• Compliance: Regulatory standards like ASC 842 demand accuracy in lease calculations. Errors can lead to compliance violations and regulatory penalties.
• Audit Preparedness: Accurate calculations make audit processes smoother, reducing the risk of audit issues and delays.
• Budgeting and Planning: Precise lease calculations aid in budgeting and financial planning, helping organizations allocate resources effectively.
• Contract Negotiations: Accurate calculations provide a strong foundation for lease negotiations, allowing organizations to make informed decisions about lease terms.

Each of the lease accounting standards (ASC 842, IFRS 16, GASB 87) specifies methodology for calculating interest, straight-line rent, ROU Asset amortization, and Liability reduction.  If the present value calculation does not perfectly align with the schedule, the ROU Asset and Liability will not amortize to zero at the end of the lease term.  This is a red flag for auditors.

Calculating the Present Value of Lease Payments with Visual Lease Accounting Software

While Excel is a useful tool, it has limitations, and managing complex lease portfolios can be challenging. That’s where Visual Lease software comes in.

Visual Lease can calculate the Net Present Value of a lease accounting schedule in one of two ways. The following describes these ways in terms of the Excel function NPV, and B1 through BN are the individual payments:

• Beginning of the Period: This method deducts the payment amount from the principal, then calculates the periodic interest. The formula is NPV(Annual Rate/12, B2:Bn) + B1 .
• End of the Period: This method calculates the periodic interest, then deducts the payment amount from the principal. The formula is NPV(Annual Rate, B1:Bn).

If the initial period is partial (begins any day other than first of a month), the methods are as follows, referencing the Excel function PV. The platform creates individual present values for each period i as PV(Annual Rate/12,Period,,-Bi) where Bi is the payment for the ith period, and sums the values for the initial liability value, according to one of these methods:

• Beginning of the Period: This method uses the full face value of the first payment. It then discounts the second payment by the fractional initial month. For each subsequent payment, it increases each subsequent period by adding 1 to the prior period value.
• End of the Period: This method discounts the first payment by the fractional initial month. For each subsequent payment, it increases each subsequent period by adding 1 to the prior period value.

The Net Present Value of payments affects the Right of Use Asset Starting Balance, Total Ending Liability Starting Balance, and Interest for all schedules affecting the balance sheet.

Users can easily select their preference in the Net Present Value Calculation Method drop-down within VL. This can

Visual Lease simplifies the process of calculating PV and offers numerous advantages:

• Automation: Visual Lease automates lease calculations, reducing the risk of errors and saving you time.
• Accuracy: With a dedicated platform for lease accounting, Visual Lease ensures accuracy in all calculations, helping you stay compliant with accounting standards.
• Comprehensive Reporting: Generate detailed and customizable reports for better insights into your lease portfolio.
• Centralized Document Management: Store and manage all lease documents in one secure location.
• Audit Trail: Visual Lease maintains an audit trail, making it easier to track changes and ensure transparency.
• Compliance: Visual Lease is designed to keep you compliant with the latest accounting standards, reducing the risk of regulatory issues.

Improving your Lease Management Process

Calculating the present value of lease payments is a critical aspect of lease accounting. While Excel can handle these calculations, it comes with limitations and potential risks. Transitioning to Visual Lease software not only simplifies the process but also offers enhanced automation, accuracy, compliance, and reporting capabilities. If you’re serious about lease accounting, Visual Lease is the smart choice to ensure accuracy and efficiency in your lease management processes.

Making the Switch from Excel to Visual Lease Software

When Excel can’t keep up with multiple leases and running reports is an extremely manual process, it’s time to consider a better option. Switching from Excel to Visual Lease is a straightforward process. Visual Lease’s proven migration methodology ensures completeness, consistency and sustainable workflows.

Visual Lease offers an easy transition, and like our customers, your organization can quickly realize the benefits of using specialized lease management software:

• Efficiency: Visual Lease streamlines lease management, saving you time and reducing manual data entry.
• Accuracy: With automated calculations, you can trust that your lease data is accurate.
• Compliance: Visual Lease ensures compliance with ASC 842 and other accounting standards.
• Advanced Reporting: Access advanced reporting and analytics to gain valuable insights into your lease portfolio.
• Document Management: Store and organize all lease-related documents within the platform.
• Audit Readiness: Visual Lease prepares you for audits with accurate and well-documented lease data.

Switch to Visual Lease today to experience the difference.