cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EddyW
Regular 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
Regular 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
Regular 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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors