Dec 14 2023 11:44 PM
Hi, I need some help to understand how Rate function works in this case :I want to calculate APR of one year of the loan. Let's say the loan is 1000000 for the period of 4 years and annual fees and interests are 100865. If I would calculate simply just dividing 100865/1000000, I would get 10.09 %. That's my desired outcome.
As I understand Rate function, you need a number of periods - nper , payments - pmt and present value - pv. Inputing my values into Rate function :
nper: 4
pmt: -100865
pv: 1000000
At this point my reasoning is : I have 4 periods with annual payment of 100865 to the loan, but Rate function gives me -28,49 %: Whats going on here? Why does it give a negative percentage? If I would change nper argument to 1 I would get -89,91 % and if I would add 1 to this result, I would get my desired outcome. Is this correct calculation to get APR at this point? Or what I am getting?
Dec 15 2023 12:43 AM - edited Dec 15 2023 01:12 AM
@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
Dec 15 2023 01:42 AM
Dec 15 2023 03:43 AM
@GediminasC wrote: ``the APR formula that i have is (Fees + Interest/Principal)/n*365*100``
Loans can be structured in a lot of different ways, and "APR" can be calculated in a lot of different ways, both subject to local laws.
The methods that you described are not consistent with the methods that Excel financial functions, notably RATE, are based on.
(Excel financial functions use the so-called actuarial method. In contrast, are you following Islamic banking practices?)
So, if you continue to use different methods, you should abandon your use of the RATE function. GIGO! ("Garbage in, garbage out")
That said, I cannot tell if you truly understand your loan methods, or if you simply misunderstand them.
So, I really cannot help you further.
-----
I will comment on some inconsistencies in your mathematics, as you present them.
First, you seem to have a typo in your abstract definition of APR. In part, you are calculating:
(fees + interest) / principal
not
(fees + interest/principal)
I believe the first expression is correct, if 100865 is fees+interest.
Second, when you multiply by 100 at the end, the result is the decimal number 10.0865, not the percentage 10.0865% as you wrote.
(Note that I use period as the decimal point, whereas you use comma. That is not an issue.)
In fact, I deprecate the practice of multiplying by 100 at the end. I know that it is common practice in some presentation of "percentages". But it just confuses calculations, as you demonstrate.
Dec 15 2023 04:16 AM - edited Dec 15 2023 04:18 AM
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:
Now, using the corrected inputs:
=NOMINAL(RATE(4, -100865, 1000000), 1)
Note:
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.
Dec 15 2023 06:47 AM - edited Dec 15 2023 09:37 AM
deleted
Dec 15 2023 07:12 AM - edited Dec 15 2023 07:32 AM
It's always amazing that people only become happy when they make stupid comments about others. Everyone tries to help everyone here, the fact that it doesn't always work doesn't mean that no one should get in touch because someone makes another stupid comment. I pay very close attention to such understated comments that refer to any person.
In order not to be misunderstood,
I am always happy to receive constructive and detailed feedback and corrections.
Apparently there was a misunderstanding in my first answer, which has now been clarified.