Pymt Formula

J

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

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
J

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
John said:
Hi Norman,

Thanks for your input, however, I'm trying to determine the
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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.
 
J

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
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

[email protected]... 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


.
Click to expand...
 
Top