Assessment: Amortization and Data Visualization


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.


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