NJ Realty Transfer Fee

[kdoshi]

I'm probably between a basic to intermediate level of user but I really need help putting together a formula for calculating a NJ real estate transfer fee based on a sale price. There are websites with calculators but I need to integrate a formula into a spreadsheet. Here's a sample calculator:

Here's how the fee is calculated:

Any help would be appreciated!!

- Kunal

[kdoshi]

Wouldn't let me post links for some reason:

[alansidman]

If you need to post a link, since you have less than 10 posts, you will need to type it out manually and not copy and paste.

[kdoshi]

lol sorry!

[kdoshi]

for the sample calculator just google NJ Realty Transfer fee. The 2nd link is the 2nd link when you google NJ Realty Transfer Fee.

[JeteMc]

Is this the site? https://www.nj.gov/treasury/taxation/realty_over.shtml
It may be helpful if we could see the layout of the spreadsheet, instructions are given in the banner at the top of the page.
Let us know if you have any questions.

[kdoshi]

Is this the site?
It may be helpful if we could see the layout of the spreadsheet, instructions are given in the banner at the top of the page.
Let us know if you have any questions.

Thats the correct website!

The spreadsheet has a lot of other expenses & data about the property but the calculator just needs to take the sales price and run it through a calculator. That should give us a transfer tax amount just based off the sales price

[Pepe Le Mokko]

Administrative note
Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
Thanks

[Pepe Le Mokko]

Administrative Note:

Please don't quote entire posts unnecessarily. They clutter threads and make them hard to read.
Use the "Quick reply" instead
Thanks

[kdoshi]

Got it, just updated my profile. I'd like to ideally use this on a google sheets workbook that I share with my company.

[kdoshi]

Here's a concise version of how the calculation should work:

A. REALTY TRANSFER FEES IMPOSED ON SELLER, TOTAL CONSIDERATION NOT IN EXCESS OF $350,000
$2.00/$500 of consideration not in excess of $150,000;
$3.35/$500 of consideration in excess of $150,000 but not in excess of $200,000;
$3.90/$500 of consideration in excess of $200,000 but not in excess of $350,000.

REALTY TRANSFER FEES IMPOSED ON SELLER, TOTAL CONSIDERATION IN EXCESS OF $350,000
$2.90/$500 of consideration not in excess of $150,000;
$4.25/$500 of consideration in excess of $150,000 but not in excess of $200,000;
$4.80/$500 of consideration in excess of $200,000 but not in excess of $550,000;
$5.30/$500 of consideration in excess of $550,000 but not in excess of $850,000;
$5.80/$500 of consideration in excess of $850,000 but not in excess of $1,000,000;
$6.05/$500 of consideration in excess of $1,000,000.

If over $1M, theres an additional 1% on top of the normal Transfer Fee

[JeteMc]

From what little I know about google sheets I believe that this will work as uses only basic functions.
I set up two ranges, one for 350k and under and the other for over 350k
The formulas to give the fee per step are: =IF(B$1>350000,0,MAX(0,MIN(B$1-K6,K7-K6)/500*J7))
and: =IF(B$1<=350000,0,MAX(0,MIN(B$1-K17,K18-K17)/500*J18))
The formula for the additional 1% is: =IF(B1>K27,B1*J27,0)
The sales price is placed into cell B1
The tax is displayed in cell B2 using: =SUM(L7:L27)
Let us know if you have any questions.

[kdoshi]

This is literally perfect, thank you soo much!!

[kdoshi]

Is there a way for me to have that formula in one cell? So that when I have a line of data with the data, the transfer fee will populate?Screen Shot 2020-04-26 at 1.28.34 PM.png

[JeteMc]

Although the attached file doesn't put the formula into one cell, it does arrange the calculations that were listed in post #12 horizontally in columns F:P so that they may be dragged from row to row.
Note that the range of green (manual inputs) and blue (calculations) cells may be moved and/or hidden for aesthetic purposes.
Let us know if you have any questions.

[kdoshi]

Just added it and it works flawlessly. Thank you so much for your time!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.