How to calculate interest rates using VLOOKUP

We have chart with four rows which actually giving interest rate for 1yrs, 5yrs, 10yrs and 30 yrs.

Now from that chart our target is to find out interest rate for any year given as input.

Here is the chart given as input.

How to get interest rates using Vlookup - Input

B and C columns hold the given year and corresponding interest rate. For example if the loan year is 1 interest rate will be 6, if the year of loan is 5 interest rate will be 7 and so on.

‘A’ Column is input year for which we need to find the interest rate, for example if the year of loan is 2 what will be the interest rate or if the year of loan is 30 what will be the interest rate and like that.

First of all we will add an extra column (Column ‘D’) to find out the gradient for the table

Formula will be

=(C3-C2)/(B3-B2)

(For the first row)

We will use the gradient in the final formula which will be

=C2+((C3-C2)/(B3-B2))*(A2-B2)

(for the first row)

To make the formula general we will use VLOOKUP

=VLOOKUP(A2,$B$2:$D$5,2,TRUE)+(VLOOKUP(A2,$B$2:$D$5,3,TRUE)*(A2-VLOOKUP(A2,$B$2:$D$5,1)))

We just have to drag it to the below rows to get the interest rate for the corresponding year in the ‘A’ column.

How to get interest rates using Vlookup - Output

 

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

3 thoughts on “How to calculate interest rates using VLOOKUP

Leave a Reply

Your email address will not be published. Required fields are marked *