Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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 :
Thank you in advance.
Warm Regards,
Eddy W.
Solved! Go to Solution.
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
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
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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
20 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
22 |