URGENT Template for auto calculation

[Exceldummy2018]

I would like to create a template that could automatically compute the values below each time i change the value in in the yellow field

Sales Price	10,000,000
	AMOUNT	RATE	INTEREST PAYABLE
First RM500K	A3	1.00%	D3
NextRM500K	A4	0.80%	D4
Next RM2M	A5	0.70%	D5
Next RM2M	A6	0.60%	D6
Thereafter	A7	0.50%	D7
From manual calculation,
A3	500,000		D3	5000
A4	500,000		D4	4000
A5	2,000,000		D5	14000
A6	2,000,000		D6	12000
A7	5,000,000		D7	25000

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

I need a template where it can auto calculate A3-A7 and D3-D7 when i input the Sales Price value

 

[oldbrewer]

so if you entered 750k you want 500 * 1% plus (750k-500k) * 0.8% ?

if so you need a lookup table.... will post a quickie in a few minutes

 

[oldbrewer]

	AMOUNT	RATE	INTEREST PAYABLE								mytable
First RM500K	First RM500K	1.00%	D3
NextRM500K	NextRM500K	0.80%	D4
Next RM2M	Next RM2M	0.70%	D5							500	0.01	5	5	500	0.008
Next RM2M	Next RM2M	0.60%	D6							1000	0.008	8	13	1500	0.007
Thereafter	Thereafter	0.50%	D7							4000	0.007	28	41	5500	0.006
										8000	0.006	48	89	13500	0.005
										99999999	0.005	0	0	0	0
								col I
						row 13		750	7
								2000	20
								5000	47
								9000	94
								20000	149
					not fully checked
					formula giving 7
					=VLOOKUP(I13,mytable,4)+(I13-VLOOKUP(I13,mytable,1))*VLOOKUP(I13,mytable,6)

<colgroup><col><col><col span="15"></colgroup><tbody>
</tbody>