FV Function (Excel)

T

Tinman4

I have a loan that I’m trying to calculate which is better 26 partia
payments or 12 full payments. I know the 2 extra payments should reduc
my overall loan amount.

I’m trying to calculate the remaining balance after 12 months using th
following excel function (FV)

FV(8.544%/12,12,250.08,-16782.92) this value results in 15,153.05 whic
is what I expected.

However, when I use the same logic for 26 partial payments

FV(8.544%/12,26,125.04,-16782.92) I get 16,625.1
 
R

Ron Rosenfeld

I have a loan that I’m trying to calculate which is better 26 partial
payments or 12 full payments. I know the 2 extra payments should reduce
my overall loan amount.

I’m trying to calculate the remaining balance after 12 months using the
following excel function (FV)

FV(8.544%/12,12,250.08,-16782.92) this value results in 15,153.05 which
is what I expected.

However, when I use the same logic for 26 partial payments

FV(8.544%/12,26,125.04,-16782.92) I get 16,625.14

Since you are making 26 payments instead of 12, your interest rate per period needs to be 8.544%/26, for the second formula.
 
J

joeu2004

Tinman4 said:
I have a loan that I'm trying to calculate which is
better 26 partial payments or 12 full payments. I know
the 2 extra payments should reduce my overall loan amount.

I'm trying to calculate the remaining balance after 12
months using the following excel function (FV)

FV(8.544%/12,12,250.08,-16782.92)
this value results in 15,153.05 which is what I expected.

However, when I use the same logic for 26 partial payments
FV(8.544%/12,26,125.04,-16782.92) I get 16,625.14

The latter might be FV(8.544%/26,26,125.04,-16782.92).

And that does result in a lower first-year balance of 14,889.17.

Nonetheless, that might not match your reality. And in any case, it might
not result in the "better" loan (a subjective term).

The loan with regular monthly payments will fully amortize in 92 months.

The loan with regular biweekly payments equal to half the monthly payment
will fully amortize in 84 months, and you will pay $731.47 less in total
interest.

However, some lenders charge an extra set-up fees of $275 to $379, and/or
they charge $2 to $5 per transaction -- $356 to $890 for 178 payments. (Ask
your lender.)

Alternatively, it might be "better" to set up a loan with regular monthly
payments and to include an extra monthly payment amount yourself every 12th
payment [1].

In that case, the outstanding balance after 12 payments can be computed by:
=FV(8.544%/12,12,250.08,-16782.92)-250.08

Such a loan will also fully amortize in 84 months, but you will pay "only"
$599.15 less in total interest.

Although that is $132.32 more than the loan with regular biweekly payments
($18.90 more per year), it might be "better" because there are no (or less)
per-transaction fees.

(Your lender might charge a "penalty" for the additional payment amount.
But remember: that would be 6 transactions, not 178. Again, ask your
lender.)

Another advantage: you have the flexibility to suspend the extra payment
amount if you fall on hard times, like losing your job.

If you prefer the discipline of paying the lesser amount biweekly, consider
making a "contract" with yourself to deposit that amount into an existing
savings.


-----
[1] Possibly better: a loan with regular semi-monthly payments, including
an extra monthly payment amount yourself every 24th payment. That loan
fully amortizes in 83 to 84 months, and you save $635.10 in total interest.
The outstanding balance after 24 payments can be computed by:
FV(8.544%/24,24,125.04,-16782.92)-250.08. If that option is available, be
sure to ask your lender about extra fees.
 
J

joeu2004

Clarification....
Alternatively, it might be "better" to set up a loan with regular monthly
payments and to include an extra monthly payment amount yourself every
12th payment [1]. [....]
Although that is $132.32 more than the loan with regular biweekly payments
($18.90 more per year), it might be "better" because there are no (or
less) per-transaction fees.

That is an __average__ of $18.90 more per year.

I was trying to make a point by putting things into perspective. IMHO,
about $20 more per year (on average) is not enough of a difference to
outweigh the subjective benefits.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top