Thanks for all the help Sheeloo. Your formula worked a treat.

I tried to rate your post but always got told that this service is not
available.

I give you full marks!!!

Cheers Scott

"Sheeloo" wrote:

Is the N23/100 bit of the calculation changeing 9% to 0.09 before it
It changes 9 to 0.09. If you already have 9% then it will change it to 009%
giving you the wrong answer.

With 9% in N23, PI in I23, and 200000 in L23
=IF(AND(I23="IO",L23<"",N23<""),L23*N23,IF(AND(I 23="PI",L23<""),PMT(N23,30,-L23),""))
will give you $19,467.27

"Scoober" wrote:

The correct answer for $200,000.00 @ 9% on a P&I calculation is $19,467.27

By using the formula:
=IF(AND(I23="IO",L23<"",N23<""),L23*N23,IF(AND(I 23="PI",L23<""),PMT(N23/100,30,-L23),"")) I get an answer of $6,760.

Is the N23/100 bit of the calculation changeing 9% to 0.09 before it
calculates ,30,-L23 ?

Cheers Scott


"Sheeloo" wrote:

=IF(AND(I23="IO",L23<"",N23<""),L23*N23,IF(AND(I 23="PI",L23<""),PMT(N23,30,-L23),""))
with N23 equal to 0.09 and
=IF(AND(I23="IO",L23<"",N23<""),L23*N23,IF(AND(I 23="PI",L23<""),PMT(0.09,30,-L23),""))

should give you the same answer.

Pl. note that if you enter 9 in a cell and then format it as %, it will give
you 900%
You may try
=IF(AND(I23="IO",L23<"",N23<""),L23*N23,IF(AND(I 23="PI",L23<""),PMT(N23/100,30,-L23),""))

In a cell play with
=N23/100
=N23/3000
etc. till you get the number you expect and replace 0.09 by that formula
(without the = sign)