Ammortization formula errors?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am doing a project for school and we have to ammoratize an automobile loan
and when I put in the formulas into the spreadsheet the payment amount is
right but the principle amount keeps going down instead of up. What is the
problem.
60 month loan, 5% interest on $25,000.00 for five years once a month payment.
=PPMT(IR/12,1,60,0). The first amounts come out right but when I go to
figure the second amounts the principle amount goes from 470.55 to 465.
something and it should go up.
 
The principal should (better) go down!
You're paying of the loan (amortizing it).
Each payment is blended (paying interest and reducing the principal).
 
Thanks for your response. I realize the principle amount should go down but
in the ammortization process you have the payment amount, the amount that is
applied to the principle and the amount that is applied to the interest
amount. As you make payments the amount that you pay on the principle should
go up and the interst should go down. In my case it was showing that I was
paying $603.88 and 470.55 was applied to principle and 133.33 was applied to
interest in the first payment. In the second payment it showed that I paid
$603.88 and $463.63 was applied to the principle and $131.37 was applied to
interest. So what happened to the $8.88?
 
I'm a bit confused. The numbers you are giving us don't make any sense. I'd
suggest you work on gettting the correct results for the 1st month before
you worry about month 2. Check cell references and whether they are absolute
or relative (depending on how you have your sheet set up, everything except
Payment# should be absolute).

Given: 25,000 loan, 5 year term, monthly payments (per your 1st post).

$133.33 interest in the 1st month (per your last post) would be correct for
a 6.3998% interest rate (total payment 487.98 & principal of 354.65), but
not for the 5% you say you are using.

if 5% interest rate (per 1st post, using PPMT and IPMT)
PPMT IPMT Total
month 1 367.61 104.17 471.78
month 2 369.15 102.63 471.78

PPMT(0.05/12, Payment#, 60, 25000)

I assume the "Type" argument for PPMT is omitted or 0 because if payments
were at the beginning of the period, the 1st payment would be 100% to
principal.

HTH,
 
Or your principal amount was keyed as $32,000-at a nominal rate of 5%, compounded monthly
with 12 payments per year = 603.88/mo - 1st mo 470.55 applied to principal & 133.33 to
interest.

(Sorry, missed the part about your problem being that the amount applied to principal was
dropping.)
 
Back
Top