Need some help understanding Rate function

GediminasC 

The issue you are encountering with the RATE function is related to the sign convention used in financial functions. The RATE function assumes that cash outflows (payments made) are represented by negative numbers, and cash inflows (receipts or money received) are positive.

In your case, you are dealing with a loan, which means you are making payments to pay off the loan, so the payment should be represented as a negative number.

Let's correct your inputs:

• NPER: Number of periods, which is 4 (for 4 years).
• PMT: Payment made each period, which is the annual payment to the loan. Since it's an outgoing payment, it should be represented as a negative number. So, PMT should be -100865.
• PV: Present value, which is the loan amount. Since it's an outgoing payment (you are receiving the loan amount), it should be represented as a positive number. So, PV should be 1000000.

Now, using the corrected inputs:

=NOMINAL(RATE(4, -100865, 1000000), 1)

Note:

1. The RATE function returns the periodic interest rate. To convert it to an annual rate, you can use the NOMINAL function.
2. The NOMINAL function takes the periodic interest rate and the number of compounding periods per year (1 for annual compounding) and returns the nominal annual interest rate.

By doing this, you should get the desired APR. The negative sign in the result simply indicates that it's an outgoing payment (cash outflow) according to the sign convention used in financial functions. AI was partially deployed to support the text.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark it with Yes!

This will help all forum participants.

GediminasC  wrote:  ``annual fees and interests are 100865 [....] pmt: -100865``

You get a negative rate because 4 payments of 100865 = 403460 is not enough to pay off a loan of 1000000, not to mention interest and additional fees.

Excel financial functions like RATE assume a declining principal balance with each payment.  Therefore, the amount of interest also declines with each payment.

We cannot say anything about the periodic fees because you did not.

-----

GediminasC  wrote:  ``If I would calculate simply just dividing 100865/1000000, I would get 10.09 %. That's my desired outcome``

If 10.0865% is the desired annual rate for fees and interest, the minimum payment is 316061.71; namely:

=-PMT(10.0865%, 4, 1000000)

Then, the loan is amortized with annual payments over 4 years as follows.

Formulas:

B5: =$A$1

C5: =B5-D5

D5: =E4*10.0865%

E5: =E4+D5-B5