Retirement Planning and Forecasting Application

Project Description

We are a company that provides retirement planning and wealth management services.  We have been using an outdated DOS-based program to provide forecasts to our clients.  We would like to develop a simple application that will take a set of inputs and provide forecasts as outputs based on a specific logic.  While we are attaching the results that are generated for each forecast, it would be helpful for us to hire someone who has the ability to run the DOS-based program in Windows environment to reverse-engineer formulas that may be unclear.  The DOS-based program can be downloaded here: https://www.dropbox.com/s/jdho2uxftdqk0lz/qpro.zip

Here are the different types of forecasts that we are currently running and their examples are attached. The pdf file names correspond to the executable files in the pps1 folder within the zip file.

Retirement Funds Forecaster (see attachment RCP1.pdf)

Column A = Client expenses.  Relatively simple calculation, The expense grows by whatever inflation number we choose to use.  In this case it is 4.00%.  The only issue here is that a mortgage, car payment, long term care premium won’t inflate so I manipulate the cells to take the cell above $6820, subtract out the mort payment or LTC premium, multiple the net number by 4.00% and then add the mort and LTC premium back in and replicate the process each year until the mort is paid off.  Sometimes I will have to add on an expense such as a tax increase which will then flow through the following expenses.

Column B = monthly savings.  Used when client is still working and has 401(k), company match  and or surplus from income.  This grows at whatever rate we choose.

Column C = social security for each spouse if married – we choose to grow at 3.00% but should be any rate we choose.

Column D = pension column, same as social security, grows at whatever rate we choose if any.  This column needs to have several pension inputs as sometimes a client can have 2 monthly pensions and the spouse may have 1, each may grow at a separate inflation rate if any so we need flexibility here.  In this example there is just the 1 monthly pension of $1,283 and doesn’t inflate. 

Column E = Shortage or surplus.  This is a simple calculation of column A – column C and column D = column E  In this example the expense of $6,820 exceeds the social security and pension expenses by $1,716 so that amount is coming out of the beginning capital. 

Column F and G are one of the same just and example of different rate of return.  This is the more challenging calculation which it is most likely easier for the developer to see the formula because you have the rate of return and you have a monthly shortage working at the same time.  I have a hard time understanding the calculation when I click on the particular cell.  Also, I subtract on 1 time expenses at random times for car purchase, roof etc and sometimes add on lump sums for sale of house or inheritance and random years.

Life Insurance Forcaster (see attachment CNA1.pdf)

Attached is the output for the life insurance or capital needs analysis.  It would be a good one for the developer to look at this file to see the inputs. 

• The standard age, retirement age, life expectancy age etc carries from the Data tab.
• If client is working we put in their income.
• Then we put in the monthly expenses pre retirement if working and post retirement for everyone.
• Then we put in the expected social security and any pensions that they have, may be several.
• Input immediate large expenses, funeral, debts etc.
• Estate settlement costs
• Group life insurance if they have any.
• Individual life insurance if they have any.
• Current liquid assets.
• Rate of return – we choose
• Inflation rate – we choose
• Output shows Capital Needs A and Capital Needs B.  One is for each spouse in the case the other passes away.

Loan Modeler (see attachment Loan.pdf)

This one is straight forward. We enter # payments, interest rate and amount borrowed and it does the rest.  This one is like any other amortization schedule.

I do build other loans from the original loan file. So I have their mortgage as the “Loan File”

Then I copy that file and make one for their car loan or home equity etc.  I just want to point out that I may need multiple loan files.

Education Fund Forecaster (see attachment EDF1.pdf)

This is straight forward too.  Might help to have developer view the inputs.

• We enter the kids name, age (which I think should be DOB instead), age college begins and cost of college.
• There are multiple lines for additional kids.
• Then we enter money already saved, if any.
• Then we enter inflation rate for college and the rate of return for the capital saved.  Both rates we determine.
• The program does the rest to figure out how much you would need today to cover the cost or how much to save monthly on a linear line.

Long Term Care Forcaster (see attachment LTC1.pdf)

This one is very similar to the Retirement Forecasters.  Output is pretty close. The difference is showing impact of having a long term care need and whether or not you have coverage to pay for it.

We found a flaw with the logic of the original design of this which I will point out below.

So all the inputs are the same from the Retirement version, until you get to line 34-37.  The developer should look at this.

Here we put in the amount of expense and coverage for the need. 

We put in the age the need begins and ends.  We usually do 3-5 years

Then we enter the inflation protection rate, if any.

So we basically recommend the policy we think they should have which fills in these values.  We would show 6000 month need and coverage with 5% inflation rate.

The we enter the beginning capital and the rate of return for each column, No LTC  (Column F) or with Long Term Care (column G).

Column A is the monthly expense, including the cost of the LTC insurance as an add on that doesn’t inflate.  Similar to a mortgage.

• Here is the issue in the current logic.  The comparison is showing what happens when you HAVE a long term care need and you either have coverage or you don’t.  It doesn’t factor in if you don’t have the coverage, you don’t have the cost of the premium.  Column A should be less the premium when factoring in column F.
• Column G shows if you have a long term care need and you have the coverage, how it looks which is accurate because in order to have the coverage you would need to have the expense.  Column A should have a higher cost (ie the premium). So technically, it isn’t a fair comparison as your expense is too high in the not having coverage example.

Column B is monthly savings, until you get down to the age where you have a need for long term care.  This shows the actual benefit coming in to offset the cost rising.  You can see the expense jump up at age 80, column D goes up as no insurance to cover the additional burden, column E doesn’t have a bump as the insurance covers the cost.

Age 83, the cost drops back down as most likely the client in need of the care has passed on and the remaining spouse no longer has that cost or the premium for the coverage.

We would like to segment this project into six milestones, one for each forecasting application and the last milestone for a system that saves client accounts with easy modification of input variables. Please provide (1) your methodology; (2) the technology stack that you would use to develop the system; (3) timeline; and (4) your expertise with this kind of forecasting system development.  We are technology agnostic but would like to ensure that the system is user-friendly and will not be dependent on on our operating system.  We also need to ensure that they system can be easily backed-up so we don't lose client data.