Reference no: EM133066443
Amortization Schedule
Overview:
In this question, you must create a simple mortgage loan amortization schedule using Microsoft Excel. The excel sheet should contain simple tables detailing the input variables (loan amount, interest rate, etc.), schedule of monthly payments and loan summary. Please use the example discussed in class as reference for this question. Below are detailed instructions for submission and creating the amortization schedule.
Note: You should not use any templates for this assignment. Problem Sets that use pre-made templates will not receive points.
Submission requirements:
Your final excel file should include 1 spreadsheet with the full loan amortization schedule for the following loan:
|
Principal ($)
|
$600,000
|
|
Interest Rate (%)
|
5%
|
|
Loan Term (years)
|
30
|
- Save your final excel file as "PS5_UICNetID.xls" using your UIC Net ID. For example: My UIC Net ID is ajames40 so my file will be saved as "PS5_ajames40.xls".
- Submit your excel file on Blackboard under Problem Set 5.
Reference for the excel spreadsheet (this is from the example discussed in class):
Instructions:
- Dollar values and interest rates should be rounded to 2 decimals.
- Convert the loan term to months.
- The data format for all payments, including principal, monthly payments, interest payments and principal payments should be set to "Currency" (specifically, $). The data format for interest rate should be set to "Percentage". Please refer to the example discussed in class on how to format cells.
- Use the Excel formulas discussed in the example in class.
For example, for month # 1 in the reference amortization table given above, the formulas used are:
Payment: =ROUND(PMT($B$3/12,$B$4,$B$2,0), 2)
Interest (for the first month): =ROUND(E9*($B$3/12),2)
Principal (for the first month): =B10+C10
Remainder Balance (for the first month): =E9+D10
For the loan summary table:
Total Payment: =$B$5*180
Total Interest: =SUM(C10:C189)
Total Principal: =SUM(D10:D189)
(The cells used in these formulas are with reference to the screenshot of the example spreadsheet. For an explanation of the formulas, please refer to the example discussed in class.)
- To get the values for the remaining months, select cells B10 through E10. When you rest the mouse cursor over the bottom-right part of the selected area, the cursor will turn to a crosshair (a cross-shaped cursor). Drag the crosshair all the way down to the last month of payment. This populates all the cells until that row with the amortization schedule.
- You should hide the cells for payments intermediate months (e.g., between months 11 and 350) by selecting cells in those rows and using Home > Format > Hide & Unhide > Hide Rows. This removes the intermediate rows from view and creates a shorter table for better presentation. (This has also been discussed in class.)