Index Match

Stclements1

Board Regular
Joined
Sep 15, 2018
Messages
120
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,045
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,011
Office Version
  1. 2016
Platform
  1. Windows
Hi StClements1,

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

Book2
ABCDEFGHIJKL
1Value100000Value ListPercentTerm ListPercent
2Term300.007%10.%
3300000.00825%20.00025%
4750000.0095%30.0005%
5
6Loan Rate0.01%
7
Sheet1
Cell Formulas
RangeFormula
B6B6=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

Board Regular
Joined
Sep 15, 2018
Messages
120
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,472
Messages
5,548,237
Members
410,824
Latest member
Bobmn4
Top