Question

Amortization Schedule Project

Program an Excel Spreadsheet to complete a Fixed Payment Loan Amortization Schedule based on the 5 inputs (see 1a below). This spreadsheet should work for any scenario chosen using the 5 inputs below that does not exceed 360 periods. You may start with any 5 inputs as these will be changed multiple times during grading. All cells with number format should have no decimal places. All cells with accounting format should have 2 decimal places. All cells with percentage format should have 3 decimal places. Formulas must use appropriate cell addressing and be efficiently copied to complete the schedule. Every number within the spreadsheet must be positive. If you are given a template, you must use the given template and follow the directions within the template. The required part of this assignment is All of Objective I: Building a basic amortization schedule. The spreadsheet should have the following characteristics in a logical order:

Objective I: Building a basic amortization schedule

1. A data table must appear at the top left that includes: a. Inputs for:

   • Purchase Price – accounting format
   • Down Payment – accounting format
   • Number of Years – number format
   • Compounding periods per year – number format • Annual Interest Rate – percentage format

   b. Formulas for:
   • Principle – accounting format • Payment – accounting format

2. A summarization table must appear at the top right that includes: a. Formulas for:

   • Total Amount Paid – accounting format • Total Interest Paid – accounting format

3. An amortization table able to accommodate any scenario must appear at the bottom containing 6 columns and 360 rows of numbers and formulas with the following titles:
   a. Period Number – number format
   b. Beginning Balance – accounting format, dollar sign in first row only

   c. Payment – accounting format, dollar sign in first row only
   d. Interest – accounting format, dollar sign in first row only
   e. Principle Repaid – accounting format, dollar sign in first row only f. Ending Balance – accounting format, dollar sign in first row only

4. Using logic and the Excel capabilities, format the spreadsheet as follows. When a number of periods smaller than 360 are required, all rows below the last significant row must be blank (i.e. no rows visible beyond the total number of periods, no negative or extremely large and unnecessary numbers).

Answer 1

For amortization table, beginning balance for period 2 is ending balance of period 1, similarly it continuosly goes on. Payment remains same, interest = beginning balance *1%.