PMT Function Help

[KatelynnP]

Hello everyone, I am trying to use the PMT Function, but everytime I use the formula I come out with a negative answer in red and in () and when I try to use it in a formula I don't seem to be getting the correct answer. Can anyone please explain what I am doing wrong when trying to do this?
Thanks In Advance!
-Katelynn

[MarvinP]

Hi KatelynnP and welcome to the forum,

I'm looking at:
https://support.microsoft.com/en-us/...0-214433fa6441
and it looks like the PMT answer is negative.

Also watch https://www.youtube.com/watch?v=2fXX-KfONu0 where the answer is negative!!

Maybe your thinking is just backwards. A Payment you make is money going away from you so it is negative???

[curiouscat408]

@Katelynn.... In order for us to explain specifically what you are doing wrong, we need to see a concrete example. You should provide the formula that you use, and the values of any referenced cells.

The following is a general explanation that might or might not be relevant.

-----

Most Excel financial functions use the convention of signed cash flows.

The signs (positive and negative) must be the opposite for inflows and outflows.

However, the sign that you choose for inflows, for example, is arbitary.

The form of the PMT function is PMT(rate, nper, pv, fv, type).

"Nper" is the number of payment periods.

"Rate" must be per payment period. Often, that means converting an annual rate to a periodic rate; for example, a monthly rate for monthly payments.

No, the PMT function result does not have to be negative.

As I noted, the choice of signs is arbitrary. I usually choose the signs of my cash flows so that my use of financial functions returns positive values.

(Off-hand, the only exceptions are the functions CUMIPMT and CUMPRINC. Those functions require a positive "pv", and they always return negative results.)

-----

For example, suppose we borrow $100,000 at 1.2% annual interest for 15 years with monthly payments.

The monthly payment is:

=PMT(1.2%/12, 15*12, -100000)

which returns 607.331729396173. (Usually, I round up to $607.34.)

It is a positive value because I use a negative value for the loan amount.

-----

But suppose that we are willing to pay $607.34 monthly, and we want to know the maximum loan amount that we can afford.

The loan amount is:

=PV(1.2%/12, 15*12, -607.34)

which returns 100,001.361793457.

Again, it is a positive value because I use a negative value for the payment.

-----

If you find the choice of signs to be confusing, of course you can simply change the sign of a function result with a unary minus operator. For example:

=-PMT(1.2%/12, 15*12, 100000)

That assumes that you always use negative cash flows for amounts that you pay out and positive cash flows for amounts that you receive.