Less Than / Greater Than Formula-Code

[Dave Y]

Hello,

I'm trying to learn Excel Formulas as well as VBA. I have
a spreadsheet that shows fictional loan payments and the
cumulating interest paid over a period of time. I was
able to come up with the formulas using Excel Help for
the monthly loan payment as well as the cumulative
interest amount. The spreadsheet is as follows:
LoanAmt Months Rate MonthlyPmt TotalInterest Under400
$5000 36 .06 -127.11 -475.95

I'm having trouble getting either a formula or VBA code
that will put "TRUE" into the Under400 column if the
value in the MonthlyPmt is less than $400 and "FALSE" if
the value is over $400. I have tried a few things with
VBA code but I keep getting a return of "TRUE" even if
the value is over $400. I thought this would be the easy
part to figure out, but I had less trouble with the other
formulas. Any help will be appreciated. Thank you.

Dave

 

[JE McGimpsey]

So what if PMT is exactly $400?

If the result should be TRUE:

= D1>= -400

if the result should be FALSE:

=D1 > -400

 

[Merlin]

Is it because you actually have -475.95 (neagitive)?
Which of course is less than 400, so you will get a
result of TRUE. If this is the case, your function would
have to be =IF(D1>-400,TRUE,FALSE).

Merlin

 

[Norman Harker]

Hi Dave!

I have a number of problems!

How do you calculate the monthly payment at -$127.11 Using your data
I'm a lot higher with the amount depending upon assumptions on rate
type (APR12 or Annual Effective) and whether payments are in arrears
or in advance.

Total Interest is =(-127.11*36)+5000 which is 424.04

On your layout, assuming Monthly Pmt in D2
=IF(D2<400,TRUE,FALSE)

You can get away with:

=IF(D2<400,TRUE)

The third argument is optional and if omitted it returns FALSE, which
is what you wanted.

You left out what happens if the payment is exactly 400. Unlikely but
too sloppy. I've assumed that you want =400 to be FALSE. If you don't,
then in the two formulas above change < to <=


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[JE McGimpsey]

You can omit the second argument, and indeed, the whole wrapping
function, too:

= D2<400

 

[Norman Harker]

Hi Dave!

My main concern is the data given and getting the signs right. This
totally threw off my original reply.

With payments of -127.11 per month for 36 months, the total payments
are -4575.96

That has got to be wrong! It's not enough to cover the loan of 5000.

Then comes the sign issue:

If I use:
=PMT(6%/12,36,5000,0,0)
Returns: -152.10968725778

Total payments:
-152.10968725778*36
Returns: -5475.94874128008

Add back the loan
-5475.94874128008+5000
Returns: -475.94874128008

If you are interested in returning TRUE for payments made that are
LESS than $400, you need to reverse the comparison because the sign of
the payments are negative.

From this we get:

=D2>-400

This returns TRUE if payments are less than $400 and FALSE if
otherwise.

Although the above logic will answer your question, it is important to
get the underlying calculations sorted out first.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Dave Y]

Hi Guys,

I was using the followin formula to get the monthly
payment: =PPMT(.06\12, 1, 36, A2) which was giving me the
wrong monthly payment. Thanks for the heads up. I wasn't
really paying attention since it is ficticious data,
although I admit, I should have caught that. Thank you
for your reply and your help. I appreciate it greatly.

Dave

 

[Guest]

Hi Merlin,

Thank you for your reply and for the formula. I had to
give myself a big DUH for not realizing that. I
appreciate your help.

Dave

 

[Norman Harker]

Hi Dave!

Thanks for thanks is always appreciated and shows Google searchers
that a solution works. Although that negative causes problems, in my
view it is best to master those problems than to play around with
changing the signs.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.