$ to pay off loan

Hello All

Does anyone know how to set up Excel to calculate this:

A person has a 60 month car loan (say $30,000 at 7 percent) - $594.04
monthly
At 36 months, the person wants to pay off the loan.

How much would the person need to come up with, assuming the standard loan
formula and no prepayment penalty? It seems like this would be easy to
calculate, except that a standard loan formula front-loads interest payments
(at least that's my understanding), so I'm not sure how much principal is
left after 36 months. In other words, I'm pretty sure it's just not a
straight 30,000 divided by the remaining 24 months.

Thanks for any help.


--
Regards,
Anthony Giorgianni

The return address for this post is fictitious. Please reply by posting back
to the newsgroup.

Hi Anthony,

Try,

=FV(7%/12,36,-594.04,30000)

which will give you an outstanding balance of $13,267.69 after 3 years.

Hope this helps!

In article ,
"Anthony Giorgianni"
d wrote:

Hello All

Does anyone know how to set up Excel to calculate this:

A person has a 60 month car loan (say $30,000 at 7 percent) - $594.04
monthly
At 36 months, the person wants to pay off the loan.

How much would the person need to come up with, assuming the standard loan
formula and no prepayment penalty? It seems like this would be easy to
calculate, except that a standard loan formula front-loads interest payments
(at least that's my understanding), so I'm not sure how much principal is
left after 36 months. In other words, I'm pretty sure it's just not a
straight 30,000 divided by the remaining 24 months.

Thanks for any help.

Anthony Giorgianni wrote:

Hello All

Does anyone know how to set up Excel to calculate this:

A person has a 60 month car loan (say $30,000 at 7 percent) - $594.04
monthly
At 36 months, the person wants to pay off the loan.

How much would the person need to come up with, assuming the standard loan
formula and no prepayment penalty? It seems like this would be easy to
calculate, except that a standard loan formula front-loads interest payments
(at least that's my understanding), so I'm not sure how much principal is
left after 36 months. In other words, I'm pretty sure it's just not a
straight 30,000 divided by the remaining 24 months.

Thanks for any help.


We need to know more about how the interest is calculated; a 60 month
loan of $30,000 with a nominal annual interest rate of 7% would *not*
have a payment of $594.04. So there's some information you'e not
providing about what the deal originally was.

Alan Beban

Do a google search for a loan calculator. There are many
out there. I have one I made that a few people like, I
could send it to you if you wish.

Charlie O'Neill
-----Original Message-----
Hello All

Does anyone know how to set up Excel to calculate this:

A person has a 60 month car loan (say $30,000 at 7
percent) - $594.04
monthly
At 36 months, the person wants to pay off the loan.

How much would the person need to come up with, assuming
the standard loan
formula and no prepayment penalty? It seems like this
would be easy to
calculate, except that a standard loan formula front-
loads interest payments
(at least that's my understanding), so I'm not sure how
much principal is
left after 36 months. In other words, I'm pretty sure
it's just not a
straight 30,000 divided by the remaining 24 months.

Thanks for any help.


--
Regards,
Anthony Giorgianni

The return address for this post is fictitious. Please
reply by posting back
to the newsgroup.


.

It's was only a hypothetical, Alan/

But Excel and my financial calculator agree that the PMT is $594.03 assuming

n=60
PV=-$30,000
i=.07/12
FV=$0

No? What do you get?


--
Regards,
Anthony Giorgianni

The return address for this post is fictitious. Please reply by posting back
to the newsgroup.



"Alan Beban" wrote in message
...
Anthony Giorgianni wrote:

Hello All

Does anyone know how to set up Excel to calculate this:

A person has a 60 month car loan (say $30,000 at 7 percent) - $594.04
monthly
At 36 months, the person wants to pay off the loan.

How much would the person need to come up with, assuming the standard
loan
formula and no prepayment penalty? It seems like this would be easy to
calculate, except that a standard loan formula front-loads interest
payments
(at least that's my understanding), so I'm not sure how much principal
is
left after 36 months. In other words, I'm pretty sure it's just not a
straight 30,000 divided by the remaining 24 months.

Thanks for any help.


We need to know more about how the interest is calculated; a 60 month
loan of $30,000 with a nominal annual interest rate of 7% would *not*
have a payment of $594.04. So there's some information you'e not
providing about what the deal originally was.

Alan Beban

Hmmm... . IS that right? I'm not sure of the logic behind it...though I
suffer from a small brain. If a $30,000 loan is halfway repaid (IE 36
months), wouldn't I have to owe AT LEAST $15,000 more, assuming the
principal and interest are credited equally at the beginning? If the
interest has some front-loading, one would think the balance has to be more
than $15,000, assuming there is no pre-payment penalty. Is my thinking
faulty?


--
Regards,
Anthony Giorgianni

The return address for this post is fictitious. Please reply by posting back
to the newsgroup.




"Domenic" wrote in message
...
Hi Anthony,

Try,

=FV(7%/12,36,-594.04,30000)

which will give you an outstanding balance of $13,267.69 after 3 years.

Hope this helps!

In article ,
"Anthony Giorgianni"
d wrote:

Hello All

Does anyone know how to set up Excel to calculate this:

A person has a 60 month car loan (say $30,000 at 7 percent) - $594.04
monthly
At 36 months, the person wants to pay off the loan.

How much would the person need to come up with, assuming the standard
loan
formula and no prepayment penalty? It seems like this would be easy to
calculate, except that a standard loan formula front-loads interest
payments
(at least that's my understanding), so I'm not sure how much principal
is
left after 36 months. In other words, I'm pretty sure it's just not a
straight 30,000 divided by the remaining 24 months.

Thanks for any help.

Actually, the monthly payment is made up of a blend of principal and
interest. And the ratio between the two changes over the life of the
loan. So as time goes by, the interest portion of the payment becomes
less while the principal portion increases.

In article ,
"Anthony Giorgianni"
d wrote:

Hmmm... . IS that right? I'm not sure of the logic behind it...though I
suffer from a small brain. If a $30,000 loan is halfway repaid (IE 36
months), wouldn't I have to owe AT LEAST $15,000 more, assuming the
principal and interest are credited equally at the beginning? If the
interest has some front-loading, one would think the balance has to be more
than $15,000, assuming there is no pre-payment penalty. Is my thinking
faulty?


--
Regards,
Anthony Giorgianni

Anthony Giorgianni wrote:

Hmmm... . IS that right? I'm not sure of the logic behind it...though I
suffer from a small brain. If a $30,000 loan is halfway repaid (IE 36
months), wouldn't I have to owe AT LEAST $15,000 more, assuming the
principal and interest are credited equally at the beginning? If the
interest has some front-loading, one would think the balance has to be more
than $15,000, assuming there is no pre-payment penalty. Is my thinking
faulty?


Whoops! Yes, that's right. What I tested for was the number of periods
using 30000 instead of -30000.

The balance after 36 months (which is more than halfway on a 60-month
loan) is $13,268:

=PV(0.07/12,24,594.04)

Alan Beban

Hi Anthony!

Two ways:
=FV(0.07/12,36,-594.04,30000,0)
Returns: -13267.6905983431

Or: Analysis ToolPak use of CUMPRINC:

=30000+CUMPRINC(0.07/12,60,30000,1,36,0)
Returns: 13267.8520672658

Difference is due to approximation used by FV formula but in reality
your repayments are more likely to be an exact amount of cents, so FV
might actually give a closer answer if that is really important.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

"Anthony Giorgianni"
d wrote in message
...
Hello All

Does anyone know how to set up Excel to calculate this:

A person has a 60 month car loan (say $30,000 at 7 percent) -
$594.04
monthly
At 36 months, the person wants to pay off the loan.

How much would the person need to come up with, assuming the
standard loan
formula and no prepayment penalty? It seems like this would be easy
to
calculate, except that a standard loan formula front-loads interest
payments
(at least that's my understanding), so I'm not sure how much
principal is
left after 36 months. In other words, I'm pretty sure it's just not
a
straight 30,000 divided by the remaining 24 months.

Thanks for any help.


--
Regards,
Anthony Giorgianni

The return address for this post is fictitious. Please reply by
posting back
to the newsgroup.

"Anthony Giorgianni" wrote...
It's was only a hypothetical, Alan/

But Excel and my financial calculator agree that the PMT is $594.03 assuming

n=60
PV=-$30,000
i=.07/12
FV=$0

No? What do you get?
...

XL97 SR-2 using the formula

=PMT(0.07/12,60,-30000)

returns $594.04 (actually, 594.035956210484). Direct calculation,

30000 * (0.07 / 12) / (1 - (1 + 0.07 / 12)^-60)

returns 594.035956210484. Maybe Alan means you're missing that last cent.

--
To top-post is human, to bottom-post and snip is sublime.