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

[EddyW]

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/2... , 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.

[ImkeF]

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

[EddyW]

Hello @ImkeF,

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

Best Regards,

Eddy W.

[ImkeF · Accepted solution]

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.

[EddyW]

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.