Help with costing formulas

[NoelD]

I am building a car RENTAL schedule based on three possible terms, each with two possible advance payments. The BUYBACK is calculated based on the same premise as the Rental.

I have been unable to create a working formula for the scenario. I have a spreadsheet image attached with my formula [ that does not work ]

Any help would be fantastic

Noel

 

[jasonb75]

If I'm following your requirement correctly then VLOOKUP is not suitable for what you are trying to do.

Try this one on O19 (note that P19 must be empty, or you will still get a #SPILL! error.

=N19*INDEX($I$11:$N$12,MATCH(J19,$H$11:$H$12),MATCH(K19,$I$9:$N$9)+{0,1})/1000

 

[Eric W]

I have some questions about how the Term/Advance values match up with the table. Your term (the J19 value) - since it's a 2, do you mean the second term listed, i.e. K11:L12? Or does the 2 match with the I9 value? For the Advance (K19), does it match with the H11:H12 values? If so, shouldn't you be looking at row 11 for a result? If not, how you you choose which row to use?

Here's an example using my current assumptions:

Book1
	G	H	I	J	K	L	M	N	O	P	Q
9	Term	Term	2	Years	3	Years	4	Years
10	Factors		Invoice	Buyback	Invoice	Buyback	Invoice	Buyback
11	Advance	1	44.31	38.71	30.48	24.96	23.59	18.1
12	Advance	3	43.86	38.32	30.17	24.7	23.34	17.91
13
14
15
16
17				TERM	ADVANCE		STEP 1		STEP 2	STEP 3	STEP 4
18	Price	VRT	Buyback amt	Term >Years	Rentals in Advance	VRT Factor	IFC	VFFC	Invoice Factor	Buyback Factor
19	40000	5500	16000	2	1	0.18699	1265	41265	1257.757	399.36	858.3972
Sheet5

Cell Formulas
Range	Formula
O19	O19	=N19*INDEX($I$11:$N$12,MATCH($K$19,$H$11:$H$12,0),$J$19*2-1)/1000
P19	P19	=I19*INDEX($I$11:$N$12,MATCH($K$19,$H$11:$H$12,0),$J$19*2)/1000
Q19	Q19	=O19-P19

 

[NoelD]

I have some questions about how the Term/Advance values match up with the table. Your term (the J19 value) - since it's a 2, do you mean the second term listed, i.e. K11:L12? Or does the 2 match with the I9 value? For the Advance (K19), does it match with the H11:H12 values? If so, shouldn't you be looking at row 11 for a result? If not, how you you choose which row to use?

Here's an example using my current assumptions:

 

[NoelD]

Hi Eric

Firstly, thank you for your assistance.

To Clarify J19 = 2, which refers to Column I9 and the Advance number in K19, refers to I11. It was that part of the formula that I failed with as the Terms and Advance numbers changed.

In relation to the Buyback formula, for this case , would use J11. Can you have the formula work when changing the term number and advance in Cells J19 & k19 will read the appropriate rates from teh table.

If you could rework your magic, that would be great.

Noel

 

[NoelD]

Hi Eric

Firstly, thank you for your assistance.

To Clarify J19 = 2, which refers to Column I9 and the Advance number in K19, refers to I11. It was that part of the formula that I failed with as the Terms and Advance numbers changed.

In relation to the Buyback formula, for this case , would use J11. Can you have the formula work when changing the term number and advance in Cells J19 & k19 will read the appropriate rates from teh table.

If you could rework your magic, that would be great.

Noel

this image has the answer that should be achieved....if you can work your magic formulas

 

[Eric W]

Try:

Book1
	G	H	I	J	K	L	M	N	O	P	Q
9	Term	Term	2	Years	3	Years	4	Years
10	Factors		Invoice	Buyback	Invoice	Buyback	Invoice	Buyback
11	Advance	1	44.31	38.71	30.48	24.96	23.59	18.1
12	Advance	3	43.86	38.32	30.17	24.7	23.34	17.91
13
14
15
16
17				TERM	ADVANCE		STEP 1		STEP 2	STEP 3	STEP 4
18	Price	VRT	Buyback amt	Term >Years	Rentals in Advance	VRT Factor	IFC	VFFC	Invoice Factor	Buyback Factor
19	40000	5500	16000	3	3	0.18699	1265	41265	1244.965	395.2	849.7651
20									1244.965	395.2
Sheet5

Cell Formulas
Range	Formula
P19	P19	=I19*INDEX($I$11:$N$12,MATCH($K$19,$H$11:$H$12,0),MATCH($J$19,$I$9:$N$9,0)+1)/1000
Q19	Q19	=O19-P19
O19	O19	=N19*INDEX($I$11:$N$12,MATCH($K$19,$H$11:$H$12,0),MATCH($J$19,$I$9:$N$9,0))/1000
O20:P20	O20	=INDEX(I19:N19,{6,1})*INDEX($I$11:$N$12,MATCH($K$19,$H$11:$H$12,0),MATCH($J$19,$I$9:$N$9,0)+{0,1})/1000
Dynamic array formulas.

The O19, P19 formulas are single cell formulas, while the O20 is a SPILL formula if you prefer, although I think the 2 formulas are clearer in this case.

 

[NoelD]

Thank you Eric, you have made my night.

 

[Eric W]

Glad we could help.