Principle and interest formula - Help

  • Thread starter Thread starter Scoober
  • Start date Start date
S

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). ?
 
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
 
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
 
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?
 
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?
 
=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)
 
=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)
 
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
 
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
 
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
 
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
 
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
 
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
 
Back
Top