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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. 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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors