View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default Interest Functions

"Bruno Campanini" wrote:
"dwe" [...] wrote:
Loan = 30,180.00
interest = 14.90% pa (calculated daily)
term of loan would be 60 months

[....]
So, these are the exact parameters for your plan [...]:
Loan = 30 180.00
Montly interest = 1.2416768%
Terms = 60 months

A monthly interst rate of 1.2416768% corresponds
to 15.961% annual rate. You get this with the formula:
(1 + 0.012414768)^12 - 1 = 0.15961 = 15.961%


I think you intended to write 1.2416667% (14.9%/12).
I suspect that you had correctly computed
(1 + 14.9%/12)^12 - 1 = 15.960861%. But after you
rounded that to 15.961%, you tried to reverse-engineer
the monthly rate with: (1 + 0.15961)^(1/12) -1 =
1.2416768%.

From 15.961% per year you get daily interest rate with:
(1 + 0.15961)^(1/365) - 1 = 0.000405791 = 0.0405791%


I concur that this computation would be consistent with the
usual way to compute the monthly payment. But I would
use accurate numbers to minimize numerical error, namely:
(1 + (1 + 14.9%/12)^12 - 1)^(1/365) - 1 =
(1 + 14.9%/12)^(12/365) - 1 = 0.0405788%.

To be honest, I am not sure that lenders do not do just that,
instead of compounding 14.9%/365, as I had said. Your
formula would explain why the OP's numbers match the
lender's computation of the payment and the total interest,
even though interest for individual months did not match.

I am trying to get ahold of my daughter's loan records to
vet all of this for modern (US) loans.

Then the overdue fee for 23 day-delay in montly payment
is: 716.40 * ((1 + 0.000405791)^23 -1) = 6.71623


That might be one way for the lendor to compute the late
fee plus interest. But there is no "mathematical" right or
wrong here. In fact, the late fee could be totally unrelated,
as is the case with my 30-year-old loan. The late fee was
8% per month times the monthly payment, even though the
nominal annual rate was 8.75%.

Anything less then this is a gift compared with mathematical
results, anything more is a robbery.


Perhaps. But arguably the lender chooses a high late fee
partly to discourage you from being late and partly due to
uncertain opportunistic costs that the lender might incur
because late payments might mean that the lender has
less capital to work with.