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


(For the first row)

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


(for the first row)

To make the formula general we will use VLOOKUP


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

Leave a Reply

3 Comment threads
0 Thread replies
Most reacted comment
Hottest comment thread
3 Comment authors
Rajashri Chakrabortyรับถ่ายวีดีโอPrithwis Mukerjee Recent comment authors
newest oldest most voted
Notify of
Rajashri Chakraborty

above xplanasn z ohk!!!!!! but what to do if v need to put input year in each step wdout having a seperate column??and what if v have a particulr frmula to get the rate for an intrmediate yr???


Very good article. I will be going through some of these issues as

Prithwis Mukerjee
Prithwis Mukerjee