What am I doing wrong with PMT function?

[Marc]

I have searched the net and formated the function like all the examples but
the payment is way out of line.

In cell L57 I have the annual interest rate, in M57 the numbers of years for
the loan and K57 is the principal.

=PMT(L57/M57*12,M57*12,-K57)

The principal is $714,136.63 the rate is 8% and the terms is 7 years but it
returns a paymetns of $9,793,873.81. This is what it should be from the
online calculators I've used $14,480.11.

What am I doing wrong?

Marc

 

[R. Choate]

Without seeing your setup, I would ask if you are multiplying by 12 because you have a monthly int amount and are trying to convert?
Usually people are dividing by 12 to get a monthly pmt.. Also, I would use parenthesis around my division to control which math
comes first. Finally, I would have all of the elements of the function in individual cells so it would be easier to troubleshoot.

HTH
--
RMC,CPA


I have searched the net and formated the function like all the examples but
the payment is way out of line.

In cell L57 I have the annual interest rate, in M57 the numbers of years for
the loan and K57 is the principal.

=PMT(L57/M57*12,M57*12,-K57)

The principal is $714,136.63 the rate is 8% and the terms is 7 years but it
returns a paymetns of $9,793,873.81. This is what it should be from the
online calculators I've used $14,480.11.

What am I doing wrong?

Marc

 

[via135]

hi!

=PMT((L57/12)%,(M57*12),K57)

-via135

I have searched the net and formated the function like all the examples
but
the payment is way out of line.

In cell L57 I have the annual interest rate, in M57 the numbers of
years for
the loan and K57 is the principal.

=PMT(L57/M57*12,M57*12,-K57)

The principal is $714,136.63 the rate is 8% and the terms is 7 years
but it
returns a paymetns of $9,793,873.81. This is what it should be from
the
online calculators I've used $14,480.11.

What am I doing wrong?

Marc

 

[CaptainQuattro]

I have searched the net and formated the function like all the examples
but
the payment is way out of line.

In cell L57 I have the annual interest rate, in M57 the numbers of
years for
the loan and K57 is the principal.

=PMT(L57/M57*12,M57*12,-K57)

The principal is $714,136.63 the rate is 8% and the terms is 7 years
but it
returns a paymetns of $9,793,873.81. This is what it should be from
the
online calculators I've used $14,480.11.

What am I doing wrong?

Marc

The main problem that I see with your formula is that if you want to
divide the interest rate by the number of months, you need to enclose
the expression M57*12 in brackets, i.e. =PMT(L57/(M57*12),M57*12,-K57)

 

[R. Choate]

If you try =PMT(L57/12,M57*12,-K57) then you will get a pmt of $11,130.69. You were multiplying the # of years by 12 and you were
dividing the interest rate by that number, giving you a bad interest rate. Everything else looks fine, but it doesn't agree with the
# you got manually of $14,480.11. I think the 11K answer is correct for the terms you gave. Try testing it again.

--
RMC,CPA


I have searched the net and formated the function like all the examples but
the payment is way out of line.

In cell L57 I have the annual interest rate, in M57 the numbers of years for
the loan and K57 is the principal.

=PMT(L57/M57*12,M57*12,-K57)

The principal is $714,136.63 the rate is 8% and the terms is 7 years but it
returns a paymetns of $9,793,873.81. This is what it should be from the
online calculators I've used $14,480.11.

What am I doing wrong?

Marc

 

[Marc]

Thanks so far. It's closer. This is my formula now
=PMT((L57/M57*12)%,(M57*12),-K57) but it returns $137,117.87. If you divide
this by 12 you get $11,426.49. If I use the loan calculator at this site
http://www.tcalc.com/tvwww.dll?CalcLoan I get 14,480.12.

Marc

If you try =PMT(L57/12,M57*12,-K57) then you will get a pmt of

$11,130.69. You were multiplying the # of years by 12 and you were

dividing the interest rate by that number, giving you a bad interest rate.

Everything else looks fine, but it doesn't agree with the

# you got manually of $14,480.11. I think the 11K answer is correct for

the terms you gave. Try testing it again.

 

[Guest]

In cell L57 I have the annual interest rate, in M57 the
numbers of years for the loan and K57 is the principal.

This is my formula now
=PMT((L57/M57*12)%,(M57*12),-K57)

You formula is completely wrong. The correct formula for
the terms stated earlier is simply:

=PMT(L57/12, M57*12, -K57)

(This assumes that L57 contain "8%" or "0.08", without quotes.)

That results in $11,130.69.

but it returns $137,117.87. If you divide this by 12 you get
$11,426.49.

Pure coincidence, as near as I can tell. Taken the PMT() result
and dividing by 12 makes no sense to me.

If I use the loan calculator at this site
http://www.tcalc.com/tvwww.dll?CalcLoan I get 14,480.12.

It might help if you specify the parameters and options you enter.

I get $11,130.69 -- same as PMT() -- from that calculator when
I enter the following:

Loan start date: 05-06-2006
First payment date: 06-06-2006
Loan amount: $714,136.63
Number of payments: 84
Payment period: Monthly
Interest rate: 8%
Compounding period: Monthly
Loan type: Fixed Rate

All other fields are "don't cares". I left them as-is.

If you entered different values, you are using different loan
terms. Which is right depends on the actual loan terms.