2016 Round 2, Section 2: Fund the Future

Description
This workbook is taken from the Second Round of the Modeloff financial modeling world chamionships.

Follow the instructions in order to complete the model:

You are working as a financial advisor and have been asked to perform some modeling of projected long term savings for four different people: Alice, Bob, Charlie and Diana. Each of the 4 people will create a special savings account and invest money at regular intervals, with the aim of being able to retire from work once their savings account reaches a balance they are happy with. The accounts will also earn an investment return. Using the information and assumptions provided, you will need to calculate the forecast balances of the accounts at several points in the future as well as perform other analysis as asked.

Your model can be either quarterly or annually as you deem appropriate. It will need to cover a date
range starting at 31 December 2016 and run for 45 years.

Initial Account Deposit at 31 Dec 2016
This is the opening balance of the savings account at the beginning of the model.

Annual Salary in 2017
The annual salary in the first full year of the model. This is used to help size the deposits into the account. The salary may grow each year based on the information provided.

Core Deposits into Savings Account (beginning in 2017)
The regular amount deposited each quarter or each year. It is expressed as a % of salary.

Additional Deposits into Savings Account
Self-explanatory.

Date the Account is Withdrawn and Closed
The final date to model the account balance to, and the target retirement date of each person. Remember to INCLUDE any deposits and investment returns scheduled to occur on the account closing date before calculating the final closing balance.

Forecasted Investment Returns
Self-explanatory. The investment returns are added to the account balance either each quarter end or each year end (31 Dec) as stated.

OTHER ASSUMPTIONS
• Assume that all account cashflows (deposits, investment returns, withdrawals etc) occur at the
end of the final day of the period. Investment returns can be calculated based on the opening
balance of each compounding period.
• Assume all periods are of equal length. You should not consider the actual number of days in any
given quarter or year. (In other words, perform all relevant calculations on a 30/360 basis.)

Allotted time: 36 minutes

Once finished, feel free to upload your model to your own dedicated author channel!