PMT function

[Micros]

I need to calculate a payment on a loan where the customer puts 2 payments in
advance. How would I incorporate the advanced payment sinto the formlula for
PMT?

 

[Gary''s Student]

Reduce the initial balance by the two payments

 

[joeu2004]

I need to calculate a payment on a loan where the customer
puts 2 payments in advance. How would I incorporate the
advanced payment sinto the formlula for PMT?

Kind of a circular question. As you know, the PMT is normally a
periodic amount that will reduce the initial balance (PV) to zero (or
a specified balloon payment) based on the loan term and interest
rate. Making 2 payments of that amount reduces the outstanding
balance and the remaining term of the loan. But the periodic PMT is
not normally recalculated -- unless you are refinancing. (By the way,
normally, if more than one payment is made in advance, the principal
is reduced by the full amount of the extra payments.)

So I wonder what your real question is. Do you want to know the new
term of the loan? Use NPER, reducing the outstanding balance
appropriately.

Note: You said "2 payments in advance". That's ambiguous. If the
normal payment is $1000, which do you mean you paid in advance: a
total of $2000 or $3000?

The first $1000 is normally considered payment of both interest and
principal as if paid on the due date. The remainder ($1000 or $2000)
is considered payment of principal. But check your loan agreement.
It might say something different.

 

[joeu2004]

Errata ....

Note: You said "2 payments in advance". That's ambiguous. If the
normal payment is $1000, which do you mean you paid in advance:
a total of $2000 or $3000?

The first $1000 is normally considered payment of both interest and
principal as if paid on the due date.

Although generally true for interstitial "advance payments", this
probably does not apply to the case you are talking about. All of any
amount paid at the outset of a loan reduces the principal.

What I was trying to say before is: you cannot compute payment based
on the __initial__ loan such that it reduces the __initial__ loan to
zero in n periods, and it reduces the __reduced__ loan to zero in the
same n periods, where the reduced loan is the initial loan less twice
that payment.

However, you __can__ compute a payment that reduces (only) the
__reduced__ loan to zero in n periods such that the reduced loan
amount is the initial loan less twice that payment. That's probably
what you want.

I doubt that you can compute that payment using PMT. However, you can
use the following formula to compute the payment:

=-pv*(1+rt)^n * rt / ( (1+2*rt)*(1+rt)^n - 1 )

where rt = i/n, i = annual interest rate, pv = initial loan amount
(positive number), and n = term of the loan.

Note that that computes payment as a negative number. If you want a
positive number, change "-pv..." to "pv..." (eliminate the unary
minus).

BTW, the factor "2*rt" can be replaced by "k*rt", were k = number of
advance payments.

HTH.

 

[joeu2004]

you can use the following formula to compute
the payment:

=-pv*(1+rt)^n * rt / ( (1+2*rt)*(1+rt)^n - 1 )

where rt = i/n, i = annual interest rate,
pv = initial loan amount (positive number),
and n = term of the loan.

A numerical example might help. Consider a loan of $100,000 over 15
years at 6%. The monthly payment would be about $843.86. Then you
might say that the monthly payment can be reduced to about $829.85 if
the customer makes 2 advance payments totaling $1659.70.

But all you really have done is transform the $100,000 loan into a
loan of $98,340.30. And it always true that if you reduce the
principal of a loan over the same term at the same annual interest
rate, the periodic payments are reduced.

Why limit the customer to "2 advance payments"? Is that just a sales
gimmick?

If you tell the customer that he can reduce the payments by making a
"down payment" of any amount, then you can use the PMT function with a
"pv" of 100000 less the "down payment", as Gary said.

If you want to ensure that the customer pays a minimum down payment
equivalent to 2 payments, you can use my formula to compute that
minimum.

HTH.