How do I calculate after making 12 payments of my mortgage

L

Lutrinh

Hi, can anyone tell me which fonction should I use to calculate a loan of
100,000$ with an interest Rate of =5.15% (0.4291) for 25 years (300) and pmt
= -593.36.

Question: after 12 payments How much do I still own the bank? The answers is
$97982.46 by using my calculator but I don't know which fonction do I need to
use in Microsoft Excel .

Could anyone help Me!

Regards

Lutrinh
 
J

John Bundy

Look at the PMT function, it will explain how to use, to get what you owe the
bank, look into the PV and FV of the function.
 
R

Ron Rosenfeld

Hi, can anyone tell me which fonction should I use to calculate a loan of
100,000$ with an interest Rate of =5.15% (0.4291) for 25 years (300) and pmt
= -593.36.

Question: after 12 payments How much do I still own the bank? The answers is
$97982.46 by using my calculator but I don't know which fonction do I need to
use in Microsoft Excel .

Could anyone help Me!

Regards

Lutrinh
Click to expand...


=Loan+CUMPRINC(Rate/12,Term*12,Loan,1,12,0)



--ron
 
J

joeu2004

Lutrinh said:
Hi, can anyone tell me which fonction should I use to calculate a loan of
100,000$ with an interest Rate of =5.15% (0.4291) for 25 years (300) and pmt
= -593.36.
Question: after 12 payments How much do I still own the bank? The answers is
$97982.46 by using my calculator but I don't know which fonction do I need to
use in Microsoft Excel .
Click to expand...

Ostensibly:

=fv(5.15%/12, 12, 593.36, -100000)

But that evaluates to 97982.50 instead of 97982.46. A small
difference; but it might be instructive to understand the reason.

It appears that you might have used the exact payment amount, which in
Excel is computed by:

=pmt(5.15%/12, 300, -100000)

That evaluates to 593.3626 (rounded). Substituting:

=fv(5.15%/12, 12, pmt(5.15%/12, 300, -100000), -100000)

evaluates to 97982.4681 (rounded). I would round that to 97982.47; but
you might have truncated or rounded down to 97982.46.

Since the payment is "real money", I think it is more correct to use
the actual currency amount, namely 593.36 -- or whatever payment amount
the bank chooses arbitrarily.

Caveat: Your estimate might never exactly match a bank's amortization
schedule because there are a number of ways of dealing with "rounding
error" and because some lenders compound interest on a daily basis
between payments. Over 300 months, the difference can be sizable.
 
D

Dana DeLouis

Question: after 12 payments How much do I still own the bank? The answers
is
$97982.46 by using my calculator..
Click to expand...

=CUMPRINC(5.15%/12,300,100000,1,12,0)

Hi. The total principal paid down from months 1 to 12 is the equation
above. Subtract this from your loan to calculate the remaining balance.
 
L

Lutrinh

John, Ron, Joeu2004 and Dana, Thank you for responding to my question. I try
each and every equation, they all worked perfectly. Thanks especially
joeu2004 for spending your time explaining in detail
 
Top