Calculating Interest Amount

B

bronxbabe

I'm trying to insert the formula for the correct results below. Here's what
I have:

Int Rate Int. Amt
Total Principal No of days (L+1.5%)
69,000,000.00 182 7.46875 2,605,348.96

I would like to achieve this Int. Amt result by using a formula. I was told
that I could just multiply the Total Principal * Int Rate, but when I insert
that formula, my results are $515,343,750.00.

Any suggestions?

Thanks in advance,
 
J

Joel

7.46875% = .0746875
to convert from percentage to decimal you have to divide by 100
 
B

bronxbabe

But, i'm still receiving the same result (515,343,750.00). My formula is
=SUM(A2*F2). should i not be using the sum syntax?

Thanks again,
 
B

bronxbabe

well, i guess i have more work to do on this because this formula gives me
$5,153,437.50 and the result should be $2,605,348.96. maybe there's
something to do with the "L+1.5%".

Thanks again,
 
D

David Biddulph

Yes, you have got more work to do, because you did your calculation using
the principal and the annual interest rate, but you forgot to include the
number of days. Won't you pay an amount of interest which is proportional
to the period of the loan? You need to multiply your existing answer by 182
and divide by the number of days which your lender is assuming in a year
(which in your case seems to be 360 to give that answer).

And another thing (which doesn't affect the result but affects the
readability of the formula) is for you to think about why you (and Joel)
have used SUM(). What numbers are you *ADDING*? Why did you say
=SUM(A2*F2) and not just =A2*F2 (or now =A2*F2*182/360)?
If you've got a function that you don't understand, such as SUM(), then
please look it up in Excel's help.
 
J

Joel

The interest rate 7.46875 is a yearly interest rate. It looks like the
actual rate for a year componded daily. This is really 7.2% componded daily.

In your case the 7.46875 is the yearly interest, but your rate was for only
182 days (1/2 year). Therefore if you took 7.2% interest componded daily for
182 days the rate would be 3.7086279

Int Rate Int. Amt
The L+1.5% is the Prime Interest Rate + 1.5%. Interest Rates are usually
base on the governments Prime Interest rate plus a fix percentage. My number
is close to the rate you were given.

I based these numbers on US interest rates which uses a 360 day banking
year. Your rate may be different if the calculations were based on a 365 day
year. Banks in US like 360 days becasue it divides evenly by 12 so each
month is 30 days.
 
J

joeu2004

I'm trying to insert the formula for the correct results below. Here's what
I have:
Int Rate Int. Amt
Total Principal No of days (L+1.5%)
69,000,000.00 182 7.46875 2,605,348.96
Click to expand...

It appears to have been computed as follows:

=round(69000000 * 7.46875% * 182 / 360, 2)

Frankly, I find the divisor (360) quite surprising. What is the
jurisdiction (country) for this investment?
 
J

Joel

US truth in Lending Laws require Lending institutions to give both the
Interest Rate and the APR (Anual Interest Rate componded). US banking
compute interest on a 360 day year which is 30 days a month for 12 months.
for some reason microsoft formulas use 365. I guess most of the world in
banking does things diffferent than the USA. 360 day years are nice because
the interest is the same for each month. Monthly payments are the same. On
a 6 month loan in the US they usually give 2 grace days thats why the length
of the loan is 182 days.
 
J

joeu2004

[....]
The L+1.5% is the Prime Interest Rate + 1.5%. Interest Rates are usually
base on the governments Prime Interest rate plus a fix percentage.
Click to expand...

I assume "L" is LIBOR, the London Interbank Offered Rate Index. That
is not the same as the "Prime Rate" in the US, which is set by the
Federal Reserve.
I based these numbers on US interest rates which uses a 360 day banking
year. Your rate may be different if the calculations were based on a 365 day
year. Banks in US like 360 days becasue it divides evenly by 12 so each
month is 30 days.
Click to expand...

You are correct that the original computation used a divisor of 360 --
although I believe you applied it incorrectly. See my response to the
OP's original posting.

However, I don't know of any US bank that (still) uses a 360-day year
when computing daily interest rates. And there is good reason to
believe that they would not. It is not in their "best interest" ;-).

Most US banks comply with FDIC regulations for the Truth In Savings
Act. Reg DD Sec 230.7 requires that compliant institutions "calculate
interest by use of a daily rate of at least 1/365 of the interest
rate". It does permit the use of 1/366 in leap years.

Of course, 1/360 is more than 1/365; so it would be compliant. But
Sec 230.7 also requires that compliant institutions "calculate
interest on the full amount of principal in an account for
__each_day__". If a bank used a daily rate of 1/360 of the annual
rate, it would pay more interest over some number of days than if the
bank used a daily rate of 1/365. It seems unlikely that a bank would
pay out more interest than the minimum required by law.
 
J

joeu2004

US truth in Lending Laws require Lending institutions to give both the
Interest Rate and the APR (Anual Interest Rate componded).
Click to expand...

Where did the OP say that this was about a loan, not an investment? I
know of some international securities that use LIBOR as the base rate
(i.e. LIBOR plus x%).
US banking
compute interest on a 360 day year which is 30 days a month for 12 months.
Click to expand...

App J of Reg Z (regulations for the TLIA) states that "f the unit-
period is a day, the number of unit-periods per year shall be 365".

And by the way, App J of Reg Z also states that "[t]he annual
percentage rate shall be the nominal annual percentage rate determined
by multiplying the unit-period rate by the number of unit-periods in a
year".
for some reason microsoft formulas use 365.
Click to expand...

Well, XIRR does use 365. But the formulas that I commonly use for
loan computations -- PV, FV, PMT and RATE -- do not make any
assumptions at all. The user inputs the compounding frequency. That
also seems to be true of some of the other loan-related financial
functions that I glanced at just now.
 
J

joeu2004

PS: I did not comment on this before because the original statement
seems irrelevant to the OP's question, but FYI....

US truth in Lending Laws require Lending institutions to give both the
Interest Rate and the APR (Anual Interest Rate componded).
Click to expand...

Not to nitpick, but for your information, the APR for US loans is not
"the annual interest rate compounded". That is a common
misconception, presumably due to the (mis)use of that term in
connection with savings and other investments by some banks as well as
individuals. The term APY -- annual percentage yield -- is used in
the Truth In Savings Act regulations to describe that compounded
annual rate of return.

For US loans, according to App J of Reg Z, the APR is effectively the
"annualized" internal rate of return of the financed amount and the
periodic payments of principal and all "finance charges" (certain
specified charges in addition to the interest on principal) over the
time. The unit of time is the payment frequency (e.g. monthly). But
as I noted in a previous posting, Reg Z App J specifies that the per-
period rate is "annualized" simply by multiplying by the number of
unit-periods per year.

As defined in Reg Z, the APR is "a measure of cost of credit". It is
not directly related to the interest rate used to compute periodic
payments and interest accrued on the principal. The "finance charges"
used to compute the APR include other amounts paid, such as points,
prepaid interest, loan fees, appraisal and credit report fees.

Moreover, Reg Z "requires" the disclosure of only the APR, not also
the "simple annual rate or periodic rate" (nominal interest rate).
The nominal interest rate is explicitly allowed, but not required in
advertisements. It is not mentioned as part of the Reg Z transaction
disclosure; but it is not explicitly disallowed either.

HTH.
 
Top