Amortization Table Calculations

[chundrub]

Hi Team ,
I have an query related to Power BI DAX where the user need a Lease sales payments where there is an
Loan Amount=10500
interest rate=0.0076278 per Month
Deferral Period for 6 months
and intiallay the payment= 100 and increses every fiscai to 2.5 % per year 
before the deferral Period compund interest should be calculated.
here the net investment is calculted based on interest of the previous month and interest is generated based on previous month net value and rest of the principal amount after paying interest is added to the Net investment Value
 

Lease Number

Lease Description

Beginning Date

End Date

Lease Term (Months)

Asset Type

Asset In Service Date

Asset Economic Life (Months)

Asset Carrying Value

Asset Fair Value

Guaranteed Residual Value

Unguaranteed Residual Value

Initial Direct Costs

Lease Incentives

Lease Incentive Date

Payment Deferral Start Date

Payment Deferral End Date

Rent Escalation (%)

Escalation Frequency

Gross Rent (Lease Component Only)

Rent Start Date

Rent End Date

Frequency

1234567

HVAC#970000001136

10/1/2023

10/31/2038

180.00

HVAC

10/1/2023

180

9500

10000

0

0

0

500

10/1/2023

11/1/2023

4/28/2024

2.50%

Annually

$                                                                         100.00

5/1/2024

10/31/2038

Monthly

1234568

HVAC#970000001137

10/1/2024

10/31/2034

120.00

HVAC

10/1/2024

120

7500

9000

0

0

0

1500

10/1/2024

11/1/2024

4/28/2025

2.50%

Annually

$                                                                         100.00

5/1/2025

10/31/2034

Monthly

1234569

HVAC#970000001138

10/1/2023

10/31/2038

180.00

HVAC

10/1/2023

180

15000

10000

0

0

0

2000

10/1/2023

11/1/2023

11/1/2023

0.00%

Annually

$                                                                         100.00

10/1/2023

10/31/2038

Monthly

I am breaking my head for too long could not get it 
 

[v-yaningy-msft]

Hi, @chundrub 

Read your requirements very carefully, but unfortunately, still can't understand what you ultimately want to calculate. I have a limited knowledge of economics and can't make it clear in your description what the initial data is, it seems to be 10,500 but don't see you using it, can you give all the data involved in manipulating this data as an example in one row as in the screenshot you post to help understand your requirements better. As well, some Measure is missing. Thanks for your understanding.

Best Regards,
Yang
Community Support Team

[chundrub]

yes the initial amount is 10500 and the value I hardcoded it get the  correct when I'im passing for values manually 
took the sumx values as to get the calculated values in row wise

Lease Payment = SUMX(VALUES('Calendar'[Month-Year]),[LP Base])

Compound1sumx = SUMX(VALUES('Calendar'[Month-Year]),[Compound_base1])

Period Year =

VAR LeaseStartDate = SELECTEDVALUE('Lease Entry'[Beginning Date])

VAR CurrentDate = SELECTEDVALUE('Calendar'[Start of Month])

VAR FiscalYearStartMonth = 5 // May

// Adjust LeaseStartDate and CurrentDate to fiscal year basis

VAR LeaseYear = YEAR(LeaseStartDate)

VAR CurrentYear = YEAR(CurrentDate)

VAR CurrentMonth = MONTH(CurrentDate)

// Adjust the start date based on fiscal year

VAR AdjustedLeaseStart =

    IF(MONTH(LeaseStartDate) < FiscalYearStartMonth, LeaseYear - 1, LeaseYear)

// Adjust the current date based on fiscal year

VAR AdjustedCurrentYear =

    IF(CurrentMonth < FiscalYearStartMonth, CurrentYear - 1, CurrentYear)

// Calculate the fiscal period difference in months

VAR FiscalMonthsBetween =

    DATEDIFF(DATE(AdjustedLeaseStart, FiscalYearStartMonth, 1), DATE(AdjustedCurrentYear, FiscalYearStartMonth, 1), YEAR)

RETURN

FiscalMonthsBetween

In Net Investment Measure I have added Period year 
In measure above last paremeter is hardcoded beacuse when th add the value at the end only i can get correct value that year so it dynamically pick value from APR month of the fiscal year to calculate for rest of the year 

Interest 10787.40*0.76278=82.28    Principal redcution =100-82.28=20.22   next month value=10787.40-20.22=10767.18

unlees i pass the value 10787.40 th next year is not populating if  take the vale from net investment itself is giving me circular dependency concurrent issue

[chundrub]

 Thank you @Rupak_bi Here is the detailed eloboration of what I'm trying to acheive 
to calculate Net Investment In The Lease it should be balanced based of the payments made there will be no payment in the defferal period so the interest gets at 0.76278 per month added to principal value.If the payments starts for example in May-24 83.83 is interest fro prev month(10989.81) and 16.17(100-interest) is the principal amount need to subtracted(10989.81-16.17=10973.64(May-24)) for the next month() and interest to be calculated to the month..and Lease peyments increses every fiscsl year at 2.5% .
to claculate lease payments i have used the measure:

LP Base =

VAR MonthlyRent = SUMX('Lease Entry','Lease Entry'[Gross Rent (Lease Component Only)])

VAR LeaseIncentive = SUMX('Lease Entry','Lease Entry'[Lease Incentives])

VAR EscalationRate = 0.025

VAR StartDate = MIN('Lease Entry'[Beginning Date])

VAR PaymentDate = MIN('Calendar'[Start of Month])

VAR LeaseStartDate = SELECTEDVALUE('Lease Entry'[Beginning Date])

VAR DeferralEndDate = SELECTEDVALUE('Lease Entry'[Payment Deferral End Date])

-- Calculate the months between the lease start date and current month, and between the deferral end date and current month

VAR MonthsBetween = DATEDIFF(LeaseStartDate, SELECTEDVALUE('Calendar'[Start of Month]), MONTH)

VAR MonthsBetweenDeferralEnd = DATEDIFF(DeferralEndDate, SELECTEDVALUE('Calendar'[Start of Month]), MONTH)

-- Calculate the escalation factor based on the fiscal years that have passed

VAR FiscalYearStart = IF(MONTH(LeaseStartDate) >= 5, YEAR(StartDate), YEAR(StartDate) - 1)

VAR FiscalYearPayment = IF(MONTH(PaymentDate) >= 5, YEAR(PaymentDate), YEAR(PaymentDate)-1 )

VAR ElapsedFiscalYears = DATEDIFF(DATE(FiscalYearStart, 4, 1), DATE(FiscalYearPayment, 4, 1), YEAR)-1

VAR RentForCurrentYear = MonthlyRent * (1 + EscalationRate) ^ ElapsedFiscalYears

-- Calculate the lease payment for the current month

VAR CurrentMonthPayment =

    IF(

        MONTH(PaymentDate) = MONTH(StartDate) && YEAR(PaymentDate) = YEAR(StartDate),

        MonthlyRent - LeaseIncentive,  -- First month payment with incentive

        RentForCurrentYear  -- Escalated rent for other months

    )

RETURN

IF(    MonthsBetween = 0, -LeaseIncentive,    IF(MonthsBetweenDeferralEnd <= 0, 0, CurrentMonthPayment))

Calendar Table:

Calendar =

var a=ADDCOLUMNS(CALENDAR(DATE(2020,1,1),DATE(2050,1,1)),

  "Datekey",FORMAT( DATE(YEAR([Date]), MONTH([Date]), 1), "YYYYMMDD"),  

    "Year", YEAR([Date]),

    "Month", MONTH([Date]),

    "Quarter", QUARTER([Date]),

    "Month Name", FORMAT([Date], "MMMM"),  

     "Month-Year", FORMAT([Date], "MMM-YY"),    

    "Start of Month", DATE(YEAR([Date]), MONTH([Date]), 1),

"Month Year",FORMAT([Date],"mmm-yy"))

RETURN

SUMMARIZE(a,[Datekey],[Month Name],[Month-Year],[Month],[Quarter],[Start of Month],[Year])


  Compounding measure for every month:

Compound_base1 =

VAR MonthlyRent = SUMX('Lease Entry', 'Lease Entry'[Asset Fair Value] + 'Lease Entry'[Lease Incentives])

VAR BaseEscalationRate = 0.0076278

VAR StartDate = MIN('Lease Entry'[Beginning Date])

VAR PaymentDate = MIN('Lease Entry'[End Date])

VAR FiscalYearPayment = [Period]

-- Calculate the compounded escalation rate for the year

-- Calculate the yearly rent with the compounded escalation rate

VAR RentForCurrentYear = MonthlyRent * (1 + BaseEscalationRate) ^ (FiscalYearPayment)

-- Determine the payment for the current month

VAR CurrentMonthPayment =

    IF(

        MONTH(PaymentDate) = MONTH(StartDate) && YEAR(PaymentDate) = YEAR(StartDate),

        MonthlyRent,  -- First month payment with incentive

        RentForCurrentYear  -- Escalated rent for other months

    )

RETURN

CurrentMonthPayment

Net Investment =

if([Lease Payment]<=0,[Compound1sumx],(FV(0.0076278, [Period]-6, [Lease Payment], -10989.81)))

where Period value and amount value in Net Investment measure passing amount value hardcoded as it is taking same value till the end so the value should chnage every year like for next year

Net Investment for next fiscal year=

if([Lease Payment]<=0,[Compound1sumx],(FV(0.0076278, [Period]-18, [Lease Payment], -10787.40)))

Please let meknow if you need more details

[v-yaningy-msft]

Hi, @chundrub 

Thanks for Rupak_bi's reply. As he said, it is not possible to specify the effect you are currently trying to achieve, if you can give the appropriate logical calculations to expect the effect it will help you solve the problem faster. Feel free to help you.

Best Regards,
Yang
Community Support Team

[Rupak_bi]

Hi @chundrub ,

Your desired result is not clear here in your problem statement. Please elaborate. also share the logic you like to implement to get the result along with the apporach you have taken so far. this will help to understand the issue in a better way and to identify the best approach to solve.