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.

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.

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