Questions for Loan Amortizations
Version: 30-Mar-2009
1. Make a loan amortization spreadsheet (model). Each group must send one (1) model to the class teaching assistant BEFORE the beginning of class. Lei Sun’s email address is: SUNLfin@ust.hk Please name your file “Group_0#_Loans_ABCDE.xls” where “#” is your group number and “ABCDE” are the first initials of your group members’ family names (surnames), in alphabetical order. If Group 5 consists of three students with surnames “Chan”, “Man”, and “Wong”, the file would be called: “Group_05_Loans_CMW.xls”
2. The spreadsheet should have the following “Assumption/Input” cells: - Loan Amount - Rate (APR) - Term (Years) - Frequency (1=Annual; 2=Semi-Annual; 4=Quarterly; 12=Monthly) - Loan Type (see #3 below) The spreadsheet must have the following columns: “Beg Bal”, “Int Exp”, “Princ”, “Total PMT”, “End Bal”. Your group choose to have some additional columns.
3. Your spreadsheet should also be able to handle four types of amortization schedules. Let’s agree the user will input “A”, “B”, “C”, or “D” to get: A Level payment (mortgage style) B Level principal C Bullet loans D Any general schedule*
4. The general schedule* will be given to you in the form of a column of percentage amounts. It will be a column of cells that a user can cut and paste into your worksheet. A general schedule may look like: 10.0% 20.0% 50.0% In the example above, the loan has only three (3) periods. Notice that the percentages add up to 100%. They must add up to 100%. The percentages represent the amount of principal repaid each period. Notice we’ll follow our class convention and indicate external data with a blue font.
5. Your spreadsheet should be flexible enough to handle between one (1) and five hundred (500) periods. To do this, you’ll need to have some “if” statements.
6. Use Times Roman 10pt font throughout. Don’t forget to check the color of the Assumption/Input cells.
Hints and help on the following page
1
Loan Amortizations—Hints and Help
A. You will want to calculate the periodic rate and total number of periods. You can use the total number of periods to check how many rows you need in your spreadsheet (see #5 above).
B. Including some error checks is always good. For example, the PV of the total payments must be _____ . The sum of the principal repayments must be ___________.
C. One (rather strong) suggestion is to have an “Executive Summary” worksheet at the front. All Assumption/Input cells should be on this worksheet. The many results of the loan repayment can be included as well. You can then make a separate worksheet for each of the four types of amortization schedules (mentioned in #3 above). Your executive summary worksheet need only reference the correct worksheet
2