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
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
Somenath

3
Leave a Reply

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

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???

รับถ่ายวีดีโอ
Guest

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

Prithwis Mukerjee
Guest
Prithwis Mukerjee

Good