Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
EddyW
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
ImkeF
Super User
Super User

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

EddyW
Frequent Visitor

Hello @ImkeF,

 

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

 

Best Regards,

Eddy W.

ImkeF
Super User
Super User

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

EddyW
Frequent Visitor

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.