I am trying to write a formula like the PMT formula in excel to calculate monthly payments on a fixed loan and fixed interest rate. below is the formula I input, but it keeps spitting out "0" as an answer, when in fact the answer should be $70.19. can anyone help me?
Round(('Loan Amount'.Text*(.209/12))/(1-(1+(.209/12)^(-60))),2)
Wh
It worked perfectly! TY so much!
@LipoLoHu - if there's nothing else, please remember to mark the solutions to your questions to close this thread.
You can apply a condition into the formula where the Annual Rate is 0. For example:
Round(
With(
{
AnnualRate: .209,
LoanAmount: 2600,
PeriodsinMonths: 60,
AnnualPayments: 12
},
With(
{
P: LoanAmount,
t: PeriodsinMonths
},
If(
AnnualRate = 0,
P / t,
(AnnualRate / AnnualPayments) * P / (1 - (1 + (AnnualRate / AnnualPayments))^-t)
)
)
),
2
)
Where interest is variable, for example 0% APR for the first 6 months and then 0.29%
thereafter, from the top of my head I suspect you will need a separate amortization table (generated via a Collection or a Table function) listing the rates for each period to run the calculation.
------------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.
If you like my response, please give it a Thumbs Up.
Imran-Ami Khan
@Amik that worked perfectly, TY!
Last question - I have a scenario where the annual APR rate is 0%, but when i input that in the formula, it states as an error that I cannot devise by 0. How would I correct the formula to give me the right output? (the example is - with a loan down payment of $2600, what is the monthly payment if interest rate was 0% for 6 months length)
@LipoLoHu wrap the result in a Text function. E.g.:
Text(
Round(
With(
{
AnnualRate: .209,
LoanAmount: 2600,
Months: 60,
AnnualPayments: 12
},
With(
{
r: AnnualRate / AnnualPayments,
P: LoanAmount
},
r * P / (1 - (1 + r)^-Months)
)
),
2
),
"[$-en-US]$#,###.00",
"en-US"
)
------------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.
If you like my response, please give it a Thumbs Up.
Imran-Ami Khan
@Amik that worked! How can I get the result to show up as currency?
@LipoLoHu - have you considered using the With function?
https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-with
Example below:
Round(
With(
{
AnnualRate: .209,
LoanAmount: 2600,
Months: 60,
AnnualPayments: 12
},
With(
{
r: AnnualRate / AnnualPayments,
P: LoanAmount
},
r * P / (1 - (1 + r)^-Months)
)
),
2
)
------------------------------------------------------------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.
If you like my response, please give it a Thumbs Up.
Imran-Ami Khan