Creating a lease accounting app using Power BI

[DataNinja777]

To meet accounting standards like ASC 842 and IFRS 16, which require treating monthly operating lease payments as finance leases by recording right-of-use (ROU) assets and lease liabilities, many companies turn to off-the-shelf or internally developed software solutions tailored for lease accounting. After observing various software solutions in action, I decided to simulate and replicate these calculations using Power BI. This approach allowed me to accurately generate the necessary accounting assets and liabilities from monthly payments, similar to those produced by specialized software.

Through this method, I discovered that Power BI offers distinct advantages over subscription-based third-party software solutions, particularly in terms of data updating and visualizations. Not only does Power BI provide robust capabilities for financial reporting at virtually no additional cost, but its flexibility and ease of use have proven superior in effectively managing lease accounting requirements.

For example, when using lease accounting software, I often have to input data through a web-based interface that is not very user-friendly. In many cases, the interface requires manual entry of information one item at a time, with data located in various places, necessitating numerous clicks to reach the correct input fields. Instead of allowing for a straightforward copy-and-paste or refreshing of all the necessary information at once, the process of data inputting becomes tedious and time-consuming 😩.

The data required to prepare lease accounting schedules, such as Right-of-Use (ROU) assets and the present value of minimum lease payments amortization schedules required by accounting standards, typically includes:

• Lease start date
• Lease end date
• Scheduled payments
• Lease duration (in months, which can be calculated from the start and end dates)
• Frequency of the scheduled payments (e.g., monthly, quarterly)
• Discount rate

(For this example, I’m assuming everything is monthly, so other payment frequencies are not considered.)

On the other hand, using Power BI eliminates the tedious process of manually inputting data into the system. Instead, you can prepare the information in advance in a format like the one below in Excel, summarizing the key details from each lease contract, and then simply refresh the data in the Power BI data model.

To generate lease amortization schedules for ROU assets and lease liabilities, you can create a calculated table like the one below:

LeaseTableWithMonths = 
    GENERATE (
        VALUES ( 'Data'[Lease #] ),
        VAR LeaseNumber = 'Data'[Lease #]
        VAR MaxMonths =
            MAXX ( FILTER ( 'Data', 'Data'[Lease #] = LeaseNumber ), 'Data'[Months] )
        VAR DiscountRate =
            VALUE (
                MINX ( FILTER ( 'Data', 'Data'[Lease #] = LeaseNumber ), 'Data'[Discount rate] )
            ) / 100
        VAR ScheduledPayment =
            MINX (
                FILTER ( 'Data', 'Data'[Lease #] = LeaseNumber ),
                'Data'[Scheduled payments USD]
            )
        VAR MaxCumulativeAmortization =
            SUMX (
                GENERATESERIES ( 1, MaxMonths, 1 ),
                ROUND (
                    ScheduledPayment / POWER ( 1 + DiscountRate / 12, MaxMonths - [Value] ),
                    0
                )
            )
        RETURN
        ADDCOLUMNS (
            GENERATESERIES ( 1, MaxMonths, 1 ),
            "Month", [Value],
            "Start Date", MINX ( FILTER ( 'Data', 'Data'[Lease #] = LeaseNumber ), 'Data'[Start date] ),
            "End Date", MINX ( FILTER ( 'Data', 'Data'[Lease #] = LeaseNumber ), 'Data'[End date] ),
            "Scheduled Payments USD", ScheduledPayment,
            "Asset Amortization",
            ROUND (
                ScheduledPayment / POWER ( 1 + DiscountRate / 12, MaxMonths - [Value] ),
                0
            ),
            "Accumulated Amortization",
            SUMX (
                GENERATESERIES ( 1, [Value], 1 ),
                ROUND (
                    ScheduledPayment / POWER ( 1 + DiscountRate / 12, MaxMonths - [Value] ),
                    0
                )
            ),
            "Accumulated Scheduled Payments",
            SUMX (
                GENERATESERIES ( 1, [Value], 1 ),
                ScheduledPayment
            ),
            "Closing Lease Liability",
            - (
                MaxCumulativeAmortization
                - SUMX (
                    GENERATESERIES ( 1, [Value], 1 ),
                    ROUND (
                        ScheduledPayment / POWER ( 1 + DiscountRate / 12, MaxMonths - [Value] ),
                        0
                    )
                )
            ),
            "Closing ROU Assets",
            MaxCumulativeAmortization
            - SUMX (
                GENERATESERIES ( 1, [Value], 1 ),
                ROUND (
                    ScheduledPayment / POWER ( 1 + DiscountRate / 12, MaxMonths - [Value] ),
                    0
                )
            ),
            "Lease Liability Interest Expense",
            - (
                ScheduledPayment
                - ROUND (
                    ScheduledPayment / POWER ( 1 + DiscountRate / 12, MaxMonths - [Value] ),
                    0
                )
            ),
            "Cumulative Lease Liability Interest Expense",
            SUMX (
                GENERATESERIES ( 1, [Value], 1 ),
                - (
                    ScheduledPayment
                    - ROUND (
                        ScheduledPayment / POWER ( 1 + DiscountRate / 12, MaxMonths - [Value] ),
                        0
                    )
                )
            ),
            "ROU Assets Interest Expense",
            ScheduledPayment
            - ROUND (
                ScheduledPayment / POWER ( 1 + DiscountRate / 12, MaxMonths - [Value] ),
                0
            ),
            "Cumulative ROU Assets Interest Expense",
            SUMX (
                GENERATESERIES ( 1, [Value], 1 ),
                ScheduledPayment
                - ROUND (
                    ScheduledPayment / POWER ( 1 + DiscountRate / 12, MaxMonths - [Value] ),
                    0
                )
            ),
            "Adjusted Date",
            EDATE (
                MINX ( FILTER ( 'Data', 'Data'[Lease #] = LeaseNumber ), 'Data'[Start date] ),
                [Value]
            ) - 1
        )
    )

The GENERATESERIES DAX function automagically converted a 10-row lease data table into a 468-row table, representing the monthly lease positions for each month within the duration of the ROU assets and lease liabilities.

The calculated table is automatically updated when the Data table with the following information is refreshed: lease start date, lease end date, scheduled payments, discount rates, and the number of months (calculated from the lease start and end dates). You can update this information by simply right-clicking and refreshing the original data source.

I am grateful to Microsoft for providing such a powerful and versatile tool, which has greatly enhanced our ability to seamlessly comply with complex accounting standards. With Power BI, we can also visualize the impact of lease accounting on ROU assets and lease liabilities as they appear on the balance sheet over time.

Visualization enables us to clearly see that the cumulative interest expense in a lease amortization schedule increases at a decreasing rate over time, resulting in a convex shape, as shown below. This pattern occurs because the principal balance, which is used to calculate the interest, decreases as the lease liability is reduced with monthly payments that significantly exceed interest expense accretion. This visual representation simplifies understanding of how the interest expense accumulates over time.

The advantage of Power BI over other software is that we, as accountant citizen developers, can create flexible visualizations tailored to our needs and curiosities.

In contrast, with traditional subscription-based software, enhancing a cumbersome data input interface or adding a new visualization often becomes a project in itself 😓, requiring extensive back-and-forth communication, meticulous preparation of requirement documents, reliance on intermediaries to convey specifications, and inevitable delays in delivery. However, these challenges are virtually eliminated with Power BI, which empowers citizen-developer accountants to rapidly iterate and implement changes in a fraction of the time 😀.

I have attached my lease accounting solution created using Power BI.

ASC842 & IFRS16 accounting.pbix