Index Match

[Stclements1]

I am trying to create an interest return calculator.

In Cell B1 the value of the loan is entered
In Cell B2 the term of the loan is selected from a drop-down list numbered 1-3
The problem I am having is that there are multiple variables on the interest return dependent on the size of the loan and the term of the loan.
What I am trying to return In Cell B6 is the loan rate based on the loan amount and the term
All the loan rates below are based on a one-year loan, but should the loan be for two or three years then it would attract additional interest as per the term rates

Example 1

Loan 50000 = .00825%
Term 2 Years = .00025%
Total Interest rate = 0085%

Example 2

Loan 100000 = .0095%
Term 3 Years = .005%
Total Interest rate = .01%

Loan rate
0-30000 =.007%
>30000 -75000=.00825%
>75000 =.0095%

Term
1 Year = 0%
2 Years = .00025%
3 Years = .0005%

If statements would be rather a large formula so I wondered whether an index match or Choose formula would make more sense, unless there is a simpler formula.

 

[jasonb75]

Something like

=LOOKUP(loan,{0,30001,75001},{0.007,0.00825,0.0095})%+CHOOSE(term,0,0.00025,0.0005)%

Change the descriptions in the formula as needed.

 

[Toadstool]

Hi StClements1,

INDEX & MATCH would probably be easiest, or even a VLOOKUP.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L
1	Value	100000						Value List	Percent		Term List	Percent
2	Term	3						0	0.007%		1	0.%
3								30000	0.00825%		2	0.00025%
4								75000	0.0095%		3	0.0005%
5
6	Loan Rate	0.01%
7
Sheet1

Cell Formulas
Range	Formula
B6	B6	=INDEX($I$2:$I$4,MATCH($B$1,$H$2:$H$4,1))+INDEX($L$2:$L$4,MATCH($B$2,$K$2:$K$4,1))

 

[Stclements1]

Thank you both for getting back to me and for your time and guidance, I went with the Index Match formula and it worked perfectly, I did try the Lookup formula but it didn't work on my sheet. Once again many thanks for your time and efforts its much appreciated.

 

[jasonb75]

I did try the Lookup formula but it didn't work on my sheet.

Works fine for me, did you change the descriptions as instructed?