#### GregoryQueen

##### New member

- Joined
- Mar 29, 2021

- Messages
- 7

- Thread starter GregoryQueen
- Start date

- Joined
- Mar 29, 2021

- Messages
- 7

- Joined
- Jun 18, 2007

- Messages
- 24,338

Do you know the equation used for calculating monthly payments for a given loan amount, interest rate and loan-duration?

Please show us what you have tried and

Please follow the rules of posting in this forum, as enunciated at:

Please share your work/thoughts about this problem.

- Joined
- Mar 29, 2021

- Messages
- 7

I don't know the equation that can calculate the annual interest rate. I tried different ways but it always came out wrong. I don't know the equation that can calculate the annual interest rate.Do you know the equation used for calculating monthly payments for a given loan amount, interest rate and loan-duration?

Please show us what you have tried andexactlywhere you are stuck.

Please follow the rules of posting in this forum, as enunciated at:

Please share your work/thoughts about this problem.

Last edited:

- Joined
- Apr 12, 2005

- Messages
- 11,307

Please show us what you have tried and

Please follow the rules of posting in this forum, as enunciated at:

Please share your work/thoughts about this problem.

Banks are required to give disclosures for this sort of thing. Please go back and READ the terms of your loan.

- Joined
- Mar 29, 2021

- Messages
- 7

The result of this calculation is 5.5% per year, but I don't know how to calculate it.

Well you certainly can validate or invalidate it.

The result of this calculation is 5.5% per year, but I don't know how to calculate it.

- Joined
- Mar 29, 2021

- Messages
- 7

I = RC - C

where:

I - Interest

RC - Repaid Credit (153 302,4)

C - Credit (75 000)

I = 153 302,4 - 75 000; I = 78 302,4

153 302,4 because 425,84 * 360 (12 months * 30 years)

Formula for Rate of return:

RoR = Interest / Credit

RoR = 78 302,4 / 75 000 = 1,044032

Formula for Nominal Interest Rate:

NIR = Rate of return / period

NIR = 1,044032 / 1 = 1,044032 = 104,40% (WRONG RESULT)

Correct answer is 5,5%

According to the formula for Rate of return, we should have:

79 125 - 75 000 / 75 000 = 4125 / 75 000 = 0,055 = 5,5%

i have no idea where the number 79 125 came from.

- Joined
- Mar 29, 2021

- Messages
- 7

You mean the formula for calculating this exercise doesn't exist?

- Joined
- Jun 18, 2007

- Messages
- 24,338

There is no CLOSED FORM equation. It is solved through iterative method.

You mean the formula for calculating this exercise doesn't exist?

- Joined
- Apr 29, 2014

- Messages
- 385

I told you in post 5 that excel could approximate it. I also said that excel used a “so called” formula. It is not a closed form formula; it invokes an iterative algorithm

You mean the formula for calculating this exercise doesn't exist?

- Joined
- Mar 29, 2021

- Messages
- 7

From which calculation do you get this interest?Beer soaked ramblings follow.

I mean 0,0045833023239915

- Joined
- Apr 29, 2014

- Messages
- 385

Did you not notice the first excel screenshot where I highlighted the “so called” formula format that JeffM intimated at post #13. The 2nd screenshot was merely for show to let you know that you are to plug the value of x into the amortization expression to assure yourself that you will get the loan amount should you do so.From which calculation do you get this interest?

I mean 0,0045833023239915

- Joined
- Mar 29, 2021

- Messages
- 7

So, interest in Excel is calculated automatically? Don't you know how to calculate them?Beer soaked ramblings follow.

Did you not notice the first excel screenshot where I highlighted the “so called” formula format that JeffM intimated at post #13. The 2nd screenshot was merely for show to let you know that you are to plug the value of x into the amortization expression to assure yourself that you will get the loan amount should you do so.

I seem in the first first screenshot you highlighted the result from second screenshot.

If the "x" in the second screenshot is interest rate (which I'm looking for), then I don't need interest which is "i" in the fist screenshot.

Things like the interest rate implied by the amount of the periodic payment, the original principal,and the number of periodic payments are not calculated by a formula as you understand it. They can beSo, interest in Excel is calculated automatically? Don't you know how to calculate them?

I seem in the first first screenshot you highlighted the result from second screenshot.

If the "x" in the second screenshot is interest rate (which I'm looking for), then I don't need interest which is "i" in the fist screenshot.

I have no idea which one is used by Excel for calculating interest on annuities. Because Excel asks for a single initial guess, it may be using the Newton-Raphson method, which requires an initial guess.

If you remember your calculus, you can look up the Newton-Raphson method on google.

For an example of a different method of an iterated process, l shall show you the bisection method for finding an approximation to the square root of two.

\(\displaystyle 1 < 2 < 4 \implies 1 < \sqrt{2} < 2.\)

\(\displaystyle \left ( \dfrac{1 + 2}{2} \right )^2 = 1.5^2 2.25 > 2 \implies 1 < \sqrt{2} < 1.5.\)

\(\displaystyle \left ( \dfrac{1 + 1.5}{2} \right )^2 = 1.25^2 = 1.5625 < 2 \implies 1.25 < \sqrt{2} < 1.5.\)

\(\displaystyle \left ( \dfrac{1.25 + 1.5}{2} \right )^2 = 1.375^2 = 1.890625 < 2 \implies 1.375 < \sqrt{2} < 1.5.\)

\(\displaystyle \left ( \dfrac{1.375 + 1.5}{2} \right )^2 = 1.4375^2 = 2.06640625 > 2 \implies 1.375 < \sqrt{2} < 1.4375.\)

You keep doing that until you get a good enough approximation. It should be intuitive already that the answer will be close to 1.4.

You will get a good approximation doing that, but it is not a simple formula. Computers can do it quickly without error.

- Joined
- Apr 29, 2014

- Messages
- 385

Don't you read what volunteer helpers here are trying to tell you?So, interest in Excel is calculated automatically? Don't you know how to calculate them?

What're you blind?

Mind your grammar.I seem in the first first screenshot you highlighted the result from second screenshot.

The nominal rate compounded monthly is `j` (1st screenshot) and `i` (1st screenshot) is the effective monthly rate (x in the 2nd screenshot). How would you know how much interest you'll pay per month on the outstanding balance of your loan if you don't know what `i` is?If the "x" in the second screenshot is interest rate (which I'm looking for), then I don't need interest which is "i" in the fist screenshot.

Hi if you want a quick and simple answer in Excel just use this formula

=RATE(30*12,-425.84,75000,0)*12

1) make sure the payments are negative.

2) the zero at the end is that you want to assume the balance is reduced to zero at the end of term

3) To improve accuracy, it matters slightly if you are charged interest at the beginning of the month or at the end.

Charged at the end of each month : =RATE(30*12,-425.84,75000,0,0)*12

Charged at the start of each month : =RATE(30*12,-425.84,75000,0,1)*12