Calculate the loan amount from a monthly payment

[fisicx]

If you know how much you can afford to pay each month how do you calculate how much you can borrow?

There are lots of online calculators that will tell you how much you can borrow but I need the formula.

I've got an idea it's compound interest but nothing I've tried comes close to the various online results.

 

[Mark T Jones]

I don't know the answer, I just use industry-standard HP12c

 

[fisicx]

Loads of tools I can use. But this is for a client who wants a loan calculator based on a monthly payment. Nobody seems to know how it’s done. Not even Google.

 

[pentel]

It will depend on lots of things for example how the interest accrues, if there are fees and how they are accounted for amongst others.

 

[fisicx]

Just need a very basic formula to calculate how much someone can borrow. Once I’ve got that I can incorporate fees and other parameters.

 

[IanSuth]

A = P(1 + r/n)^nt, where A is total repaid, P is the principal balance, r is the interest rate, n is the number of times you do the interest per time period (so 12 for monthly compounding with t the number of years)

You need to rearrange that formulae

Or maybe this rearranged

Monthly Payment = Total Loan / {[(1 + r)^n]-1} / [ r (1+r) ^n] where r is periodic interest rate and n is number of periods

Source - https://www.thebalancemoney.com/loan-payment-calculations-315564

 

[brucegreig]

The PMT formula in Google Sheets or Excel can help here:

=PMT(rate,term,amount-borrowed)

Note: rate and term need to match up. If you express term in months, you need to express the interest rate per month too (so divide the annual rate by 12).

For example:

=PMT(0.05/12,36,50000) would show you the monthly repayment for a 50k loan over 36 months with an *annual* interest rate of 5%.

This calculates the monthly payment, rather than the maximum you can borrow. But obviously you can fiddle with the amount-to-borrow and the term to get the monthly payment where you need it to be.

 

[DontAsk]

Note: rate and term need to match up. If you express term in months, you need to express the interest rate per month too (so divide the annual rate by 12).

Simply dividing the annual rate by the number of periods gives the wrong result if interest is compounded every period.

E.g. 10%/12 compounded monthly would result in something nearer 10.5% over a year.

 

[fisicx]

The PMT formula in Google Sheets or Excel can help here

Doesn't help at all. That's just the standard amortisation formula and calculates the monthly payment.

I need to be able to enter the monthly payment and get the loan amount. And the PMT function uses nominal APR. In the UK monthly interest is calculated using Effective APR.

The good news is I have found out how to calculate the loan amount:

https://www.sapling.com/4914522/calculate-year-fixed-mortgage

So we are all good now

 

[brucegreig]

Doesn't help at all. That's just the standard amortisation formula and calculates the monthly payment.

I need to be able to enter the monthly payment and get the loan amount. And the PMT function uses nominal APR. In the UK monthly interest is calculated using Effective APR.

The good news is I have found out how to calculate the loan amount:

[link]

So we are all good now

Seems a bit harsh to say it doesn't help *at all*. I did point out that it calculates the monthly payment from the amount, rather than the other way around.

As a simple way of getting a reasonably accurate answer through trial and error (i.e. popping in different amounts and rates and seeing what monthly amounts come out from the formula), some people might find the PMT formula easier than doing the exponent calculation shown in Ian's answer and that Sapling link.

Feels to me like it might help at least *a little bit*.

 

[fisicx]

Unfortunately the formula needs to be dynamic. It’s for a Wordpress plugin where someone enters a monthly amount, the JavaScript does its thing a spits out a loan value. Trial and error isn’t going to work.

This is the result:

Borrowing Calculator | Calculator Variants

A little bit different this one. Instead of calculating the monthly payment you enter how much you can afford and it calculates how much you…

variants.loanpaymentplugin.com

 

[brucegreig]

Unfortunately the formula needs to be dynamic. It’s for a Wordpress plugin where someone enters a monthly amount, the JavaScript does its thing a spits out a loan value. Trial and error isn’t going to work.

This is the result:

Ah, I see. Right, that makes much more sense. The PMT formula is indeed of zero help for you!

 

[IanSuth]

When he is back from Cote D'Ivoire I will ask my maths graduate son to give me a straight answer as I am sure he knows a way

 

[UkAppCoder]

The following is working correctly for me in one of my calculator apps:

A = P( ( i( 1 + i )^n ) / ( (1 + i)^n - 1 ) )

Where:

A: The amount to be repaid at regular intervals.
P: The principal amount owing (i.e. the amount borrowed).
i: The annual interest rate. Must be divided by 12 because interest is added to the balance at the end of each month before the payment is deducted from the balance.
n: The number of repayments.

In Java:

output = principal * ( ( interestRate * Math.pow( 1.0f + interestRate, numberOfRepayments ) ) / ( ( Math.pow(1.0f + interestRate, numberOfRepayments ) - 1.0f ) ) );

The formula above calculates the periodic repayment. But since you want P at the beginning of the term, then:

P = A / ( ( i( 1 + i )^n ) / ( (1 + i)^n - 1 ) )

Hope this helps. But it's late now, and I've had to delve down into my archives without revision. If it doesn't help, then please just give me a shout. I've been over this a few times over the last couple of decades, and so I can provide all you need and more. Can also provide the for-loop formula defined from first principles on my own real-world modeling.

 

[fisicx]

I used the formula @IanSuth provided and the one I found online so I’m all sorted thanks.

Also your formula uses Nominal APR. I need to use Effective APR. I already had the formula for this.

 

[UkAppCoder]

Also your formula uses Nominal APR. I need to use Effective APR. I already had the formula for this.

Unless I'm missing something important in your requirements here, I think you're making a mistake. As far as I understand it, you should use the headline interest rate. The effective interest rate is the actual interest after compounding. It is something you might add to your advertising to show customers that you are being up-front because you wouldn't want customers thinking you're inaccurate or being dishonest.

Thinking about it, if you were to use the effective interest rate in your formula, and then calculate the effective interest rate on that, then you would end up with another higher effective interest rate!

It's been a couple of years since I played around with this, but I remember making sure that my formula produced results which concur with the following (to the exact penny (2 decimal places)):

Loan Calculator

Free loan calculator to find the repayment plan, interest cost, and amortization schedule of conventional amortized loans, deferred payment loans, and bonds.

www.calculator.net

I did not compare my results with those of banks, financial organisations or any commercial organisation such as moneysavingexpert, one reason being because they don't even concur with each other!

The maths is A Level standard. I remember going back to my Core Maths by Bostock & Chandler for A Level to derive my formula algebraically from first principles (real-world modelling).

I am not a financial expert, so like I said, unless I'm missing something important here, I would compare your results with those of mathematicians such as those at the site I posted above.

 

[fisicx]

It’s nothing

Unless I'm missing something important in your requirements here, I think you're making a mistake. As far as I understand it, you should use the headline interest rate. The effective interest rate is the actual interest after compounding.

When you start with an annual interest rate you need to work out the monthly rate so you can then apply the amortization formula (which is the one use throughout the world).

There are two way to do this and the results are slightly different:

Converting APR to monthly repayments | Loan Payment Plugin

According to Wikipedia: The term annual percentage rate of charge (APR), corresponding sometimes to a nominal APR and sometimes to an effective APR (or EAPR),…

loanpaymentplugin.com

The one you use depends on the banking system used in your country. In the UK the banks use EAPR. Most of the online calculators just divide the annual rate by 12. This is why the tools have minor differences in the calculated repayment.

 

[UkAppCoder]

It’s nothing

When you start with an annual interest rate you need to work out the monthly rate so you can then apply the amortization formula (which is the one use throughout the world).

There are two way to do this and the results are slightly different:

Converting APR to monthly repayments | Loan Payment Plugin

According to Wikipedia: The term annual percentage rate of charge (APR), corresponding sometimes to a nominal APR and sometimes to an effective APR (or EAPR),…

loanpaymentplugin.com

The one you use depends on the banking system used in your country. In the UK the banks use EAPR. Most of the online calculators just divide the annual rate by 12. This is why the tools have minor differences in the calculated repayment.

I think you're misunderstanding what they're saying, fisicx. I can't find a single reference to your method of using the EAPR in the amortization formula. (To be honest, I felt a bit daft even looking for it! At one stage I thought you were just having me over! LOL! ) Anyway, even Step 1 in your own reference at www.sapling.com divides the interest rate by 12 to use in their calculation.

I suspect the reason why you can't find anyone who knows how to do this is because nobody does it!

Have you been able to produce results which concur with other reliable sources yet?

I'll post a more in-depth write-up over the weekend. In the meantime, I'll be sticking with my mathematically consistent method!

 

[fisicx]

It's not in the amortization formula. That's why you can't find it.

The sapling example uses nominal APR (dividing by 12) because it's a US site.

It's all explained here:

Annual percentage rate - Wikipedia

en.wikipedia.org

I only know all this because I've been building financial calculators for years and the way of calculating the monthly interest rate has come up many times in response to client concerns.

 

[Talay]

Looked at that result and it is way wrong !

Borrowing Calculator | Calculator Variants

A little bit different this one. Instead of calculating the monthly payment you enter how much you can afford and it calculates how much you…

variants.loanpaymentplugin.com

The downpayment, which should be added to the budget, is factoring into the budget formula so that with a $500 payment and $0 downpayment, the budget is $15383

But put down $2000 and the budget goes wonky to $76917 and if you put down $10000 downpayment, the budget goes off scale to $323052

 

[fisicx]

Yes, client spotted that as well!

Fixed now.

 

[Talay]

Yes, client spotted that as well!

Fixed now.

Nope, not fixed, still hundreds of thousands of $$$ off.

 

[fisicx]

Did you clear your cache?

It’s a JavaScript function that’s needs to be reset. Your browser needs to clear cache so the updated JS file gets loaded.

 

[Talay]

Did you clear your cache?

It’s a JavaScript function that’s needs to be reset. Your browser needs to clear cache so the updated JS file gets loaded.

Yeah, ok now - different browser. Didn't clear cache.