PMT Causing Confusion

Hi,

I am trying to fix a spreadsheet for a friend and I have come across the function PMT for the first time.

This is the sum.

=-PMT(Q4/(V13*100),Q23,Q22)


Q4 = 10.00
V13 =12
Q23 =33
Q22 = 2274.92

Could someone explain how to do this on a calculator so that I can understand it better please?

Originally Posted by timbo1957

=-PMT(Q4/(V13*100),Q23,Q22)
Q4 = 10.00
V13 =12
Q23 =33
Q22 = 2274.92
Could someone explain how to do this on a calculator so that I can understand it better please?

See the math formula in the PV function help page, and solve for "pmt". In Excel:

=Q22*((1 + (Q4%/V13))^Q23) * (Q4%/V13)/(((1 + (Q4%/V13))^Q23) - 1)

The colored parentheses are intended to help you understand the order of operations. They are redundant in Excel, due to operator precendence rules. So I usually eschew them because they make the formula harder to read, IMHO.

Note that Q4/(V13*100) can be written Q4/100/V13, which can written Q4%/V13. IMHO, it would be better to put 10% into Q4 and simply write Q4/V13. On a calculator, we might enter 0.1 instead of 10%.

But you do not explain what you want to understand; and I do not see how that math formula helps.

If you are wondering why PMT returns a negative value, the reason is: by convention, cash flows are oppositely signed values to distinguish inflows and outflows. But the choice of signs is arbitrary.

One interpretation of your original formula is: it calculates the monthly payment to reduce a loan of 2274.92 at 10% annual interest to zero in 33 months. Is that what you intended?

But in the UK, the annual rate might be stated as an effective (compounded) rate. I believe it depends on the type of loan. If that is the case here, the monthly rate is RATE(12,0,-1,1+Q4%) or (1+Q4%)^(1/12)-1 instead of Q4%/12.

Thanks joeu2004,

I was just puzzled as to how it worked if you did it on a calculator. I tried and failed every time this helps a lot many thanks.