$ to pay off loan

[Anthony Giorgianni]

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.

 

[Domenic]

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!

"Anthony Giorgianni"

 

[Alan Beban]

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

 

[Charlie]

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

 

[Anthony Giorgianni]

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.

 

[Anthony Giorgianni]

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]

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.

"Anthony Giorgianni"

 

[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?

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

 

[Norman Harker]

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
(e-mail address removed)
"Anthony Giorgianni"

 

[Anthony Giorgianni]

Thank you everyone.

Just to make sure I'm analyzing that correctly:

The payoff is $13,267 -- and that amount would be 100% principal?

And this: If I were to continue making the payments for the remaining 24
months instead, the total would be 594.04*24 = 14,256.96?

So the savings of paying it early would be $14265-13227 (not counting the
change)? Or $1,038 in interest savings?

This is interesting, because a variety of auto experts have assured me that
36 months into a 60 month loan, the borrower would be upside down - in other
words, the payoff of the loan would exceed the value of the car. But lease
residuals put the value of a 36-month car at about half - maybe $15,000. So
it looks like the value of the car (selling it perhaps), would more than
cover the $13,267 needed to pay off the loan early.

Interesting. I'll have to save all this.

Thanks again.

--
Regards,
Anthony Giorgianni

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

 

[Domenic]

"Anthony Giorgianni"

The payoff is $13,267 -- and that amount would be 100% principal?

Right! The balance outstanding would be $13,267.69
=FV(0.07/12,36,-594.04,30000,0)

If I were to continue making the payments for the remaining 24
months instead, the total would be 594.04*24 = 14,256.96?

Right again!

So the savings of paying it early would be $14265-13227 (not counting the
change)? Or $1,038 in interest savings?

I think you meant $13,267, not $13,227. So then the savings would be
$989.27 ($14,256.96-$13,267.69).

This is interesting, because a variety of auto experts have assured me that
36 months into a 60 month loan, the borrower would be upside down - in other
words, the payoff of the loan would exceed the value of the car.

If you mean the balance of the loan outstanding (in this case,
$13,267.69) would exceed the value of the car, I don't think so. That
would mean that the car would have depreciated more than half of it's
value after only 3 years.

But lease
residuals put the value of a 36-month car at about half - maybe $15,000. So
it looks like the value of the car (selling it perhaps), would more than
cover the $13,267 needed to pay off the loan early.

I wouldn't be looking at the leasing industry's standard residual value.
That's not what the car's fair market value would be in 3 years time.
It's used for financing purposes, making leasing more expensive.

Barring any unforeseen circumstances, I would think that after 3 years
the car would still be worth more than half of it's original value.
Which would mean that the car's value should exceed your outstanding
balance at that time.

 

[Anthony Giorgianni]

Thank you, Dominic, exactly what I needed.

I did talk to a top leasing guy for one of the big auto companies the other
day. For residuals, he said they try to value the car somewhere between
wholesale and retail. Overall, the industry has lost billions incorrectly
guessing the residuals (they actually were lower than predicted.) Of course,
in many cases they subsidized the residuals, making them artificially high
to get the monthly payments down..

Anyway, I'm saving the formula for the future.

Thanks again for the great help.

Regards,
Anthony Giorgianni

860-529-3308
Email: (e-mail address removed)
Web page: www.giorgianni.homestead.com

 

[Harlan Grove]

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.