Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
07-19-2020 05:30 AM - last edited 07-23-2020 06:39 AM
In July 2020, Power BI has released 49 financial functions.
I have posted how to use PMT.
https://community.powerbi.com/t5/Quick-Measures-Gallery/PMT-Monthly-EMI-Calculator/td-p/1232245
But I know everyone wants to see EMI Details, Principal paid, outstanding Principal, and month-wise details of EMI.
It needs more functions. So I covered those functions along with few others in the 10 functions here.
PMT,PV,FV,Rate,NPER
PPMT,IPMT,CUMIPMT,CUMPRINC,RRI
Formulas
CUMIPMT = -1*CUMIPMT([Rate Per Month],[Months],[Principal],1,max('Duration'[No Months]),0)
CUMPRINC = -1*CUMPRINC([Rate Per Month],[Months],[Principal],1,max('Duration'[No Months]),0)
EMI = maxx(ALLSELECTED(EMI),EMI[EMI Amount])
FV = -FV([Rate Per Month],[Months],[Principal],0,1)
IPMT = -1*IPMT([Rate Per Month],1,[Months],[Principal],1)
IPMT Month = -1*IPMT([Rate Per Month],Max('Duration'[No Months]),[Months],[Principal],1)
Monthly EMI = -PMT([Rate Per Month],[Months],[Principal])
NPER = NPER([Rate Per Month],[EMI],-1*[Principal])
PPMT = -1*PPMT([Rate Per Month],1,[Months],[Principal],1)
PPMT Month = -1*PPMT([Rate Per Month],Max('Duration'[No Months]),[Months],[Principal],1)
PV = -PV([Rate Per Month],[Months],[Principal])
Rate = RATE([Months],-1*[EMI],[Principal])*12
Remaining Principal = [Principal]-[CUMPRINC]
RRI = RRI([Months],[Principal],[EMI])*12
More Details about these functions :
https://docs.microsoft.com/en-us/dax/financial-functions-dax
Few of them help in EMI Details. Other are common financial functions.
In Power BI, we do not have input boxes and What-if Parameter is limited by max value. I have created a few series, to get this done. This means you can give the principal only in the multiple of 100. And FV value limited is not so high. It is the same EMI max limit.
eyJrIjoiZTBmNjI1OWQtZDYxMC00MmY0LWIxMjEtNWMzYTNjMDNlMzVkIiwidCI6ImVhOGJkMWZkLWFjMzQtNGFlMi1iNDIxLTZjZmEyZmNmZjI0MyJ9&pageName=ReportSection9cdabd0a40b5d84c3f4c