Should I use IF here? How?

[mynew06]

Hi all. Kind of a basic user of excel here, hoping someone more advanced than I (not saying much) on this forum can help me with a formula to us in a worksheet to calculates the appropriate transfer taxes when you plug in the purchase price in, say cell B1. Following is the tax rate structure: (Thanks in advance for any help!!)

(a)$0.90 for each $500.00 of purchase price or fractional part thereof recited in the deed that is not in excess of $550,000.00, except that in the case of a conveyance or transfer of property for which the total consideration recited in the deed does not exceed $350,000.00, no general purpose fee shall be imposed;

(b)$1.40 for each $500.00 of purchase price or fractional part thereof in excess of $550,000.00 but not in excess of $850,000.00 recited in the deed;

(c)$1.90 for each $500.00 of purchase price or fractional part thereof in excess of $850,000.00 but not in excess of $1,000,000.00 recited in the deed; and

(d)$2.15 for each $500.00 of purchase price or fractional part thereof in excess of $1,000,000.00 recited in the deed

[sloth]

How about creating a tax rate table and then using vlookup to return
the appropriate tax based on purchase price.

Tax Table

price (A1) tax (B1)
00.00 $0.00
350.01 $0.90
550.01 $1.40
850.01 $1.90
1,000.01 $2.15

price value (B8)
tax x

x= vlookup(B8,A2:B6,2,True)

This equation looks up the price in B8 in the tax table defined by
A2:B6 and returns the 2nd column tax amount. Since it range lookup is
set to True, if there is no exact match, vlookup will match against
largest price in column A less than the price entered in B8.

Hope this helps.

[[email protected]]

mynew06 wrote:
> Subject: Re: Should I use IF here? How?

There are many ways to do the same thing. You certainly could use an
IF() function. I would do the following. But first....

> (a)$0.90 for each $500.00 of purchase price or fractional part thereof
> recited in the deed that is not in excess of $550,000.00, except that
> in the case of a conveyance or transfer of property for which the total
> consideration recited in the deed does not exceed $350,000.00, no
> general purpose fee shall be imposed

Do you really mean that no fee is assessed on the first $350,000? It
seems odd that no fee is assessed on a deed of not more than $350,000,
but a fee is assessed on the first $350,000 if the deed exceeds that
amount.

If no fee is assessed on the first $350,000, you might use the
following formula:

=0.90*roundup(max(0,B1-350000)/500,0) +
0.50*roundup(max(0,B1-550000)/500,0)
+ 0.50*roundup(max(0,B1-850000)/500,0) +
0.25*roundup(max(0,B1-1000000)/500,0)

If you truly mean to assess fees on the first $350,000 when the deed
exceeds that amount, change "B1-350000" to simply "B1".


----- original posting -----

mynew06 wrote:
> Hi all. Kind of a basic user of excel here, hoping someone more advanced
> than I (not saying much) on this forum can help me with a formula to us
> in a worksheet to calculates the appropriate transfer taxes when you
> plug in the purchase price in, say cell B1. Following is the tax rate
> structure: (Thanks in advance for any help!!)
>
> (a)$0.90 for each $500.00 of purchase price or fractional part thereof
> recited in the deed that is not in excess of $550,000.00, except that
> in the case of a conveyance or transfer of property for which the total
> consideration recited in the deed does not exceed $350,000.00, no
> general purpose fee shall be imposed;
>
> (b)$1.40 for each $500.00 of purchase price or fractional part thereof
> in excess of $550,000.00 but not in excess of $850,000.00 recited in
> the deed;
>
> (c)$1.90 for each $500.00 of purchase price or fractional part thereof
> in excess of $850,000.00 but not in excess of $1,000,000.00 recited in
> the deed; and
>
> (d)$2.15 for each $500.00 of purchase price or fractional part thereof
> in excess of $1,000,000.00 recited in the deed
>
>
> --
> mynew06

[mynew06]

Thanks very much to both of you for your replies. Joeu2004, your formula worked perfectly, and did exactly what I was looking for. I've got to learn these formulas a little better. (And to answer your question about no fees below $350K, this fee schedule was only part of the transfer fees. There are others, believe me! These particular fees only cover property sold in excess of $350,000. There are others still assessed for property under $350K.)

Thanks again!

[mynew06]

I re-read it. You're right, joeu2004. The first $350K is still assessed, it's just that any property not in excess of $350K is exempt from this fee.
Thanks again...

[mynew06]

Ok, it works, but now I can't figure out how/why. Where did you get the .50, .50, and .25 from and get it to work? (Where did the 1.40, 1.90, and 2.15 go from the fee schedule?) Unfortunately, it seems I haven't learned enough to be able to duplicate on my own using a different fee schedule if need be...

mynew06 wrote:
> Subject: Re: Should I use IF here? How?

There are many ways to do the same thing. You certainly could use an
IF() function. I would do the following. But first....

> (a)$0.90 for each $500.00 of purchase price or fractional part thereof
> recited in the deed that is not in excess of $550,000.00, except that
> in the case of a conveyance or transfer of property for which the total
> consideration recited in the deed does not exceed $350,000.00, no
> general purpose fee shall be imposed

Do you really mean that no fee is assessed on the first $350,000? It
seems odd that no fee is assessed on a deed of not more than $350,000,
but a fee is assessed on the first $350,000 if the deed exceeds that
amount.

If no fee is assessed on the first $350,000, you might use the
following formula:

=0.90*roundup(max(0,B1-350000)/500,0) +
0.50*roundup(max(0,B1-550000)/500,0)
+ 0.50*roundup(max(0,B1-850000)/500,0) +
0.25*roundup(max(0,B1-1000000)/500,0)

If you truly mean to assess fees on the first $350,000 when the deed
exceeds that amount, change "B1-350000" to simply "B1".


----- original posting -----

mynew06 wrote:
> Hi all. Kind of a basic user of excel here, hoping someone more advanced
> than I (not saying much) on this forum can help me with a formula to us
> in a worksheet to calculates the appropriate transfer taxes when you
> plug in the purchase price in, say cell B1. Following is the tax rate
> structure: (Thanks in advance for any help!!)
>
> (a)$0.90 for each $500.00 of purchase price or fractional part thereof
> recited in the deed that is not in excess of $550,000.00, except that
> in the case of a conveyance or transfer of property for which the total
> consideration recited in the deed does not exceed $350,000.00, no
> general purpose fee shall be imposed;
>
> (b)$1.40 for each $500.00 of purchase price or fractional part thereof
> in excess of $550,000.00 but not in excess of $850,000.00 recited in
> the deed;
>
> (c)$1.90 for each $500.00 of purchase price or fractional part thereof
> in excess of $850,000.00 but not in excess of $1,000,000.00 recited in
> the deed; and
>
> (d)$2.15 for each $500.00 of purchase price or fractional part thereof
> in excess of $1,000,000.00 recited in the deed
>
>
> --
> mynew06

[[email protected]]

mynew06 wrote:
> Ok, it works, but now I can't figure out how/why. Where did you get the
> .50, .50, and .25 from and get it to work? (Where did the 1.40, 1.90,
> and 2.15 go from the fee schedule?)

I anticipated your question. I was worried that the formula was a
little too tricky as written. I think you might have understood it
more easily (but I'm sure some explanation is still needed) if I had
written it as:

=0.90*roundup(max(0,B1-350000)/500,0) +
(1.40-0.90)*roundup(max(0,B1-550000)/500,0) +
(1.90-1.40)*roundup(max(0,B1-850000)/500,0) +
(2.15-1.90)*roundup(max(0,B1-1000000)/500,0)

(Sorry, I am still not clear on what you intend for property values in
excess of $350,000. For example, if the value is $400,000, would the
fee be $720 or $90?)

max(0,B1-350000) returns zero if B1 is less than $350,000; thus, no fee
is charged. If B1 exceeds $350,000, max(0,B1-350000) returns
B1-350000; that is, the amount in excess of $350,000. Thus, the $0.90
is charge only for the value in excess of $350,000.

roundup(max(...)/500,0) returns the number of "each $500 or fractional
part thereof". That is, it returns the largest integral times that
$500 divides into the value. For example, if B1 is $600,400,
roundup(...) returns 101 because (600400 - 550000) / 500 is 100.8.

The key to the structure of the formula is to recognize that when you
say that $0.90 is charged for the value under $550,000 and $1.40 is
charged for the value between $550,000 and $850,000, that is
algebraically the same as saying that $0.90 is charged for the entire
value (in excess of $350,000 in the formula above), and an additional
$0.50 ($1.40 - $0.09) is charged for the value over $550,000. Yet an
additional $0.50 is charged for the value over $850,000, and an
additional $0.25 is charged for the value over $1,000,000.

Again, the use of max(0,...) ensures that for values under those
breakpoints, we compute $0.90, $0.50 or $0.25 times zero, which is
zero.

Consider the example of $600,400. You might compute $0.90 for each
$500 of the first $200,000 in excess of $350,000 and $1.40 for each
$500 of the remaining $50,400. That is 0.90*400 + 1.40*101. But that
is the same as 0.90*501 + 0.50*101 [1]; that is: $0.90 for each $500
of the total value in excess of $350,000, and an additional $0.50 for
each $500 of the $50,400 in excess of $550,000.

(Of course, you might dispense with the phrase "in excess of $350,000"
if that is not your intent. That might still be part of my confusion
about your requirements. I continue to think it is odd to exempt the
fee on the first $350,000, but only if the total value does not exceed
$350,000, which is how I am (perhaps mistakenly) interpreting you when
you say: "The first $350K is still assessed, it's just that any
property not in excess of $350K is exempt from this
fee.")

Hope that helps. If not, feel free to ask for further explanation.


-----
[1] 0.90*400 + 1.40*101 = 0.90*400 + (0.90 + 0.50)*101
= 0.90*400 + 0.90*101 + 0.50*101 = 0.90*501 + 0.50*101

[mynew06]

mynew06 wrote:
> Ok, it works, but now I can't figure out how/why. Where did you get the
> .50, .50, and .25 from and get it to work? (Where did the 1.40, 1.90,
> and 2.15 go from the fee schedule?)

I anticipated your question. I was worried that the formula was a
little too tricky as written. I think you might have understood it
more easily (but I'm sure some explanation is still needed) if I had
written it as:

=0.90*roundup(max(0,B1-350000)/500,0) +
(1.40-0.90)*roundup(max(0,B1-550000)/500,0) +
(1.90-1.40)*roundup(max(0,B1-850000)/500,0) +
(2.15-1.90)*roundup(max(0,B1-1000000)/500,0)

(Sorry, I am still not clear on what you intend for property values in
excess of $350,000. For example, if the value is $400,000, would the
fee be $720 or $90?)

Thanks so much for the detailed explanation. I'm going to go over it later when I have a few more minutes so I can do this kind of thing on my own.

In the meantime, to answer your question: If, as in your example above, the value is $400,000, the fee would be $720. The first $550K is at the .90 rate. But any property with a value <$350K is not subject at all to this entire fee schedule. (It is subject to a different fee schedule that I didn't post in my question.)

Does this make sense?

[[email protected]]

mynew06 wrote:
> [email protected] Wrote:
> > =0.90*roundup(max(0,B1-350000)/500,0) +
> > (1.40-0.90)*roundup(max(0,B1-550000)/500,0) +
> > (1.90-1.40)*roundup(max(0,B1-850000)/500,0) +
> > (2.15-1.90)*roundup(max(0,B1-1000000)/500,0)
> [....]
> In the meantime, to answer your question: If, as in your example above,
> the value is $400,000, the fee would be $720.

Okay, that is what you said all along. Sorry for being so "dense"
about it.

So, are you clear on how to correct the formula above? It is not as
simple as I suggested previously. You could use a simple IF()
function. Another way is:

=0.90*(B1>350000)*roundup(B1/500,0) +....

If B1 does not exceed 350000, "B1>350000" is treated as zero; thus, you
would have 0.90*0*roundup(...), which is zero. If B1 exceeds 350000,
"B1>350000" is treated as one, and you would have 0.90*1*roundup(...).

Note that "max(0,B1-350000)" was replaced with simply "B1".

[mynew06]

mynew06 wrote:
> [email protected] Wrote:
> > =0.90*roundup(max(0,B1-350000)/500,0) +
> > (1.40-0.90)*roundup(max(0,B1-550000)/500,0) +
> > (1.90-1.40)*roundup(max(0,B1-850000)/500,0) +
> > (2.15-1.90)*roundup(max(0,B1-1000000)/500,0)
> [....]
> In the meantime, to answer your question: If, as in your example above,
> the value is $400,000, the fee would be $720.

Okay, that is what you said all along. Sorry for being so "dense"
about it.

So, are you clear on how to correct the formula above? It is not as
simple as I suggested previously. You could use a simple IF()
function. Another way is:

=0.90*(B1>350000)*roundup(B1/500,0) +....

If B1 does not exceed 350000, "B1>350000" is treated as zero; thus, you
would have 0.90*0*roundup(...), which is zero. If B1 exceeds 350000,
"B1>350000" is treated as one, and you would have 0.90*1*roundup(...).

Note that "max(0,B1-350000)" was replaced with simply "B1".

Of the two of us, I'd say you're definitely NOT the "dense" one here. Anyway, I had already adjusted your formula to reflect the structure of the fee schedule using IF (I did: IF(B1>350000,[your formula],0)
At least I was able to figure that out!

Thanks again...

[[email protected]]

mynew06 wrote:
> Anyway, I had already adjusted your formula to reflect the structure of
> the fee schedule using IF (I did: IF(B1>350000,[your formula],0)
> At least I was able to figure that out!

Bingo! Good job!