Pymt Formula

[John]

Is there a modified pmt formula that will take into
account fixed fees (that do not accrue interest but is
paid off first before principal & interest) as part of
the pymt amt?

 

[Norman Harker]

Hi John!

Use:

Where:
B1 = Loan = 100000
B2 = Rate = 6%
B3 = Term = 10
B4 = Fees = 1500

=PMT(B2/12,B3*12,B1-B4,0,0)
Returns: -1093.55194412527

I've assumed an interpretation of your statement. The fees are paid at
the time that the loan is drawn down. The effect of this is that the
effective borrowing is 100000 less the fees.

I've also assumed that the rate is 6% per annum nominal compounded
monthly (i.e. 6%/12 per month)
Term must match the interest rate and repayment frequency

The negative PMT indicates a payment "out" in return for the payment
"in" of the loan.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Whoops!

Too much Christmas spirit!!

Payments are made on the basis of the amount of loan:

=PMT(B2/12,B3*12,B1,0,0)
Returns: -$1110.21

But you have to pay the fees on draw down and this makes the effective
rate higher because the effective loan is loan less fees.

=RATE(B3*12,-1110.21,B1-B4,0,0,0.1)
Returns: 0.527940141528869%
Equivalent to a nominal rate of 6.34% compared with the quoted rate of
6%

You'll find that the impact of the fees varies with the term of the
loan with much higher impacts being evidenced for short loan terms.
Where a loan is paid off earlier than the contracted period, it can be
shown that the impact of the rate is based upon the actual term rather
than the contractual term.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Don Guillett]

And, a Merry Christmas to the Aussies from the Texans.

 

[Norman Harker]

And to the Texans from the Aussies where Christmas is already here and
Santa had his mince pies and beers.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[John]

Hi Norman,

Thanks for your input, however, I'm trying to determine the following (based on your example). The $1,500.00 (B4 = fees) does not accrue interest but does needs to be paid off first. The original interest rate & term (B2 & B3) will remain as is for the loan amount (B1).

Using the PMT formula based on B1 + B4 results in an overcalculation as the fees do not accrue interest over the the repayment period.
=-PMT(B2/12, B3*12,B1+B4,0,0)
=$1,126.86
Using the PMT formula based on B1 alone results in an undercalculation as the O/S fees are not being accounted for.
=-PMT(B2/12, B3*12,B1,0,0)
=$1,110.21

The first few payment(s) will be applied against the O/S fees, then subsequent payments will be applied against the loan amount & the accrued interest for the rest of the term. I am trying to find if there is a formula that can provide a more accurate payment amount estimate somewhere between $1,110.21 & $1,126.86.


----- Norman Harker wrote: -----

Whoops!

Too much Christmas spirit!!

Payments are made on the basis of the amount of loan:

=PMT(B2/12,B3*12,B1,0,0)
Returns: -$1110.21

But you have to pay the fees on draw down and this makes the effective
rate higher because the effective loan is loan less fees.

=RATE(B3*12,-1110.21,B1-B4,0,0,0.1)
Returns: 0.527940141528869%
Equivalent to a nominal rate of 6.34% compared with the quoted rate of
6%

You'll find that the impact of the fees varies with the term of the
loan with much higher impacts being evidenced for short loan terms.
Where a loan is paid off earlier than the contracted period, it can be
shown that the impact of the rate is based upon the actual term rather
than the contractual term.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi John!

An unusual one and the problem will go round in circles a bit.

My inclination is to calculate the loan payments without taking into
account fees.

Then calculate the time required to pay off the fees at that rate.

Accumulate the loan for that calculated period.

Re-calculate the payments for an adjusted loan term and loan amount.

There will be a small difference but if you repeat the exercise with
the new level of payments that difference should reduce to a nominal
amount.

Happy and Prosperous New Year to you.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Hi Norman,

Thanks for your input, however, I'm trying to determine the

following (based on your example). The $1,500.00 (B4 = fees) does not
accrue interest but does needs to be paid off first. The original
interest rate & term (B2 & B3) will remain as is for the loan amount
(B1).

Using the PMT formula based on B1 + B4 results in an overcalculation

as the fees do not accrue interest over the the repayment period.

=-PMT(B2/12, B3*12,B1+B4,0,0)
=$1,126.86
Using the PMT formula based on B1 alone results in an

undercalculation as the O/S fees are not being accounted for.

=-PMT(B2/12, B3*12,B1,0,0)
=$1,110.21

The first few payment(s) will be applied against the O/S fees, then

subsequent payments will be applied against the loan amount & the
accrued interest for the rest of the term. I am trying to find if
there is a formula that can provide a more accurate payment amount
estimate somewhere between $1,110.21 & $1,126.86.

 

[John]

Thanks again and a happy new year to you too.

-----Original Message-----
Hi John!

An unusual one and the problem will go round in circles a bit.

My inclination is to calculate the loan payments without taking into
account fees.

Then calculate the time required to pay off the fees at that rate.

Accumulate the loan for that calculated period.

Re-calculate the payments for an adjusted loan term and loan amount.

There will be a small difference but if you repeat the exercise with
the new level of payments that difference should reduce to a nominal
amount.

Happy and Prosperous New Year to you.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)... determine the
following (based on your example). The $1,500.00 (B4 = fees) does not
accrue interest but does needs to be paid off first. The original
interest rate & term (B2 & B3) will remain as is for the loan amount
(B1). overcalculation
as the fees do not accrue interest over the the repayment period.
undercalculation as the O/S fees are not being accounted for. O/S fees, then
subsequent payments will be applied against the loan amount & the
accrued interest for the rest of the term. I am trying to find if
there is a formula that can provide a more accurate payment amount
estimate somewhere between $1,110.21 & $1,126.86. this makes the
effective with the quoted
rate of with the term of
the short loan
terms. contracted period, it
can be the actual term
rather Syntax and
Arguments) statement. The fees
are paid effect of this is
that the annum nominal
compounded return for the
payment


.