Annual Percentage Rate

G

Guest

I would like figure the amount of interest on a loan
The information I have is..
loan amount 97500
Interest rate 6.0%
30 year or 360 months
any suggestions?
 
D

Dave O

Check out the CUMIPMT function in Help: it calculates the amount of
interest involved in a typical mortgage-type loan. (Caution: you may
be very surprised at the number!) The PMT function will calculate your
monthly payment, just FYI, but it does not include things like taxes
and insurance.
 
D

Domenic

For the interest paid in a period, have a look at the IPMT function.
For the interest paid between two periods, have a look at the CUMIPMT
function.

Hope this helps!
 
J

joeu2004

frustratedwthis said:
I would like figure the amount of interest on a loan
The information I have is..loan amount 97500
Interest rate 6.0% 30 year or 360 months

First, is 6% the APR, as your posting subject suggests, or is
it the nominal rate? That can make a difference of $5000 (5%)
in the amount you are interested.

Second, is the interest compounded daily (typical) or monthly?
That can make a difference of $1800 (1.7%) in the answer.

Although CUMIPMT() will give you the answer you are looking
for, your next question is likely to be: how to compute the
monthly payment? Perhaps you already discovered the PMT()
function. Either way, CUMIPMT() becomes superfluous.

If 6% is the nominal rate, the monthly payment is:

=ROUND(PMT(6%/12,360,-97500),2)

Then the total interest is simply:

=360*monthlyPayment - 97500

This might give a slightly different number than CUMIPMT()
because of the ROUND() operation. Also note that I entered
the PV as a negative number so that PMT() will be positive.
CUMIPMT() only permits a positive PV, requiring that you
write -CUMIPMT(...) -- unless you like to see negative dollar
values ;-).

(Purist will say that a positive PV is correct anyway. Really,
it depends on our point of view.)

If 6% is the APR, replace "6%/12" with:

=RATE(12,,-97500,97500*(1+6%))

If interest is compounded daily, the monthly rate can be
estimated (it is different for 28, 29, 30 and 31-day months) by:

=FV(6%/360,30,,-1)-1

If 6% is the APR, replace "6%/360" with RATE(360,...).

PS: Your answer will never match the lender's answer exactly.
Also, beware of using a mortgage APR in the manner above.
Sometimes, the published APR includes other fees, following
federal guidelines. In that case, it is better to work with the
nominal rate, if available.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top