cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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.

The only information i can give is :

• Loan Tenure or Period of Loan
• Loan Amount
• CAC

Warm Regards,

Eddy W.

1 ACCEPTED SOLUTION
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``````

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!

4 REPLIES 4
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!

Frequent Visitor

Hello @ImkeF,

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

Best Regards,

Eddy W.

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``````

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!

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.

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors