simonkingston
New Member
- Joined
- Feb 14, 2008
- Messages
- 7
- Office Version
- 365
- Platform
- Windows
I am creating a table to calculate earnings from a pension, based on different criteria when they do some extra work also.
So, they earn $950/fortnight from the government pension, if they do some work for the week, the first $150 gross that is earned, the pension isn't touched. The next $100 gross earned; the pension is reduced by 50c for each dollar earned between $151 and $250. Each dollar earned beyond $250 gross, 60c is removed from the pension.
Examples:
1. They earn $140 for the fortnight, pension isn't touched, they get the full $950 plus the earnings of $140, total $1090
2. They earn $230 for the fortnight, first $150 of pension isn't touched, next $80 is reduced by 50c per dollar, so $40 is subtracted from the pension bringing it down to $910 plus the earnings of $230, total $1140
3. They earn $500 for the fortnight, first $150 of pension isn't touched, next $100 is reduced by 50c per dollar, so $50 is subtracted from the pension, and remaining $250 is reduced by 60c per dollar, so $150 is subtracted from the pension, bringing it down to $750 plus the earnings of $500, total $1250
I hope I have explained this properly enough to understand what I'm trying to achieve.
I have pasted what I have done so far, its clumsy and I feel there must be some formulas I can use, especially for that NEXT 100 calculation.
I look forward to getting any feedback or help.
So, they earn $950/fortnight from the government pension, if they do some work for the week, the first $150 gross that is earned, the pension isn't touched. The next $100 gross earned; the pension is reduced by 50c for each dollar earned between $151 and $250. Each dollar earned beyond $250 gross, 60c is removed from the pension.
Examples:
1. They earn $140 for the fortnight, pension isn't touched, they get the full $950 plus the earnings of $140, total $1090
2. They earn $230 for the fortnight, first $150 of pension isn't touched, next $80 is reduced by 50c per dollar, so $40 is subtracted from the pension bringing it down to $910 plus the earnings of $230, total $1140
3. They earn $500 for the fortnight, first $150 of pension isn't touched, next $100 is reduced by 50c per dollar, so $50 is subtracted from the pension, and remaining $250 is reduced by 60c per dollar, so $150 is subtracted from the pension, bringing it down to $750 plus the earnings of $500, total $1250
I hope I have explained this properly enough to understand what I'm trying to achieve.
I have pasted what I have done so far, its clumsy and I feel there must be some formulas I can use, especially for that NEXT 100 calculation.
| Pension Earnings Calculator.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | Work Earnings | |||||||||||||
| 2 | Hours/Fortnight | Rate | Gross | |||||||||||
| 3 | 40 | $ 28.00 | $ 1,120.00 | |||||||||||
| 4 | ||||||||||||||
| 5 | Pension | $ 950.00 | ||||||||||||
| 6 | ||||||||||||||
| 7 | ||||||||||||||
| 8 | Calculations | |||||||||||||
| 9 | Work | Pension Deductions | ||||||||||||
| 10 | First 150 | $ 150.00 | ||||||||||||
| 11 | Next 100 | $ 250.00 | $ 100.00 | $ 50.00 | This should never be more than $50 (this is where I am having a problem working out the correct calculation) | |||||||||
| 12 | After 250 | $ 870.00 | $ 522.00 | This will be the balance after $250 x60c | ||||||||||
| 13 | Total | $ 572.00 | ||||||||||||
| 14 | Pension Balance Paid | $ 378.00 | ||||||||||||
| 15 | Pension PLUS Earnings | $ 1,498.00 | ||||||||||||
Sheet1 | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D3 | D3 | =A3*B3 |
| B11 | B11 | =IF(D3<151,0,D3-(D3-C10-100)) |
| C11 | C11 | =IF(B11=0,0,D3-(D3-B11+C10)) |
| C12 | C12 | =IF(D3<251,0,D3-250) |
| D11 | D11 | =(C11*0.5) |
| D12 | D12 | =(C12*0.6) |
| D13 | D13 | =SUM(D11:D12) |
| D14 | D14 | =IF(D13>D5,0,D5-D13) |
| D15 | D15 | =D14+D3 |
I look forward to getting any feedback or help.
