Trouble with Tax and Pension formulas on Payroll spreadsheet

[healthcarelady]

I work for a medical clinic and do physician payroll. They are technically considered self employed and pay both the employee and employer portion of taxes. I am responsible for calculating the employer portion of Medicare and FICA and taking that out of their check. They also have a Pension amount that comes out as well.

The stipulations are:
1. Medicare tax - 1.45% on Total W2 amount, no maximum
2. FICA tax - 6.2% on Total W2 amount, maximum $6621.60
3. Pension
a. 11.3% on the first $9,000 of W2
b. 17% on the rest of W2
c. the max for total pension is $41,137/year

Also, they get paid once per month and all maximums are calculated on a calendar year. The tricky part is that these amounts are percentages of the W2 cell. And the W2 cell is reduced by these amounts. So you kind of have to back into these numbers.

I've attached an example. With the formulas in columns I:T, I come close, but the pension is wrong. The correct figures are in D11:G11. I need some help fixing these formulas in I5:T5 to match these cells.

I know this is confusing - let me know if anyone needs additional info. Thanks for any input!



JAN 2010

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 54px"><COL style="WIDTH: 166px"><COL style="WIDTH: 124px"><COL style="WIDTH: 110px"><COL style="WIDTH: 110px"><COL style="WIDTH: 110px"><COL style="WIDTH: 110px"><COL style="WIDTH: 122px"><COL style="WIDTH: 142px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"><COL style="WIDTH: 112px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD><TD>O</TD><TD>P</TD><TD>Q</TD><TD>R</TD><TD>S</TD><TD>T</TD><TD>U</TD><TD>V</TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #ffff00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: right">9000</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: center">1.45%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: center">6.20%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: center">11.3%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">17%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">1.45%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">6.20%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">11.3%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">17%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">1.45%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">6.20%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">11.3%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">17%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">1.45%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">6.20%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">11.3%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">17%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Net</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #c0c0c0"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Net</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">Net</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Total</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ffffff; BACKGROUND-COLOR: #ff0000; TEXT-ALIGN: center" colSpan=4>Remaining to Max </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ffffff; BACKGROUND-COLOR: #0000ff; TEXT-ALIGN: center" colSpan=4>Straight % </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ffffff; BACKGROUND-COLOR: #008000; TEXT-ALIGN: center" colSpan=4>Calculation </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Pay # </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #c0c0c0">Name</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Comp</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">MCR Tax</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">FICA Tax</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">Pension</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">Pension</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #c0c0c0; TEXT-ALIGN: center">W-2</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">MCR </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">FICA </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">11.3 Pens </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">17 Pens</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">MCR </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">FICA </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">11.3 Pens </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">17 Pens</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">MCR </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">FICA </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">11.3 Pens </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">17 Pens</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-SIZE: 12pt; TEXT-ALIGN: center">4</TD><TD style="FONT-SIZE: 12pt; TEXT-ALIGN: left">SMITH</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: center"> 85,901.72 </TD><TD style="FONT-SIZE: 12pt; TEXT-ALIGN: right"> 987.43 </TD><TD style="FONT-SIZE: 12pt; TEXT-ALIGN: right"> 4,222.10 </TD><TD style="FONT-SIZE: 12pt; TEXT-ALIGN: right"> 1,017.00 </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right"> 11,576.74 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: right"> 68,098.45 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center"> 1,000,000 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">$ 6,621.60 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">$ 1,017.00 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: center">$ 41,137.00 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: right">$ 916.20 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: right">$ 3,917.55 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: right">$ 7,140.05 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: right">$ 10,741.66 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: right">$ 987.43 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: right">$ 4,222.10 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: right">$ 1,017.00 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3; TEXT-ALIGN: right">$ 11,576.74 </TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 8px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #ffff00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #ff0000; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #0000ff; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; COLOR: #008000; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: right">1.45%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: right">6.20%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">17.00%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #ffff00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #ffff00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #ffff00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt">CORRECT VALUES</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: center"> 85,901.72 </TD><TD style="FONT-SIZE: 12pt; TEXT-ALIGN: right"> 1,005.00 </TD><TD style="FONT-SIZE: 12pt; TEXT-ALIGN: right"> 4,299.00 </TD><TD style="FONT-SIZE: 12pt; TEXT-ALIGN: right"> 1,017.00 </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right"> 10,249.66 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: right"> 69,331.06 </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 4px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #ffff00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: right">1.45%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; TEXT-ALIGN: right">6.20%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">14.78%</TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #ffff00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD></TR><TR style="HEIGHT: 21px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #ffff00"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt; BACKGROUND-COLOR: #e3e3e3"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD><TD style="FONT-WEIGHT: bold; FONT-SIZE: 12pt"> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D5</TD><TD>=Q5</TD></TR><TR><TD>E5</TD><TD>=R5</TD></TR><TR><TD>F5</TD><TD>=S5</TD></TR><TR><TD>G5</TD><TD>=T5</TD></TR><TR><TD>H5</TD><TD>=ROUND(C5-D5-E5-F5-G5,2)</TD></TR><TR><TD>J5</TD><TD>=VLOOKUP(A5, FICA6.2!$A:$P,16,0)</TD></TR><TR><TD>K5</TD><TD>=MIN(C5*$K$2, VLOOKUP(A5,Pension11.3!$A:$S,19,0))</TD></TR><TR><TD>L5</TD><TD>=VLOOKUP(A5,Pension17!$A:$T,20,0)</TD></TR><TR><TD>M5</TD><TD>=$C5/(1+SUMPRODUCT(($D$2:$G$2)*($I5:$L5>0)))*$D$2</TD></TR><TR><TD>N5</TD><TD>=IF(J5=0,0,$C5/(1+SUMPRODUCT(($D$2:$G$2)*($I5:$L5>0)))*$E$2)</TD></TR><TR><TD>O5</TD><TD>=IF(K5=0,0,$C5/(1+SUMPRODUCT(($D$2:$G$2)*($I5:$L5>0)))*$F$2)</TD></TR><TR><TD>P5</TD><TD>=IF(L5=0,0,$C5/(1+SUMPRODUCT(($D$2:$G$2)*($I5:$L5>0)))*$G$2)</TD></TR><TR><TD>Q5</TD><TD>=($C5-SUMPRODUCT(($J5:$L5<$N5:$P5)*($J5:$L5)))/(1+$I$2+SUMPRODUCT(($J5:$L5>$N5:$P5)*$J$2:$L$2))*$I$2</TD></TR><TR><TD>R5</TD><TD>=IF(J5<=N5,J5,($C5-SUMPRODUCT(($J5:$L5<$N5:$P5)*($J5:$L5)))/(1+$Q$2+SUMPRODUCT(($J5:$L5>$N5:$P5)*$R$2:$T$2))*$R$2)</TD></TR><TR><TD>S5</TD><TD>=IF(K5<=O5,K5,($C5-SUMPRODUCT(($J5:$L5<$N5:$P5)*(J5:L5)))/(1+$Q$2+SUMPRODUCT(($J5:$L5>$N5:$P5)*$R$2:$T$2))*$S$2)</TD></TR><TR><TD>T5</TD><TD>=IF((S5+VLOOKUP(A5, Pension11.3!A:S, 17, FALSE))>=1017, IF(L5<=P5,L5,($C5-SUMPRODUCT(($J5:$L5<$N5:$P5)*(J5:L5)))/(1+$Q$2+SUMPRODUCT(($J5:$L5>$N5:$P5)*$R$2:$T$2))*$T$2), 0)</TD></TR><TR><TD>D7</TD><TD>=D5/$H$5</TD></TR><TR><TD>E7</TD><TD>=E5/$H$5</TD></TR><TR><TD>G7</TD><TD>=G5/$H$5</TD></TR><TR><TD>D13</TD><TD>=D11/$H$11</TD></TR><TR><TD>E13</TD><TD>=E11/$H$11</TD></TR><TR><TD>G13</TD><TD>=G11/$H$11</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[DonkeyOte]

x-posted (as yet unanswered):
http://www.excelforum.com/excel-general/717337-tax-and-pension-formulas.html

As a common courtesy to other members please ensure you always provide links to other forums - people are donating their free time to help others - please do not give rise to the possibility of them (unknowingly) wasting it.

For a general summary of why x-posting is frowned upon see: http://www.excelguru.ca/node/7

 

[Andrew Poulsom]

Hint:

If net is gross less 1.45% of net, then the deduction is:

=gross*1.45%/(1+1.45%)