PMT function in Excel

[Guest]

I am using the PMT function in MS-Excel-2003 with both a PV amount and a FV
amount in the function. I am trying to calculate a monthly loan payment
where the loan amount has a residual after the last payment. The values I am
using are as follows:

Rate = 1.5%
Nper = 24
PV = $120,000.00
FV = $37,000.00
Type = 1

The PMT function yields a $4,629.46 payment amount.
When I calculate this out in a month-by-month table in Excel, the last
payment needs to be almost $500 lower that this monthly payment amount, in
order to result in a residual of $37,000
If a monthly payment amount of $4,612.39 is used in the month-by-month
table, the last payment needs to be only a few cents different n order to
result in a residual of $37,000

(1) Why does the PMT function not calculate the proper monthly amount with
both a PV and FV amount in the function?

(2) Is there a fix, or work-around to get the correct figure?

 

[Guest]

Hi Tim,

Could you post the formula you are using to calculate the month by month
payment.
Also, have you checked the way you are applying your formula against the
Type parameter in PV? This makes a significant difference.

Regards,

OssieMac

 

[Fred Smith]

There is no error in the PMT function. When I build the amortization table, I
get exactly $37,000 at the end of 24 months. The most likely sources of error
are:

1. You aren't replicating the type function properly. A type of 1 means payments
at the beginning of the month. You need to calculate the interest as =(OpenBal -
Pmt) * 1.5%

2. Rounding or typos. PMT returns $4,629.26 (not 4,629.46) with the parameters
given. And, don't round this number, otherwise you'll get small errors at the
end.

 

[joeu2004]

The values I am using are as follows:
Rate = 1.5%
Nper = 24
PV = $120,000.00
FV = $37,000.00
Type = 1
[....]
(1) Why does the PMT function not calculate the proper monthly
amount with both a PV and FV amount in the function?
(2) Is there a fix, or work-around to get the correct figure?

I get the right answer, no matter what assumptions I make below.

The PMT function yields a $4,629.46 payment amount.

I presume that's a typo. I get 4629.26 when the month rate is 1.5%
and payment is "in advance" (type=1).

Alternatively, perhaps the monthly rate is between 1.500244% and
1.500256% (rounded), namely =rate(24,4629.46,-120000,37000).

When I calculate this out in a month-by-month table in Excel,
the last payment needs to be almost $500 lower that this
monthly payment amount, in order to result in a residual of $37,000

If a monthly payment amount of $4,612.39 is used in the month-by-month
table, the last payment needs to be only a few cents different n order to
result in a residual of $37,000

I cannot duplicate your numbers. Please post the structure and
formulas for your "month-by-month table" (aka amortization schedule).
This is how I would structure it (without frills):

B1: 4629.46 or =pmt(C1,24,-120000,37000,1) [monthly
payment]
C1: =rate(24,4629.46,-120000,37000,1) or 1.500250% [monthly interest]
E1: 120000 [initial loan amount]

A2: =A1+1 [payment number]
B2: =B1
C2: =(E1-B2)*$C$1 [monthly interest]
D2: =E1-E2 [monthly principal]
E2: =E1+C2-B2 [monthly ending balance]

Copy A2:E2 down through A25:E25.

Notes:

1. Technically, B1 should be =roundup(pmt(...),2). Then:

B2: =if(or(A2=24,E1+C2<B1), roundup(E1+C2,2), B1)
E2: =max(0,E1+C2-B2)

Thus, the last payment will likely be less than the other monthly
payments.

2. Arguably, monthly interest might also be rounded (up?). That
depends on the lender. Obviously, the lender rounds (up?) the amount
of total interest reported on the 1099-INT at the end of year (for US
loans). But that does not necessarily dictate the lender's internal
computations.

3. Are you sure that the payment is "in advance", not "in
arrears" (type=0 or omitted)? For most loans that I've seen, payment
is "in arrears", with "prepaid interest" added to the closing cost to
cover the short (irregular) period at the beginning. In that case,
interest (C2) is =E1*$C$1, copied down. But that does not result in
the unusual numbers that you post ("almost $500" and $4,612.39)

 

[Guest]

Thanks for the reply OssieMac,

Attached is a link to my Excel file which contains the PMT formula, and the
table I used to verify the formula.

http://www7.sendthisfile.com/d.jsp?t=hx8uyf7HXJkcARsCjkUKJjV8

In this Excel file you will find 2 Tabs, one Tab where the PMT Type = 1 (&
PMT result is wrong) and one Tab where the PMT type = 0 (where PMT results is
correct)

 

[Guest]

JoeU2004 - thanks for the Reply

Attached is a link to my Excel file which contains the PMT formula, and the
table I used to verify the formula.

http://www7.sendthisfile.com/d.jsp?t=hx8uyf7HXJkcARsCjkUKJjV8

In this Excel file you will find 2 Tabs, one Tab where the PMT Type = 1 (&
PMT result is wrong) and one Tab where the PMT type = 0 (where PMT results is
correct)

The values I am using are as follows:
Rate = 1.5%
Nper = 24
PV = $120,000.00
FV = $37,000.00
Type = 1
[....]
(1) Why does the PMT function not calculate the proper monthly
amount with both a PV and FV amount in the function?
(2) Is there a fix, or work-around to get the correct figure?

I get the right answer, no matter what assumptions I make below.

The PMT function yields a $4,629.46 payment amount.

I presume that's a typo. I get 4629.26 when the month rate is 1.5%
and payment is "in advance" (type=1).

Alternatively, perhaps the monthly rate is between 1.500244% and
1.500256% (rounded), namely =rate(24,4629.46,-120000,37000).

When I calculate this out in a month-by-month table in Excel,
the last payment needs to be almost $500 lower that this
monthly payment amount, in order to result in a residual of $37,000

If a monthly payment amount of $4,612.39 is used in the month-by-month
table, the last payment needs to be only a few cents different n order to
result in a residual of $37,000

I cannot duplicate your numbers. Please post the structure and
formulas for your "month-by-month table" (aka amortization schedule).
This is how I would structure it (without frills):

B1: 4629.46 or =pmt(C1,24,-120000,37000,1) [monthly
payment]
C1: =rate(24,4629.46,-120000,37000,1) or 1.500250% [monthly interest]
E1: 120000 [initial loan amount]

A2: =A1+1 [payment number]
B2: =B1
C2: =(E1-B2)*$C$1 [monthly interest]
D2: =E1-E2 [monthly principal]
E2: =E1+C2-B2 [monthly ending balance]

Copy A2:E2 down through A25:E25.

Notes:

1. Technically, B1 should be =roundup(pmt(...),2). Then:

B2: =if(or(A2=24,E1+C2<B1), roundup(E1+C2,2), B1)
E2: =max(0,E1+C2-B2)

Thus, the last payment will likely be less than the other monthly
payments.

2. Arguably, monthly interest might also be rounded (up?). That
depends on the lender. Obviously, the lender rounds (up?) the amount
of total interest reported on the 1099-INT at the end of year (for US
loans). But that does not necessarily dictate the lender's internal
computations.

3. Are you sure that the payment is "in advance", not "in
arrears" (type=0 or omitted)? For most loans that I've seen, payment
is "in arrears", with "prepaid interest" added to the closing cost to
cover the short (irregular) period at the beginning. In that case,
interest (C2) is =E1*$C$1, copied down. But that does not result in
the unusual numbers that you post ("almost $500" and $4,612.39)

 

[Fred Smith]

I'm sorry you couldn't figure out the problem from the responses given.

Your problem (with Type=1) is that you have 24 payments, but you have only 23
periods with interest charged. Add one more month of interest, and you will get
exactly $37,000.

My other recommendation is you redesign the spreadsheet to avoid the disjointed
periods. When Type=1, calculate interest as IntRate * (Bal - Pmt).

--
Regards,
Fred

JoeU2004 - thanks for the Reply

Attached is a link to my Excel file which contains the PMT formula, and the
table I used to verify the formula.

http://www7.sendthisfile.com/d.jsp?t=hx8uyf7HXJkcARsCjkUKJjV8

In this Excel file you will find 2 Tabs, one Tab where the PMT Type = 1 (&
PMT result is wrong) and one Tab where the PMT type = 0 (where PMT results is
correct)

The values I am using are as follows:
Rate = 1.5%
Nper = 24
PV = $120,000.00
FV = $37,000.00
Type = 1
[....]
(1) Why does the PMT function not calculate the proper monthly
amount with both a PV and FV amount in the function?
(2) Is there a fix, or work-around to get the correct figure?

I get the right answer, no matter what assumptions I make below.

The PMT function yields a $4,629.46 payment amount.

I presume that's a typo. I get 4629.26 when the month rate is 1.5%
and payment is "in advance" (type=1).

Alternatively, perhaps the monthly rate is between 1.500244% and
1.500256% (rounded), namely =rate(24,4629.46,-120000,37000).

When I calculate this out in a month-by-month table in Excel,
the last payment needs to be almost $500 lower that this
monthly payment amount, in order to result in a residual of $37,000

If a monthly payment amount of $4,612.39 is used in the month-by-month
table, the last payment needs to be only a few cents different n order to
result in a residual of $37,000

I cannot duplicate your numbers. Please post the structure and
formulas for your "month-by-month table" (aka amortization schedule).
This is how I would structure it (without frills):

B1: 4629.46 or =pmt(C1,24,-120000,37000,1) [monthly
payment]
C1: =rate(24,4629.46,-120000,37000,1) or 1.500250% [monthly interest]
E1: 120000 [initial loan amount]

A2: =A1+1 [payment number]
B2: =B1
C2: =(E1-B2)*$C$1 [monthly interest]
D2: =E1-E2 [monthly principal]
E2: =E1+C2-B2 [monthly ending balance]

Copy A2:E2 down through A25:E25.

Notes:

1. Technically, B1 should be =roundup(pmt(...),2). Then:

B2: =if(or(A2=24,E1+C2<B1), roundup(E1+C2,2), B1)
E2: =max(0,E1+C2-B2)

Thus, the last payment will likely be less than the other monthly
payments.

2. Arguably, monthly interest might also be rounded (up?). That
depends on the lender. Obviously, the lender rounds (up?) the amount
of total interest reported on the 1099-INT at the end of year (for US
loans). But that does not necessarily dictate the lender's internal
computations.

3. Are you sure that the payment is "in advance", not "in
arrears" (type=0 or omitted)? For most loans that I've seen, payment
is "in arrears", with "prepaid interest" added to the closing cost to
cover the short (irregular) period at the beginning. In that case,
interest (C2) is =E1*$C$1, copied down. But that does not result in
the unusual numbers that you post ("almost $500" and $4,612.39)

 

[joeu2004]

Attached is a link to my Excel file which contains the PMT
formula, and the table I used to verify the formula.
http://www7.sendthisfile.com/d.jsp?t=hx8uyf7HXJkcARsCjkUKJjV8

In this Excel file you will find 2 Tabs, one Tab where the PMT
Type = 1 (& PMT result is wrong)

The mistake in the Type=1 worksheet is that you are not computing
interest correctly. For example, you have zero interest in the first
period. I used to do the same thing. But in fact, interest is
computed based on the previous balance (initial loan amount) less the
payment, as indicated for C2 in my previous posting. The proof of
this can be demonstrated with the following formula:

=fv(1.5%, 1, 4629.26, -120000, 1)

That computes the outstanding balance after the first payment "in
advance". Note that the result is 117,101.30 (rounded), not
115,370.74 (120000 - 4629.26).

As I noted in my previously posting (adapted to your worksheet),
interest should be computed as follows in C24 and copied down through
C47:

C24: =(E23-D24)*$C$11

(Also note that the payment is indeed 4629.26, rounded, in your
worksheet, not 4629.46 as you wrote in your initial posting.)

PS: The annual interest rate is 18% (12*1.5%), not 19.5620%, which
you compute in C12 by (1+1.5%)^12-1. The latter computes the APY of
an investment, not the APR of a loan.

and one Tab where the PMT type = 0 (where PMT results is correct)

Whether the payment is "in advance" or "in arrears" depends on the
terms of the loan. In both cases, the financial math should yield the
correct results.