Permit Fee Schedule Calculator

[ascottflow]

Hello!
I am trying to create an interactive permit fee schedule in Excel. Essentially, I have a valuation, broken down into 5 categories of ranges with a set fee per range and an additional fee that increases for each additional thousand. I need help building a formula, so that when I enter an amount, Excel will populate the new valuation with added fees. Can anyone help me set this up please? I would so appreciate it. Thank you!

Here is the layout of the valuation:

Up to $2,000 Fee: $40.39

$2,000.01 to $50,000 Fee: $40.39 Plus $6.92 for each additional thousand

$50,000.01 to $100,000 Fee: $372.71 Plus $5.57 for each additional thousand

$100,000.01 to $500,000 Fee: $651.38 Plus $4.19 for each additional thousand

$500,000.01 and Up Fee: $2,326.84 Plus $2.79 for each additional thousand

[shg]

Welcome to the board.

How about ...

	A	B	C	D	E
1	Amt	Rate	Delta
2	0	0.000%	0.000%		C2: =B2-N(B1)
3	2,000	0.692%	0.692%
4	50,000	0.557%	-0.135%
5	100,000	0.419%	-0.138%
6	500,000	0.279%	-0.140%
7
8	Amt	Fee
9	1,000	40.39			B9: =SUMPRODUCT((A9 > $A$2:$A$6) * (A9 - $A$2:$A$6) * $C$2:$C$6) + 40.39
10	2,000	40.39
11	3,000	47.31
12	5,000	61.15
13	10,000	95.75
14	20,000	164.95
15	30,000	234.15
16	50,000	372.55
17	100,000	651.05
18	200,000	1070.05
19	300,000	1489.05
20	500,000	2327.05
21	1,000,000	3722.05

They aren't exactly the same at the breakpoints; yours have a discontinuity.

[ascottflow]

Thanks for the reply. This is an awesome start. Is there a way to set a nest of conditions that will allow someone to use this template to forecast what a value would be (including the fee) by entering a value and letting Excel spit back out the calculated result?

[shg]

A9 through A21 do exactly that.