Forum Discussion
Need some help understanding Rate function
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
Hm, i was assuming that I only need fees and interest as payment (pmt) and the function would divide principal into 4 periodic payments and would add it to interest and fees. Ok, thats error of mine.
If I say now that mine annual payment is : fees and interest 100865 + principal part 250000
my arguments
nper: 4
pmt: -350865
pv: 1000000
Rate would be = 15,08%
Hm, the APR formula that i have is
(Fees + Interest/Principal)/n*365*100
where:
Interest = total interest paid
Principal = Loan amount
n =is the number of days in loan term
So if put values in it :
((100865 * 4 ) / 1000000 / 1460 (four years in days) * 365 *100 = 10,0865 %
So now i dont get what percentage of rate function represents with 15,08%