In this how-to guide, we’ll cover how to calculate a **daily** lease liability amortization schedule. This method shown is one of many ways of how to calculate a lease amortization schedule. The key attributes of this lease liability daily amortization schedule are:

- The most accurate way of calculating the lease amortization schedule (by far)
- It uses the XNPV function in Excel
- The calculation is done daily
- A more time-intensive setup but can handle any lease permutation

If you want to know more about present value calculations and the different Excel functions available, refer to How to Calculate the Present Value of Future Lease Payments.

If you're looking for a quicker way to calculate your lease liability, refer to How to Calculate a Monthly Lease Liability Amortization Schedule in Excel. Between the two calculation methodologies, I'd recommend a daily lease liability amortization schedule. That's because it's not that much more of a time investment than the monthly NPV alternative. With the additional time investment, you have far the most accurate lease liability calculation, which allows you to handle any potential modification accounting.

**If you would like the Excel calculations displayed in this article, reach out to [email protected].**

The lease agreement we’re going to calculate is based on the following details:

- Commencement date: January 1, 2021
- Lease end date: December 31, 2021
- Discount rate: 7%
- Fixed payment amount: $10,000
- Payment timing: In Advance

With these inputs, we'll calculate the daily lease liability amortization schedule.

- Create six columns within the Excel worksheet. The columns will be called Date, Lease liability pre-payment, Payment, Lease liability post-payment, Interest, and Lease liability closing.

- As this is a daily schedule, this will encompass all individual days of the lease. For example, a 12-month lease will have 365 rows. If the calculation falls on a leap year, the calculation will have 366 rows.
- These inputs are taken from the lease agreement. In this example, there are 12 payments occurring on the first of each month for an amount of $10,000.

- Input the XNPV function into Excel. The function captures the discount rate, the payment amounts, and the dates of the payments.
- The XNPV function is the most accurate present value formula due to taking into consideration the payment timing.
- For more information on the XNPV function, refer here.

- This is the lease liability's value post-payment. In this case, the lease liability pre-payment is $116,357 less a $10,000 payment resulting in $106,357 lease liability balance post-payment.

- Given this is a daily calculation, it's necessary to convert the annual discount rate to a daily discount rate.

- Apply the daily discount rate to the post-payment lease liability balance to calculate the interest incurred for the day.
- This amount will then be added to the lease liability value.

- This is the closing balance for each applicable day.
- This balance will be brought forward as the opening balance for the next row in the Excel worksheet.

- Ensure your formulas are picking up the correct cells.
- If they are not, that's probably the reason why the lease liability amortization schedule is not unwinding to zero.

- The best check to ensure you have calculated the lease liability amortization schedule is that the lease liability goes to zero!

A daily lease amortization schedule is the most robust lease liability calculation available. As you can see, there are significantly more data points, but those data points give you flexibility when modification accounting occurs. For example, you know exactly what the closing balance is on March 25 as on March 26, the lessor communicated an increase in payments starting from April 1. As per the lease accounting standard, a remeasurement should occur on March 26 of the lease liability. Suppose you're using a monthly calculation, the only information available opening/closing balance of each month.

The lease liability amortization schedule is one of many pieces in the lease accounting puzzle. Here are some additional articles you might find helpful in broadening your understanding of lease accounting:

- How to Calculate the Present Value of Future Lease Payments This article will ensure you know how the present value formula you use in Excel can drastically impact your lease liability value.
- How to Calculate the Discount Rate Implicit in the Lease Once you're ready to start your present value calculations, you have one of the most highly judgemental inputs to determine with lease accounting - what discount rate to use. The standard says the implicit rate in the lease. This article will show you have to calculate it.
- How to Calculate the Lease Liability and Right-of-Use Asset for an Operating Lease under ASC 842 The lease liability is just one side of the balance sheet for both IFRS 16 & ASC 842. Here's an in-depth guide on calculating the lease liability and right of use asset and how modification accounting impacts these calculations.
- How to Calculate the Right-of-Use Asset and Lease Liability for a Finance Lease under ASC 842 There's a different calculation methodology under ASC 842 for the right of use asset classified as a finance lease.