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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors