topic Re: Goal Seeking the Rate of CAC Without Knowing the PMT and Rate in DAX Commands and Tips

Goal Seeking the Rate of CAC Without Knowing the PMT and Rate

EddyW — Sun, 04 Dec 2022 05:12:27 GMT

Hello Everyone and ImkeF,

Regarding to this topic https://community.powerbi.com/t5/Power-Query/Goal-Seek-to-Each-Row-for-Effective-Interest-Rate/m-p/2945901#M93044 , I would like to continue the case:

After the management know about the percentage of total cost of borrowing, the management of Company A wanted to know what is the equivalent Effective Interest Rate (EIR) of Cost of Acquistion Cost (CAC). FYI, Company A is engaged in fin-tech business. By knowing this, the company A might be aware and they will consider in determine the selling EIR to the consumer.

I can calculate on the case i have described by using Excel with goalseek property VBA (Attached). However, i don't know if it's possible to be done in PowerBI by only using RATE() Function. The reason is because i don't know the exact of PMT that is fulfill the %CAC.

Example EIR - Sent.xlsx

The only information i can give is :

Loan Tenure or Period of Loan
Loan Amount
CAC

Thank you in advance.

Warm Regards,

Eddy W.

Re: Goal Seeking the Rate of CAC Without Knowing the PMT and Rate

ImkeF — Sun, 04 Dec 2022 08:04:11 GMT

Hello EddyW ,
yes, now we need a recursive iteration. The M-code could look like so:

// fnEIRofCUMIPMT // fnEIRofCUMIPMT - returns annual effective interest rate, given total interest costs, PV and number of payment months // Author: Imke Feldmann (www.TheBIccountant.com) let Source = (CAC as number, Months as number, LoanAmount as number, optional Lowest_, optional Highest_, optional Precision_) => let Lowest = if Lowest_ = null then 0 else Lowest_, Highest = if Highest_ = null then 10000000000 else Highest_, Precision = if Precision_ = null then 0.0001 else Precision_, /* Debug parameters Lowest = 0.001, Highest = 0.1, Precision = 0.0001, */ Result= List.Generate( ()=> [Lowest=Lowest, Highest=Highest, Result=1, Counter=0, Value=Highest, DiffToPrevious=0.1], // Conditions determining if the next execution of the loop shall be performed each Number.Abs([Result])>=Precision and [Counter]<1000 and [DiffToPrevious]<>0, // loop command each [ // Binary-search procedure Lowest = if [Result]<0 then [Value] else [Lowest], Highest= if [Result]>0 then [Value] else [Highest], Value= (Lowest+ Highest)/2, // Helper function fnCUMIPMT_full = (Rate as number, nPer as number, Pv as number, Type as number) => let C = Number.Power((1+Rate),nPer), Standard = (Pv* C) * - Rate / (C-1), PMT = Standard / (1+Rate*Type), _CUMIPMT_full = PMT * nPer + Pv in _CUMIPMT_full, // Goal (formulated in a way that it should be null): Result= - CAC - fnCUMIPMT_full(Value, Months, LoanAmount, 0) , // Check if the result is still improving/changing DiffToPrevious = Result-[Result], Counter=[Counter]+1 ] ), Custom1 = List.Last(Result), AnnualRate = Custom1[Value]*12 in AnnualRate in Source

Please also check file enclosed.

Re: Goal Seeking the Rate of CAC Without Knowing the PMT and Rate

EddyW — Sun, 04 Dec 2022 09:20:21 GMT

Hello ImkeF

Thank you so much for the M-Code. It really helps me a lot! But i have a problem here. When i tried to put this M-Code into PowerBI, it said that i have to "switch all tables to Import Mode" (FYI, i'm in DirectQuery mode). Is it possible to get this M-Code running under DirectQuery Mode?

Best Regards,

Eddy W.

Re: Goal Seeking the Rate of CAC Without Knowing the PMT and Rate

ImkeF — Sun, 04 Dec 2022 09:56:47 GMT

Hi EddyW ,
Sorry, but I don't think so, as this is true recursion.
By choosing Direct Query Mode you are limiting yourself to only basic transformations: Using DirectQuery in Power BI - Power BI | Microsoft Learn

Re: Goal Seeking the Rate of CAC Without Knowing the PMT and Rate

EddyW — Sun, 04 Dec 2022 14:23:56 GMT

Hello ImkeF,

Noted that. Your information is clear and concise. Thank you 😀👍

Best Regards,

Eddy W.