Change the years to calculate a loan

[Scott Miller]

I am trying to complete this formula so by changing the years (in L22) the formula will calculate the correct loan repayment.

=if(e22="",""),PMT(H22/52,$L$22*52,-E22,0)

E22 = Loan size

H22 = Interest rate

$L$22 = years the loan is over

Can anyone see where i am going wrong?

Thank you in advance.

Scott

[#2 · Accepted solution]

The If Statement goes like this

If(condition, value if true, value if false)

 Your formula syntax needs to be fixed

=if(e22="","" ,PMT(H22/52,$L$22*52,-E22,0), 0)

-You have a premature closing bracket for the if statement (removed)

-You don't have a value if the condition is false. I assumed you want a zero, but you might want something else.

-I don't know if your condition works since you haven't told us anything about e22.

[#3]

You don't need the IF function because PMT will treat the NULL string as 0 and not throw an error.  So this should work even if E22 is blank:

=PMT(G22/52,L22*52,-E22)

Although it will display 0 instead of a blank cell.

Read on for tips on avoiding the error you have.

I guess you're getting a message like this when you hit ENTER:

Close the message and the cell will be in "Edit mode" with the cursor in the formula bar after the last bracket.  Press the left arrow to move back through the formula and you will notice a box below the formula bar highlighting the parameter containing the cursor.  When you get back to the second double quotes in your IF statement, you should be seeing something like this:

The IF statement can't display your PMT result because it's outside the brackets of the IF statement.  Delete the bracket and it will work fine.

The number of left and right brackets must always be equal.  Yours has 2 left and 3 right and the last right one is red to highlight that it doesn't have a matching left bracket.  If your last bracket isn't black, delete it, left arrow past the next bracket and notice that it and its partner will change color and be bold for a short time.  That should help your debugging.