Rate Function

Question

Description	Data
Years of the loan	1
Monthly payment	$         <br>(933.33)
Amount of the loan	$     10,000.00
Monthly Interest Answer	1.79%
Annual Interest Answer	12.00%

Formula used to calulcate monthly interest is =RATE(1*12,-933.333,1000,0) which is giving me answer of 1.79% which is wrong it should be 1% 

But when I used formula for Annual it gives me correct answer of 12% =RATE(1,-933.33*12,10000,0) 

My question is why it is giving me wrong answer in Monthly Calculation? Thanks to explain 

Sheet "RATE example from online" can be referred in attachment, Also check other sheets where I tried to work in different examples and then worked answers by calculating fixed and reducing balance method but my answers are not correct. I need some expert opinion RATE & PMT Function Worksheet 

Also please explain how this principal 788.49 is calculated from below table 

Answer 1

Muzahir_H wrote:

Also please check how this principal of 788.49 is calculated         

As I explained previously, if we assume an interest rate of 1% (instead of a payment of 933.33), the monthly payment is calculated as PMT(12%/12, 12, -10000) = 888.49.

(Previously, I wrote 889.49, an obvious typo, which you could have discovered by entering the PMT formula yourself.  It is also demonstrated by the online loan calculator that you show.)

And if the interest rate is 1%, obviously the first interest amount is $100, namely 1% of 10,000, leaving 788.49 to apply to the principal.

The point, again, is:  if you assume that the payment is 933.33, it changes the interest rate to about 1.7880%, as I demonstrated previously.

But if you assume that interest rate is 1%, it changes the payment to 888.49, as I demonstrated previously (but with a typo; sorry!) and as you demonstrated with the online loan calculator.

And once again, this is based on the most common loan reduction model, namely the "actuarial method".  Other methods are possible.  For example, some countries (not the US) allow for the use of the "Rule of 78" method, which has a very different amortization schedule than yours or mine.

PS.... Returning to the "actuarial method", in some countries (not the US), the annual rate is stated as a compounded rate.  Then, the monthly rate is (1+12%)^(1/12)-1 instead of simply 12%/12.  I don't think that is relavant to your question.  But I mention it because you might encounter it when using some online loan calculators.  Oh, and the Canadian calculation is different from everyone else, to wit:  (1+12%/2)^(1/6)-1 (!!).

Answer 2

Muzahir_H wrote:

but I arrived simply at 10000 x 12% = 1200 so total to be paid 11200 / 12 = 933.33

[.....]

Principal	Prin. Inst.	Interst	Total Instalment
10,000.00	833.33	100.00	933.33

.... Which does not fit the model of loan amortization (called the "actuarial method") that Excel financial functions are based on -- and what most of the world uses for real loans.

Instead, PMT returns the sum of principal and interest, the proportion of which varies with each payment.

Either you misunderstand how your loan payments are calculated; or your loan model is different due to regional differences.  (I believe that Muslim loan payments are calculated differently; something to do with Sharia law.)

Anyway, my amortization table correctly demonstrates the loan payment model that Excel financial functions are based on.

Answer 3

Also please check how this principal of 788.49 is calculated 

Answer 4

but I arrived simply at 10000 x 12% = 1200 so total to be paid 11200 / 12 = 933.33

But if I go on reducing balance method which I guess this formula is following then correct answer of monthly payment is 887.50 which is close to your figure 

Principal	Prin. Inst.	Interst	Total Instalment
10,000.00	833.33	100.00	933.33
9,166.67	833.33	92.00	925.33
8,333.33	833.33	83.00	916.33
7,500.00	833.33	75.00	908.33
6,666.67	833.33	67.00	900.33
5,833.33	833.33	58.00	891.33
5,000.00	833.33	50.00	883.33
4,166.67	833.33	42.00	875.33
3,333.33	833.33	33.00	866.33
2,500.00	833.33	25.00	858.33
1,666.67	833.33	17.00	850.33
833.33	833.33	8.00	841.33
	10,000.00	650.00	10,650.00
		6.500%	887.50

Answer 5

Your first mistake is in thinking that 933.33 is the correct payment for a monthly interest rate of 1%.

To demonstrate the correctness of the RATE result, see the following amortizations schedule.

For an interest rate of 1%, the monthly payment is 888.49 [*]:

=PMT(12%/12, 12, -10000)

---

[*] Errata: I had written 889.49, a typo.