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
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
5 Replies
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.
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 TeamThank 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.025VAR 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 monthVAR 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 passedVAR 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)-1VAR RentForCurrentYear = MonthlyRent * (1 + EscalationRate) ^ ElapsedFiscalYears-- Calculate the lease payment for the current monthVAR CurrentMonthPayment =IF(MONTH(PaymentDate) = MONTH(StartDate) && YEAR(PaymentDate) = YEAR(StartDate),MonthlyRent - LeaseIncentive, -- First month payment with incentiveRentForCurrentYear -- Escalated rent for other months)RETURNIF( 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"))RETURNSUMMARIZE(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.0076278VAR 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 rateVAR RentForCurrentYear = MonthlyRent * (1 + BaseEscalationRate) ^ (FiscalYearPayment)-- Determine the payment for the current monthVAR CurrentMonthPayment =IF(MONTH(PaymentDate) = MONTH(StartDate) && YEAR(PaymentDate) = YEAR(StartDate),MonthlyRent, -- First month payment with incentiveRentForCurrentYear -- Escalated rent for other months)RETURNCurrentMonthPaymentNet 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 yearNet 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 detailsHi, 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 Teamyes 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 wiseLease 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 basisVAR LeaseYear = YEAR(LeaseStartDate)VAR CurrentYear = YEAR(CurrentDate)VAR CurrentMonth = MONTH(CurrentDate)// Adjust the start date based on fiscal yearVAR AdjustedLeaseStart =IF(MONTH(LeaseStartDate) < FiscalYearStartMonth, LeaseYear - 1, LeaseYear)// Adjust the current date based on fiscal yearVAR AdjustedCurrentYear =IF(CurrentMonth < FiscalYearStartMonth, CurrentYear - 1, CurrentYear)// Calculate the fiscal period difference in monthsVAR FiscalMonthsBetween =DATEDIFF(DATE(AdjustedLeaseStart, FiscalYearStartMonth, 1), DATE(AdjustedCurrentYear, FiscalYearStartMonth, 1), YEAR)RETURNFiscalMonthsBetween
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 yearInterest 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
