Spreadsheet: 60811d1261405707-excel-2007-excel-formula-to-deduct-outstanding-balance-from-seperate-cell-pension-cash-flow
Sheet: Retirement Cashflow 

 | Retirement Income Cash Flow - |  |  |  |  |  |  |  |  |  |  |  |  | January |  | 2009
 | Current Position - Sources of Annual Income & Use Of Available Capital at Retirement
 | End of Year | Client | Spouse | Ret Year |  | Non-Tax Sheltered Fund Beginning Balance | Tax Sheltered Fund Beginning Balance | Annual Target Income | State Pension | Private Pension | Other Income | State Pension | Private Pension | Other Income | Total Income | Est'd Tax On Income | Net Income Received | Shortfall Pension Income | Distribution From         Non Tax Sheltered Fund To Bridge Income Shortfall | End of Year | Distribution From         Tax Sheltered Fund To Bridge Income Shortfall | Total Net Income plus Distribution Received | Invest Return Non Tax Sheltered Fund | Invest Return Tax Sheltered Fund | Non-Tax Sheltered Fund Ending Balance | Tax Sheltered Fund Ending Balance |  |  |  | Income Tax |  |  |  |  |  |  |  |  |  | Revalued 40% Threshold
 |  | Age | Age |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | No. Years To Retire | Income | [NAME] | [NAME] | HRT Slice | BRT Rate | HRT Rate | Basic Rate Tax | Higher Rate Tax | Tax Deducted
 |  |  |  |  | Note |  |  |  | Client | Client | Client | Spouse | Spouse | Spouse
 | 2034 | 65 | 65 | 1 | 1 | 50000 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 25 |  |  |  | 0 | 0.2 | 0.4 |  | 0
 |  |  |  | 1 | 2 | 30000 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 25 |  |  |  | 0 | 0.2 | 0.4 |  | 0
 |  |  |  | 1 | 3 | 40000 | 40000 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Combined Tax
 |  |  |  | 1 | 4
 |  |  |  | 1 | 5
 | 2034 | 65 | 65 | 1 | Tot |  |  | 65000 | 10000 | 12000 | 0 | 9000 | 8000 | 0
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Income Tax Rates & Allowances |  |  | 2009/10 |  |  | 2010/11 |  |  |  | NIC Rates | 2009/10 |  | 2010/11
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Under age 65 Personal Allowance |  |  | 6475 |  |  | 6475
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Age 65 to 75 Personal Allowance |  |  | 9490 |  |  | 9490 |  |  |  | Class 1 - Employees
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Age 75 plus Personal Allowance |  |  | 9640 |  |  | 9640 |  |  |  | LEL | 4940 | 0.11 | 5044 | 0.11
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Married Couples Allowance Over Age 75 |  |  | 6965 |  |  | 6965 |  |  |  | UEL | 43888 |  | 43888
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Income limit for age-related allowances |  |  | 22900 |  |  | 22900 |  |  |  | Above UEL |  | 0.01 |  | 0.01
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Class 2 - S/Employed
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Weekly | 2.4 |  | 2.4
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Income Tax Rates & Bands |  |  | Basic |  | Higher |  | Add Rate |  |  | Class 4 - S/Employed
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 0.2 |  | 0.4 |  | 0.5 |  |  | LPL | 5715 | 0.08 | 5715 | 0.08
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | £0 - | 37400 | Over | 37400 | Over | 150000 |  |  | UPL | 43875 |  | 43875
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Personal Allowance reduces where the income is above £100, 000 – by £1 for every £2 of income above the £100,000 limit. This reduction applies irrespective of age.
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Above UPL |  | 0.01
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | You do noy pay NIC if under age 16 or when attained age 65 male: 60 females.
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Capital Gains Tax |  |  | 2009/10 | 2009/10
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Tax Free Allowance |  |  | 10100 | 10100
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Rate |  |  | 0.18 | 0.18
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Today's Value |  | [NAME]
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Term (Yrs) | Rate
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Revaluation |  | 25 | 0.03 | 30000
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Depreciation |  | 25 | 0.03 |  |  | 66639
 | Notes: |  |  |  |  |  |  |  |  |  |  |  | Assumptions:
 | 1 | Deposit Accounts |  |  |  | 5 | Proceeds from sale of investment property |  |  |  |  |  | 0.03 | Pre-retirement income inflation rate |  |  |  |  | 0.03 |  | Income tax personal allowance annual increase
 | 2 | Cash ISA Portfolio |  |  |  | 6 | Proceeds from sale of business |  |  |  |  |  | 0.03 | Post retirement income inflation rate |  |  |  |  | 0.03 |  | Basic & higher rate tax thresholds annual increase
 | 3 | Equity ISA Portfolio |  |  |  | 7 | Proceeds from main residence downsizing |  |  |  |  |  | 0.2 | Basic rat tax |  |  |  |  | 0.03 |  | Non tax sheltered funds average growth pa
 | 4 | Investment Portfolio |  |  |  |  |  |  |  |  |  |  | 0.4 | Higher rate tax |  |  |  |  | 0.03 |  | Tax sheltered funds average growth pa
 |  | Cell G12 -  Total Non-Tax Sheltered Fund balance at beginning Year
 |  | Cell H12 -  Total Tax Sheltered Fund  balance at beginning Year
 |  | Cell i12 - Annual Income required
 |  | Cells i12 to O12 - annual income received from different sources
 |  | Cell P12 - Total annual income received from all sources
 |  | Cell Q12 - Income tax deducted from total income
 |  | Cell R12 - Total net income after tax
 |  | Cell S12 - Income shortfall (r12- i12)
 |  | Cell T12 - Need to automatically enter withdrawal equal to income shortfall
 |  | Cell V12 - Need to automatically enter withdrawal equal to income shortfall/balance of income T12 unable to provide due to a lack of funds
 |  | Cell W12 - Total income received from income and distribution
 |  | Cell X12 - Growth received for the year on G12 after distribution
 |  | Cell Y12 - Growth received for the year on G12 after distribution
 |  | Cell Z12 - Value of G12 at end of year after any capital distribution plus growth
 |  | Cell AA12 - Value of H12 at end of year after any capital distribution plus growth
 |  | Cell G13 - Equals cell Z12
 |  | Cell H13 - Equals cell AA12