Principle and interest formula - Help

[Scoober]

Hi,

Can some one help me with the following.

I have had this formula working in a spredsheet for a while now and have
realsied it has a limitation.

=IF(AND(I23="IO",L23<>"",N23<>""),L23*N23,IF(AND(I23="PI",L23<>""),PMT(0.09,30,-L23),""))

As you can see the Principle & Interest is worked out at a static 9%. Can
someone show me the alteration within this formula (everything else in the
formula is working perfectly) required for the P&I part of the formula to
read the interest rate placed in N23 (as the formula does for the interest
only formula). ?

 

[Sheeloo]

Try
=IF(AND(I23="IO",L23<>"",N23<>""),L23*N23,IF(AND(I23="PI",L23<>""),PMT(N23,30,-L23),""))

will give you the same result as your formula below if N23 is equal to 0.09

 

[Scoober]

Thanks Sheeloo,

The problem here is that the interest rate (so it's understandable to my
clients is inputted in N23 as 6.65%. So by adding (N23,30,-L23),"")) it
accually multiplies 6.65x30-L23 where I need the formula to read N23 as
0.0665 and then complete the rest of the formula ( ,30-L23),""))

How does the interest only formula calculate it correctly but not the P&I
formula?

I hope this makes sense?

 

[Sheeloo]

=IF(AND(I23="IO",L23<>"",N23<>""),L23*N23,IF(AND(I23="PI",L23<>""),PMT(N23,30,-L23),""))
with N23 equal to 0.09 and
=IF(AND(I23="IO",L23<>"",N23<>""),L23*N23,IF(AND(I23="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(I23="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)

 

[Scoober]

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(I23="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]

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(I23="PI",L23<>""),PMT(N23,30,-L23),""))
will give you $19,467.27

 

[Scoober]

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