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)


[TABLE="width: 1787"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 3"]COSTS / BUY[/TD]
[TD="colspan: 2"]PRICE / SELL[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 3"] [/TD]
[/TR]
[TR]
[TD]ITEM[/TD]
[TD]Supplier[/TD]
[TD]Product / ITEM[/TD]
[TD]Term Months[/TD]
[TD]ANY NON RECURRING CHARGE[/TD]
[TD]ANY MONTHLY RECURRING CHARGE(s)[/TD]
[TD]ANY ANNUAL RECURRING CHARGE(s)[/TD]
[TD]NON RECURRING CHARGE[/TD]
[TD]MONTHLY RECURRING CHARGE[/TD]
[TD]TOTAL FIRST YEAR MARGIN[/TD]
[TD]TOTAL SECOND YEAR CUMLATIVE MARGIN[/TD]
[TD]TOTAL THIRD YEAR CUMLATIVE MARGIN[/TD]
[TD]TERM MARGIN @ 36 MONTHS[/TD]
[TD]TCV £[/TD]
[TD]PROFIT over TERM[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Vodafone[/TD]
[TD]50/100[/TD]
[TD]36[/TD]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£4,846.08[/TD]
[TD="align: right"]£0.00[/TD]
[TD="align: right"]£484.60[/TD]
[TD]16.67%[/TD]
[TD]16.67%[/TD]
[TD]16.67%[/TD]
[TD]16.67%[/TD]
[TD="align: right"]£17,445.60[/TD]
[TD="align: right"]£2,907.36[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.