Forum Discussion
Amortization Table Calculations
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 an...
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 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:
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
Please let meknow if you need more details
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