Planit:Long Term Cash Flow Case Study

From Planipedia

Example Problem One:

Donna and Harold Woodhaven have given you all required information to produce a Long-Term Cash Flow spreadsheet of their current and model scenario.

They have told you that Ben’s education is their most important goal, and then their retirement lifestyle. Also, while they would like to give a gift to their parents, they would find a way to split it with siblings if it jeopardized their retirement cruise. They only require $70,000 of retirement income, or $55,000 at the bare minimum. In order to achieve their goals they are willing to save an extra $4,000 each year, and push their retirement back to their age 60. However they would rather reduce their retirement lifestyle than work longer.

Using the Long-Term Cash Flow Spreadsheet for the current and modeled scenario, highlight how the new rate of return and higher savings affect the growth of their accounts over time. Also show the difference in out-flowing objectives.

Solution Using the Long-Term Cash Flow Spreadsheet:

Click the Next arrow to continue to the Your Working Documents screen.

1. In the Your Working Documents screen, click on the title called Preparing Reports – Life Planning Audit Options.
2. Click on the Radio button beside Long Term Cashflow - Current
3. Click on the Generate Document link. The excel document will take approximately one minute to generate. Hit the Refresh button periodically until the completed 110 document is ready for you to download.
4. When the document has completed calculating and is ready to view, click the Download button.
5. Repeat these steps to produce the Long Term Cashflow – Model after the first spreadsheet has completed downloading.

When you open the Long-Term Cash Flow Spreadsheet (Current), you will notice the spreadsheet is broken down into different sections showing the various inflows and outflows: capital, income, investment income, withdrawals, taxes, savings and life goals. The growth in the value of accounts is shown in the first section: Capital.

On the right-hand side, you can identify Robert and Sally’s open investments, Sally and Robert’s individual RRSPs. By following it to their retirement age of 57, you can see the total value of their accounts by the time they retire (in red boxes). The growth in these accounts is calculated by taking: the value of the account the year before + any savings – any withdrawals + any unplanned savings, all multiplied by the rate of return. These are shown in different areas of the spreadsheet. Because this spreadsheet is for the current scenario, it will use the original savings plan and current rate of return. The savings for the various years are shown in the Savings section. So in the current scenario, the RSP savings are $8,000 for both Robert and Sally.

Moving to the Long-Term Cash Flow Spreadsheet (Model), all the principles will work in the same way, but you will notice now that there are additional planned savings made by Sally and Robert. For the current year, there is $3,000 going into Sally’s non-registered account (originally $0) in addition to the identical RSP savings. That combined with the new target rate of return, shows a higher value for the registered accounts at the age of retirement.

Note: the differences in the values of accounts over the years will also change depending on other factors that differ between the scenarios like goals, withdrawals from capital et cetera.

Returning to the Cash Flow Spreadsheet for the current scenario, you will notice the final section, Life Goals. Here you can see the different goals, (home renovations and a charity donation) that the clients have expressed they wanted to fund. Also, at their retirement age, the need for their current lifestyle ends, and their need for their retirement lifestyle begins. These goal needs are future values, based on the original values you entered plus the indexation you indicated over the appropriate number of years.

In comparison, in the Cash Flow Spreadsheet for the modeled scenario, some of these goals may have been reduced in value or totally eliminated.

Go to the exercise to test your knowledge on identifying your clients' shortfalls and model strategy in the Long-Term Cash Flow spreadsheet.

Note: Results may vary depending on your deployment, planning jurisdiction and simply over time. Your spreadsheets may not look exactly the same as shown here.