Creating a lease accounting app using Power BI

Hi HaithamSabra ,

I can certainly help you with that. Modifying the Power BI solution to handle different payment frequencies like yearly and quarterly is a great way to make it more versatile. First, you'll need to add a new column to your source data in Excel to specify the payment frequency for each lease. Let's call this column "Frequency". You can then enter "Monthly", "Quarterly", or "Yearly" for each lease to prepare the data for the updated DAX formula.

Next, you can replace the original DAX formula for the calculated table with the modified version below. This new formula is designed to dynamically adjust the calculations based on the frequency you've specified for each lease, making your model much more flexible.

LeaseTableWithMonths = 
GENERATE (
    VALUES ( 'Data'[Lease #] ),
    VAR LeaseNumber = 'Data'[Lease #]
    VAR LeaseData = FILTER ( 'Data', 'Data'[Lease #] = LeaseNumber )
    VAR Frequency = MINX ( LeaseData, 'Data'[Frequency] )
    VAR StartDate = MINX ( LeaseData, 'Data'[Start date] )
    VAR EndDate = MINX ( LeaseData, 'Data'[End date] )
    VAR ScheduledPayment = MINX ( LeaseData, 'Data'[Scheduled payments USD] )
    VAR DiscountRate = VALUE ( MINX ( LeaseData, 'Data'[Discount rate] ) ) / 100

    VAR PeriodsInYear =
        SWITCH (
            TRUE (),
            Frequency = "Quarterly", 4,
            Frequency = "Yearly", 1,
            12 // Default to Monthly
        )

    VAR TotalPeriods = 
        SWITCH(
            TRUE(),
            Frequency = "Quarterly", (DATEDIFF(StartDate, EndDate, MONTH) + 1) / 3,
            Frequency = "Yearly", DATEDIFF(StartDate, EndDate, YEAR) + 1,
            DATEDIFF(StartDate, EndDate, MONTH) + 1 // Default to Monthly
        )
        
    VAR AdjustedRate = DiscountRate / PeriodsInYear

    VAR MaxCumulativeAmortization =
        SUMX (
            GENERATESERIES ( 1, TotalPeriods, 1 ),
            ROUND ( ScheduledPayment / POWER ( 1 + AdjustedRate, TotalPeriods - [Value] ), 0 )
        )

    RETURN
        ADDCOLUMNS (
            GENERATESERIES ( 1, TotalPeriods, 1 ),
            "Period", [Value],
            "Start Date", StartDate,
            "End Date", EndDate,
            "Scheduled Payments USD", ScheduledPayment,
            "Asset Amortization", ROUND ( ScheduledPayment / POWER ( 1 + AdjustedRate, TotalPeriods - [Value] ), 0 ),
            "Accumulated Amortization", SUMX ( GENERATESERIES ( 1, [Value], 1 ), ROUND ( ScheduledPayment / POWER ( 1 + AdjustedRate, TotalPeriods - [Value] ), 0 ) ),
            "Accumulated Scheduled Payments", SUMX ( GENERATESERIES ( 1, [Value], 1 ), ScheduledPayment ),
            "Closing Lease Liability", -( MaxCumulativeAmortization - SUMX ( GENERATESERIES ( 1, [Value], 1 ), ROUND ( ScheduledPayment / POWER ( 1 + AdjustedRate, TotalPeriods - [Value] ), 0 ) ) ),
            "Closing ROU Assets", MaxCumulativeAmortization - SUMX ( GENERATESERIES ( 1, [Value], 1 ), ROUND ( ScheduledPayment / POWER ( 1 + AdjustedRate, TotalPeriods - [Value] ), 0 ) ),
            "Lease Liability Interest Expense", -( ScheduledPayment - ROUND ( ScheduledPayment / POWER ( 1 + AdjustedRate, TotalPeriods - [Value] ), 0 ) ),
            "Cumulative Lease Liability Interest Expense", SUMX ( GENERATESERIES ( 1, [Value], 1 ), -( ScheduledPayment - ROUND ( ScheduledPayment / POWER ( 1 + AdjustedRate, TotalPeriods - [Value] ), 0 ) ) ),
            "ROU Assets Interest Expense", ScheduledPayment - ROUND ( ScheduledPayment / POWER ( 1 + AdjustedRate, TotalPeriods - [Value] ), 0 ),
            "Cumulative ROU Assets Interest Expense", SUMX ( GENERATESERIES ( 1, [Value], 1 ), ScheduledPayment - ROUND ( ScheduledPayment / POWER ( 1 + AdjustedRate, TotalPeriods - [Value] ), 0 ) ),
            "Adjusted Date", 
                VAR MonthIncrement = SWITCH(TRUE(), Frequency = "Quarterly", 3, Frequency = "Yearly", 12, 1)
                RETURN EDATE(StartDate, ([Value] - 1) * MonthIncrement)
        )
)

The key changes in the formula are a series of new variables that make it dynamic. A Frequency variable gets the payment frequency from your data. This is used by the PeriodsInYear variable, which uses a SWITCH function to determine if there are 1, 4, or 12 payment periods per year. The TotalPeriods variable then calculates the total number of payments over the lease term. The discount rate is then correctly calculated for each period by the AdjustedRate variable. Finally, the Adjusted Date logic uses the EDATE function to correctly increment the date for each payment period, whether it be by 1, 3, or 12 months.

By making these changes, your lease accounting app will now correctly calculate the amortization schedules for leases with different payment frequencies. Let me know if you have any other questions!

Best regards,