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.