Question

ACCY 415

Individual Assignment #1

Assume you have graduated from college, are earning a steady income and are considering purchasing the condo you are currently renting. You can purchase the condo for $258,000. You have saved $24,000 for the down payment and the bank is willing to loan you $234,000 under a 30-year fixed rate mortgage. The sale will take place at the end of 2020.   Payments will be due monthly beginning January 31, 2021.

Required:

1. Using Microsoft Excel, develop a spreadsheet to amortize the mortgage loan using the following format and using the following interest rates depending on your last name:

Last name begins with:                                   Use an annual interest rate of:

A-G                                                                             4.25%

Loan amount:                                                            Annual interest rate:

Number of payments:                                               Monthly interest rate: (use formula & extend to 5 decimal places)

Loan payment start date:                                         Monthly payment: (use PMT function)     

           

                                                                                                Additional      Loan

Date                Payment         Interest           Principal         Principal         Balance

Except for the date (which you should calculate using a fill series) there should be no hard-coded numbers in these fields, only formulas and cell references. Use the $ sign in cell references to keep a column or row from incrementing by one when copying formulas from one row to the next.

• Use the Excel "auto fill" function or cell handle to fill in the date column of your schedule. (Be sure to select “month” as the fill series and start with the second date at your fill point, since the first date is cell referenced).
• Use the Excel "PMT" function to determine the amount of your monthly payment. Show the payment as positive.
• Format all cells except the monthly interest rate to two decimal places (xx.xx)
• Initially set the "additional principal" column to zero
• Calculate totals for the interest, principal and additional principal columns after the row which represents your final payment (and later add temporary totals after the first year of payments to answer several of the questions).
• Any computations necessary to get to an answer should be in cells to the right of the amortization schedule.

2. Use your spreadsheet to answer the following questions. Use formulas to calculate your answers and show your computations in the upper right of your amortization schedule:

1. What is the date of your last payment?

2. What is the total amount of interest you will pay over the course of the loan?

3. What is the total amount of principal you will repay?

4. What is the total of the mortgage payments you will make during the first year of the loan?

5. How much of these payments will be applied toward the principal amount of the loan?

6. A friend of yours who just happens to be an accountant has told you that interest on your home loan is deductible for income tax purposes (and that rent payments are not). After one year, how much interest will you be able to deduct on your income tax return?

7. Assume you are in a 20% tax bracket for income tax purposes. What is the average after-tax amount of your monthly mortgage payment in the first year? (HINT: Your interest deduction will be the interest times the tax rate. This is the tax savings which should be deducted from the total of your first year payments. Divide this net amount by 12 to get the monthly average).

8. If you were currently renting your condo for $1,100 per month, would it make better sense financially to rent or buy? Why?

1. If you are currently renting your condo for $900 per month, why might it still be better financially to buy versus rent?

3. Assume that after one year you are earning such a good income that you are able to add an additional $100 to your monthly mortgage payment.   Your financial institution will apply this additional payment directly to your outstanding principal balance. Copy your spreadsheet to a new sheet. Beginning January 31, 2022 set the additional principal column of your worksheet to $100 per month. Erase the rows in the worksheet beyond the point that the loan is paid off.   Set the final payment (and additional principal amount of the final payment, if necessary) equal to what is necessary to just pay off the loan. Prepare totals for principal, additional principal and the interest columns and answer the following questions.

1. How much interest will you save over the course of the loan by paying an additional $100 per month on your mortgage payment?

2. How much principal will you pay (think about this logically)?

3. By how many years (and/or months) will you reduce your 30-year mortgage by paying an additional $100 per month?

4. What is the major lesson of this project?

Answer 1