Percentages not showing correctly

[SteveT1811]

Hi,

First post so apologies if this has been answered before, I have a margin calculator at take into account a yearly or monthly cost, a possible install and then a sell cost, this then works out a yearly percentage of margin, for 3 years. However the percentages are incorrect and I cant find where the error is. AN example and screenshot is below

Monthly Cost £403.84 (£4846.08 per annum)
Monthly Charge £484.60 (£5815.20 per annum)
The percentage of the margin is 20% (£403.84 x 1.2 =£484.60)

The excel book is showing this as a margin percentage of 16.67% and I cant figure out from the formula where it is wrong (the formula was created by someone else who is no longer with the company)

I have included the detail below the supplier is in column 8B and the formula for column J8 is below

=IFERROR(SUM(((I8*12)+(H8))-(E8+(F8*12)+(G8)))/((I8*12)+(H8)), 0)

COSTS / BUY

PRICE / SELL

ITEM

Supplier

Product / ITEM

Term Months

ANY NON RECURRING CHARGE

ANY MONTHLY RECURRING CHARGE(s)

ANY ANNUAL RECURRING CHARGE(s)

NON RECURRING CHARGE

MONTHLY RECURRING CHARGE

TOTAL FIRST YEAR MARGIN

TOTAL SECOND YEAR CUMLATIVE MARGIN

TOTAL THIRD YEAR CUMLATIVE MARGIN

TERM MARGIN @ 36 MONTHS

TCV £

PROFIT over TERM

1

Vodafone

50/100

36

£0.00

£4,846.08

£0.00

£484.60

16.67%

16.67%

16.67%

16.67%

£17,445.60

£2,907.36

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[jasonb75]

The formula is not wrong, your margin is 16.666667%, your mark up is 20%.

Mark up = profit as percentage of cost price.
Margin = profit as percentage of sale price.