Applications Programming --- Lab 3

Objective:

• Learn to build a model using Excel
• Get familiar with Excel formula, especially with cell references and some basic built-in functions

References:

• Lab 2 referneces
• General Models
• Excel Models

Problem Description

A family wants to start saving for their child's post-secondary education. The family opened a savings account for exactly that purpose. According to their income and spending situation, they decided to save certain amount every month and deposit the savings to their account. Download the Lab 3 template file to see an example of their saving plan for each month.

The bank holding their savings account guarantees a yearly 2.5 percent interest growth on the money saved in the account at the beginning of each year.

The government also promise to match 25 percent of their new deposit, up to $500, each year.

Your tasks

• Download the Lab 3's template workbook: lab3-input.xlsx

  In the worksheet, develop an Excel model that shows the following columns from the start age of a child until when a child is 17 years old, one year's data in one row:

  • age
  • Initial account balance of the year
  • Yearly interest growth on each year's initial balance
  • Yearly contribution as the sum of all the months' savings
  • Government match for the year
  • End year account balance

  In this model, treat the government match rate, the maximum yearly match amount, the annual interest growth rate, the start age, and the monthly savings as parameters.

  A list of useful Excel functions include SUM, MAX, MIN, and AVERAGE.

• Save your work.

When you work on building the Excel model, you need to pay attention to the following things:

• Did you use the proper parameters in your model?
• Did you use the correct formula in your model?
• Did you use the right type of cell references in your formula? You should write similar formula once and copy-paste them to all the other appropriate cells.

There is a weekly Assignment 3 following this lab.