MS Excel question... - Democratic Underground

CanuckAmok

(1000+ posts) Send PM | Profile | Ignore

Sat Apr-03-04 10:51 PM
Original message

MS Excel question...

I need to show a percentage amount of a dollar sum. It's a real estate price, where the commission on the first 100K is 7% and the commission on the rest of the price is 3%.

So say the price was $250000, the commission would be:

$7000 (7% of 100000)
$4500 (3% 0f 150000)

Total: $11500

Is there an Excel formula which will calculate an unusual feee structure like that? I'm trying to do a budget and need to base it on the final sale price of our home.

Printer Friendly | Permalink | | Top

On the Road

(1000+ posts) Send PM | Profile | Ignore

Sat Apr-03-04 10:58 PM
Response to Original message

1. Sure:

Say the commission is in cell F7. The formula would be:

=if(f7<100000,f7*.07,100000*.07+(f7-100000)*.03)

Or you might want to store the commissions in separate cells and use references.

Printer Friendly | Permalink | | Top

Gman

(1000+ posts) Send PM | Profile | Ignore

Sat Apr-03-04 11:00 PM
Response to Reply #1

3. That's it!

n/t

Printer Friendly | Permalink | | Top

CanuckAmok

(1000+ posts) Send PM | Profile | Ignore

Sat Apr-03-04 11:08 PM
Response to Reply #1

5. hey, that's it! Thanks!!

n/t

Printer Friendly | Permalink | | Top

unblock

(1000+ posts) Send PM | Profile | Ignore

Sat Apr-03-04 10:59 PM
Response to Original message

2. sure

say $A$1 is the first rate (7%)
and $A$2 is the second rate (3%)
and $B$1 is the crossover ($100000)

finally, if c1 the real estate price, the commission is:

=if(c1<$B$1, c1*$A$1, $A$1*$B$1 + $A$2*(c1-$B$1))

Printer Friendly | Permalink | | Top

alcuno

(1000+ posts) Send PM | Profile | Ignore

Sat Apr-03-04 11:03 PM
Response to Original message

4. I don't see how you could do it with one formula

Make your first column the price and increase it incrementally. Make your second column (B) the (value in column A - 100,000) times .03. On excel it would be (A1 - 100,000) * .03. Then make the third column the value in column B + 7000.

Printer Friendly | Permalink | | Top

SOteric

(1000+ posts) Send PM | Profile | Ignore

Sat Apr-03-04 11:24 PM
Response to Reply #4

7. Check out your functions table...

you're in for a pleasant surprise in terms of what function formulas will do for you.

Printer Friendly | Permalink | | Top

SoDesuKa

(1000+ posts) Send PM | Profile | Ignore

Sat Apr-03-04 11:20 PM
Response to Original message

6. Three Columns

Column A is the amount of the sale.

Column B is the amount after subtracting $100,000: =A1-100000

If Column A is $100,000 or more, the commission $7,000 PLUS 3 percent of Column B, otherwise it's just 7% of column A.

=IF(A1>100000,7000+(B1*0.03),A1*0.07)

Printer Friendly | Permalink | | Top

DU AdBot (1000+ posts)

Wed May 08th 2024, 03:33 PM
Response to Original message

Advertisements [?]

Top

Powered by DCForum+ Version 1.1 Copyright 1997-2002 DCScripts.com
Software has been extensively modified by the DU administrators

Important Notices: By participating on this discussion board, visitors agree to abide by the rules outlined on our Rules page. Messages posted on the Democratic Underground Discussion Forums are the opinions of the individuals who post them, and do not necessarily represent the opinions of Democratic Underground, LLC.