Problem # 4

Use Excel to create an amortization table for a loan liability (use the same format we used in class). The excel users guide for accountants will be very helpful for this problem. Do not down load a pre-made template off the internet.

1. Use the last 6 digits of your student ID # for the amount of the mortgage.

2. Use your age for the number of years, however, do not use a number greater then 26 (multiple by 12 to get the number of monthly payments)

3. Use your month and day of birth (mm/dd) to determine the yearly interest rate as follows:

If you were born July, 28, your rate is 7.28%

If you were born on Feb. 1, your rate is 2.01%

4. Given the information in (a), (b) and (c), calculate the monthly payment using the excel spreadsheet. Assume that the payment is made at the end of each month. The payment should be in terms of dollars and cents. Remember to set it up in a way that if I change a value, it will recalculate the monthly payment. Construct a table that uses your data and the excel formula to calculate your monthly payment. (if I change the amount of the loan, the periods of the loan or the interest rate of the loan; the table will recalculate a new payment and the amortization table should also update). See below for the set up for the first few cells of the worksheet.

5. Construct the amortization table. The loan balance at the end should be very close to zero after you make all the required payments (there may be a small rounding difference, but it should be very small in relationship to the loan). The worksheet should be all formula based.

6. Be sure to label all input items and columns.

7. This print out will take more than one page.

	A	B	C
1	Amount of Mortgage	(input your value)
2	Interest Rate (yearly)	(input your value)
3	Number of years	(input your value)
4
5	Amount of Monthly Payment	Using the values in cell B1, B2, and B3 have excel calculate the monthly payment

Remember that cell B5 should recalculate with a new value when any of the inputs in B1, B2, or B3 are changed

Be sure you are calculating monthly, not yearly payment amounts. (you may need to use the Excel help function)