Excel – calculations with percentages

I often get asked on my Excel courses how to perform calculations with percentages, and I can never remember as these calculations are not something I need on a daily basis in my job, so I have produced a handout to answer these questions. I thought it would also be useful to add this as an article on BWT. I hope this helps you.

Find out the percentage of an amount

Multiple the value by the percentage required,

eg find 20% of £5.25

5.25 x 0.2 = 1.05

In Excel the formula would be =5.25*20%

Increase an amount by a percentage

Multiple the value by the percentage plus 100,

eg the cost price of a pen is £5.25, what is the selling price after 20% VAT has been added?

5.25 x 1.2 = 6.30

In Excel the formula would be =5.25*120%

Find the original amount before the increase was added

Divide the gross value by the percentage plus 100,

eg a pen sells for £6.30 after 20% VAT is added, what is the cost price?

6.30 ÷ 1.2 = 5.25

In Excel the formula would be =6.3/120%

Find the original amount when the increase and percentage is known

Divide the increase by the percentage and multiple by 100,

eg the VAT rate is 20% and the VAT amount is £11, what is the net value?

11 ÷ 20 = 0.55, 0.55 x 100 = 55

In Excel the formula would be =11/20*100

Find a value as a percentage of the total

Divide the smaller number by the larger number and multiply by 100,

eg 12 of the 25 people are female, what percentage are female?

12 ÷ 25 = 0.48, 0.48  x 100 = 48%

In Excel the formula would be =12/25*100

Calculate the percentage increase/decrease of a value

Work out the difference between the two numbers, divide the result by the original number and multiple the answer by 100,

eg the total sales last month were 850, this month the sales are 986, what is the percentage increase?

986 – 850 = 186, 186 ÷ 850 = 0.16, 0.16 x 100 = 16%

In Excel the formula would be =(986-850)/850*100

eg the number of votes last year were 1529, this year the number of votes are 1205, what is the percentage decrease?

1529 – 1205 = 324, 324 ÷ 1529 = 0.21, 0.21 x 100 = 21%

In Excel the formula would be =(1529-1205)/1529*100

Calculate simple interest

Interest = P x R x T

P = principal – amount borrowed

R = rate of interest

T = time

eg £400 is borrowed for 3 years at 5% per annum, how much interest is paid.

400 x 0.05 = 20, 20 x 3 = 60

In Excel the formula would be = 400*5%*3

Calculate compound interest

FV = PV x (1+ R)ⁿ

FV = future value – amount payable

PV = present value – amount borrowed

R = rate of interest

n = number of years/months etc

eg £400 is borrowed for 3 years at 5% compound interest, what is the total amount of the loan?

400 x (1+0.05)³ = 463.05

In Excel the formula would be =400*(1+5%)^3

eg £1,000 is saved in an account paying 2% interest annually, how much will be in the account after 5 years?

1000 x (1+0.02)⁵ = 1104.08

In Excel the formula would be =1000*(1+2%)^5