Pension Formula - Please help...

London2008

New Member
Joined
Sep 16, 2008
Messages
3
Hey there

I have never posted before, but always read the forum and the help is amazing, but i'm i have one question myself, which i can't find the answer to.

I need to work out a formula that provides the amount of pension costs we have to pay. ie.

If salary is between 0 and £19,500 the rate is 16.7%
If salary is between £19,501 and £40,500 the rate is 18.8%
If salary is between £40,501 and £69,000 the rate is 21.8%
If salary is between £69,001 and above the rate is 24.3%

I can't for the life of me work out a formula that will look at the salary field and calculate the above rates and output the annual amount.

Has anyone go any ideas?

thanks very much in advance

Mike
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Hi and welcome to MrExcel.

Try

=LOOKUP(A1,{0,19501,40501,69001},{0.167,0.188,0.218,0.243})
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
365, 2016
Platform
Windows
Give this a shot.

The formula I used in B2 is:
=VLOOKUP(B1,A4:B7,2)
Book2
ABCD
1Salary:$15,000
2Rate:
16.7%
3
4$016.7%
5$19,50118.8%
6$40,50121.8%
7$61,00124.3%
Sheet1
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,945
Welcome to the board.

So we're clear, if a salary is £20,000 should the calculation return £20,000 * 18.8% = £3,760? Or £19,500 * 16.7% + £500 * 18.8% = £3,350.5?
 

London2008

New Member
Joined
Sep 16, 2008
Messages
3
Wow, thank you so much. it has worked.

i can believe how easy it was! my brain was almost fried today.

thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,095,920
Messages
5,447,295
Members
405,447
Latest member
WPY

This Week's Hot Topics

Top