Financial planning for retirement is a look into an uncertainly long future & is doomed to fail if it is not aligned with the vision for post-retired life, retirement age & the health status of retiree. The key to a secure retirement is in accumulation of a retirement corpus large enough to support the visional lifestyle, even as earned income has ceased.

Using MS Excel to manage Retirement Finances

Financial Projections have to be based on certain assumptions – some guesstimated, others unknowable & thus should be dynamically monitored. MS Excel works well for recording, calculating, tracking & revising the data.  

Essential inputs required to plan the retirement finances

Life Expectancy – It is the age of the person, who is expected to survive other members planned for. The plan should be for the person & life partner. Children are expected to plan for themselves, after they become capable & should be included only in exceptional circumstances.

Time Span of Plan = Number of years for member expected to live longest less Age of person making the plan

Retirement Age – Retirement Age may be mandated by terms of employment & would be the age till when earned income is expected to come. If it is not, then assume a realistic number, given the type of work one is doing. Also, factor in technological changes, health & effects of ageing, that might force such retirement decision on a person.  Just because one wishes to work for forever, does not mean that is going to happen. A long-term trend is that of declining work life.

Round off your age to the next digit as at the start of financial year. Age reference allows for better planning over various milestones in the life cycle, besides reinforcing the desirability of sticking to the plan. Rip Van Winkle isn’t be the last one who woke up late. Most people just do that & miss out on major opportunities in life.

Desired Annual Retirement Income – When earned income stops coming in, then accumulated savings get dipped into to maintain the desired lifestyle. The vision for retired life & where it is going to be lived, determine the kind of expenses needed to be made.

Inflation during the time between planning stage & actual expenses, reduces the purchasing power of currency & larger funds are required to compensate for this loss of purchasing power. So, expenses budgeted as per present scenario, need to factor in compounding effect of inflation over the entire time span of plan. Till the retirement, inflation cuts into the capacity to save & after retirement, would affect sustainability of withdrawals (& income tax incidence, in case required income breaches minimum tax-free limit).

Future Value of Budgeted Expenses >>> =FV(rate, npr, pmt, [pv], [type])

Where,

rate = annual inflation rate (%),

npr = total number of periods,

pmt = budgeted annual expenses (or previous period’s expense),

[pv] = (exceptional) constant expenses during certain years,

[type] = 1, if expenses are in beginning of period or 0, if at end of period

Contribution to Corpus – Retirement Corpus is built on the bedrock of investment of savings & returns earned on such investment. The plan should assume ‘Savings Rate’ i.e., savings as percentage of pre-tax income, planned to be invested (not in rupee terms). That would progressively increase investment amount & put a check on disproportionate increase in expense with increase in income.

Return on investment – Asset allocation largely determines the returns expected from investment portfolio. Weighted average return of all asset classes could be taken as assumed return. Such assumption should be conservative & based on long term trends. Risk tolerance is key to deciding the asset allocation before retirement & becomes even more important in post-retirement scenario. A change in asset allocation with passage of time, would change the return assumption.

The degree of risk assumed in accumulation as well as distribution stage, has a great impact on the type of retirement one gets to enjoy. Avoiding volatility risk by placing too much reliance on debt segment, leads to declining purchasing power with time. At the other end of spectrum is taking too much risk to improve returns & shore up the retirement corpus such as short-term trading, speculation, penny stocks & unregulated products. It could prove highly damaging to retirement plans if it leads to capital losses & there isn’t enough time left to recover from the setback.

Income Tax Impact – Marginal Rate of income tax should be assumed, where applicable. Marginal Rate is income tax paid after claiming exemptions & deductions, as percentage of gross income. Tax rules as on date should be assumed to continue. Any change be incorporated as & when notified by the government. Income Tax impact is significant before as well as retirement & should not be ignored.  

Lump Sum Payments – It could include real estate & vehicle purchase, as well as sequence of return risk loss.

Post-Retirement Income / Receipts – It could include annuities, pension, business income, freelancing income, royalties etc.

Legacy – Legacy desired to be left for the dear & near one should be assumed. This balance amount would remain after all those for whom the retirement plan was made, have passed away.

Excel Calculations

Column A	Column B	Column C	Column D	Column E	Column F	Column G	Column H	Column I	Column J	Column K	Column L
Financial Year	Age (years)	Corpus (Opening Balance) (in Rs.)	Contribution (in Rs.)	Investment Return (in Rs.)	Post Retirement (Income /Receipt) (in Rs.)	Expense Need (adj. inflation & tax) (in Rs.)	Expense Need (pre-tax & post-inflation) (in Rs.)	Corpus (Year End Balance) (in Rs.)	Corpus Gap (in Rs.)	Required increase in Savings Rate (%)	Required reduction in Expenses Rate (%)

Column A – Financial Year – a row for every year till the last year of expected life of surviving member.

Column B – Age – First row has age rounded off to next digit. Number of rows will equal life expectancy of plan.

Column C – Corpus Opening Balance (in rupees) – First row is current market value of total financial assets. Each succeeding row has corpus amount at the end of previous year.

Column D – Contribution (in rupees) – Each row has savings for that year (savings rate as % of pre-tax income till retirement)

Column E – Investment Return (in rupees) – Return for each row is as per formula >>> Column C*Investment Return%

Column F – Post-Retirement Income/Receipt (in rupees) – Post-Retirement income is deducted from Expenses Need (before tax) for the year & if any balance remains then it is included in the row for such year.

Column G – Expenses Need (Inflation & Tax adjusted) (in rupees) – Till retirement earned income takes care of expenses, so nil amount in each row till retirement. Starting from row of first retirement year >>> FV of budgeted expenses for first year of retirement as per compounded inflation rate & each subsequent row is calculated by formula >>> Previous Year*(100+Inflation Rate). Include any lump sum payment in the row for that year.

Column H – Expenses Need (Pre-Tax & Inflation adjusted) (in rupees) – It starts from row of first retirement year >>> Column G of first year of retirement*[Marginal Income Tax Rate/(100-Marginal Income Tax Rate)*100] & each subsequent row with same formula.

Column I – Corpus Year End Balance (in rupees) >>> Column C+D+E+F-H  

Column J – Corpus Gap (in rupees) >>> Sum of all rows with negative balance in Column I & Legacy (in rupees)

Column K – Savings Rate Increase (%) Required – Present Value of each row in Column J >>> =PV(rate,nper,pmt,[fv],[type])   

Column L – Expense Rate Decrease (%) Required – To the extent Savings rate % not possible, Present Value of balance amount to be reduced for each row.