Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.

## AbbeyG

3 posts

Wannabe Geek

Topic # 66103 12-Aug-2010 11:51

Morning all,

Im after some help for an assignment im doing.
While it may not be the most effective way to work it out, it's what i'm required to do.

I have to calculate interest on a loan amount where the interest rate varies between each level of \$\$ borrowed. Similar to how tax is calculated.

I have done this for my nested if statement, but the second question is how to do it using a vlookup.
My tutor told me I will be using if statements inside it.

\$0-\$12,000 borrowed is 5%. \$12,001-\$45,000 is 7.5%, \$45,000-\$80,000 is 8% and above \$80,000 is 8.5%

My if statement is as follows: Referencing back to Cell B8

=IF(B8<12000,B8*0.05,IF(B8<45000,(B8-12000)*0.075+(12000*0.05),IF(B8<80000,((B8-45000)*0.08)+((45000-12000)*0.075)+(12000*0.05),IF(B8>=80000,((B8-80000)*0.085)+((80000-45000)*0.08)+((45000-12000)*0.075)+(12000*0.05),0))))

This works very well.

But how on earth do i get this into a table with only the % showing?

Thanks in advance for any help.
(I am also interested in the best way to do this for future referance too)

## AbbeyG

3 posts

Wannabe Geek

Ok so Ive managed to get what I want done, but its not nearly as tidy as i had hoped.
I added a third column with the if statements in it, and used the second as the %.
Just ment i referenced the 3rd column instead of the second in the vlookup.

Still very interested if anyone knows a cleaner way to do this.

## rvangelder

352 posts

Ultimate Geek

A1 to A4 are:
0, 12000, 45000, 80000
B1 to B4 are:
5.0%, 7.5%, 8.0%, 8.5%
C1 is 0
C2 is a formula:
=(A2 - A1) * B1 + C1
Copy C2 to C3
Copy C2 to C4

Formula:
=(B8 - VLOOKUP(B8, \$A\$1:\$A\$4, 1)) * VLOOKUP(B8, \$A\$1:\$B\$4, 2) + VLOOKUP(B8, \$A\$1:\$C\$4, 3)

Look mum, no IFs!

## AbbeyG

3 posts

Wannabe Geek

Thanks rvangelder,

I had to use the If's as my tutor required it....
But thanks so much for your response.

I'll have a go using it to see if it works too :)

Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.

Geekzone Live »

Our community of supporters help make Geekzone possible. Click the button below to join them.