Principle and interest formula - Help

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(I 23="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). ?

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

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

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(I 23="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). ?

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" wrote:

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

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

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(I 23="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). ?

=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)

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)

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)

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)