Assessment: Amortization and Data Visualization

Instructions

As you’ve seen in class, there are a lot of factors and variables involved in calculating mortgages.

Most banking websites have mortgage calculators that provide some estimates, but they do not present all the information needed to make a decision. That’s where you come in.

In your group, you will construct an Excel file that will display comprehensive information about a possible mortgage. Using Excel functions and formulas, you will calculate all the required values for every step of an amortization period.

Since users are generally interested in end products and summaries, your file will output the requested mortgage information on an easily accessible dashboard. All the calculations will be done on different sheets of the workbook.

Criteria

Content

Create an Excel file that will display important information on a dashboard. All calculations for this dashboard should be done on secondary sheets.

At a minimum, the dashboard should show the following:

  • Value of PMT based on user-entered variables/chosen options
  • Partial amortization schedule for a chosen year, with totals for that year (for simplicity, you may set it to show 12 payments, regardless of the number of payments per year)
  • Line graph showing remaining balance over entire mortgage
  • Pie chart displaying percentage of PMT that goes to interest and to principal (can be set to show details for a single payment or group of payments)
  • Amount of interest/principal paid between selected payments and overall mortgage remaining after a chosen payment
  • Area where lump sum prepayments can be input to see how they may affect the remainder of the mortgage

Formatting

Besides summarizing and displaying a lot of information, a significant feature of a dashboard is its appearance. When creating your dashboard, consider the following aspects:

  • Consistent and appealing colours, borders, fonts, etc.
  • Proper cell formatting
  • Dropdown menus for the user to pick options from
  • Size, layout and organization of objects, cells, drop-down menus, etc.
  • Data validation and error messages if the user entering something invalid

Additionally, your group must include the following Excel features in your file:

  • First sheet renamed to clearly indicate what is displayed
  • Other sheets hidden
  • Headers and footers on the first sheet that include group member names, the project title, the sheet name, the date and page numbers. Sheet name, date, and pages numbers should be automated.
  • An appropriate print area for the first sheet
  • Proper print formatting. Consider features such as orientation, alignment, margins, etc.