Mortgage Assignment Instructions This document may be modified for clarification

Mortgage Assignment Instructions
This document may be modified for clarification, especially in response to student questions.
Instructions
Answer the various parts of the questions using Excel as covered in class. Use Excel to determine each requested figure and then type (or copy & paste as values) your answer into the relevant cell in the table provided for each question.
Highlight cells in Excel with the answers to each question. This will make it more likely that any potential partial credit will be earned.
Do not round any intermediate steps in Excel. The only rounding that should take place is with your final answers that are typed (or copied and pasted) into the tables provided in this document for each question. Figures in the tables should be rounded to the nearest cent (for example, $123.45, $987.60, or $55.00). Note that displaying figures to a certain number of decimal places in Excel is not considered rounding, as the underlying figure isn’t actually rounded. It’s simply displayed in a more digestible way.
Submission
Submit two files electronically through Blackboard (via the relevant item in the “Assignments & other deliverables” folder):
1)A pdf file with answers to the various questions. Do not submit a Word (or other word processing software) file, a bunch of pdf files, or a bunch of picture files (like jpg or png). If I cannot open a file (which is very, very rarely an issue), then you may not receive any credit for it. Therefore, you are responsible for ensuring that your document was submitted properly and fully accepted by Blackboard. In general, emailed submissions are either not accepted or accepted with a deduction. 2)An Excel file that shows your work. This file should include 3 spreadsheets – one for each question.
Grading
Grading is based on timeliness, adherence to directions, and accuracy. Your work in obtaining answers (the Excel file) may influence grades as well, so it is important that the spreadsheets in the Excel file be easily followed and understood. For example, it’s possible that any incorrect answers in your spreadsheet would be replaced with the correct answer to see if subsequent answers are then correct. This is why it is important to highlight the cells that correspond to the figures asked for in the questions.
Questions to answer in your submission
See the following pages.
1. Red Oval Technology just purchased a factory for $6,850,000. The firm financed the purchase with a mortgage loan that involves monthly payments, matures in 9 years, and amortizes over 23 years. The initial annual interest rate on the loan is 9.48%. The loan involved points of 1.70%, other up-front financing costs paid to the lender of $20,000, and up-front financing costs paid to third party service providers of $15,000. The original LTV ratio for the loan is 85.70%. Report all answers to the nearest cent (for example, $1.23 or $1,234.56), but do not do any rounding in the Excel spreadsheet.
Question part
Answer
What is the monthly payment associated with the mortgage loan for month 1?
What is the remaining mortgage balance after the monthly payment for month 8 is made?
What is the remaining mortgage balance after the monthly payment for month 33 is made?
What is the remaining mortgage balance after the monthly payment for month 58 is made?
What is the remaining mortgage balance after the monthly payment for month 83 is made?
What is the remaining mortgage balance after the monthly payment for month 108 is made?
2. Red Oval Technology just purchased a factory for $6,850,000. The firm financed the purchase with a mortgage loan that involves monthly payments, matures in 9 years, and amortizes over 33 years. The initial annual interest rate on the loan is 9.48%. The loan involved points of 1.70%, other up-front financing costs paid to the lender of $20,000, and up-front financing costs paid to third party service providers of $15,000. The original LTV ratio for the loan is 68.20%. Report all answers to the nearest cent (for example, $1.23 or $1,234.56), but do not do any rounding in the Excel spreadsheet.Question part
Answer
What is the monthly payment associated with the mortgage loan for month 1?
What is the remaining mortgage balance after the monthly payment for month 8 is made?
What is the remaining mortgage balance after the monthly payment for month 33 is made?
What is the remaining mortgage balance after the monthly payment for month 58 is made?
What is the remaining mortgage balance after the monthly payment for month 83 is made?
What is the remaining mortgage balance after the monthly payment for month 108 is made?
2. Velo just purchased a house for $714,600 and financed the purchase with a “5/1” loan that is fully amortizing, matures in 28 years, and involves monthly payments. When the annual interest rate resets, it will reset to LIBOR + 135 basis points with a maximum rate increase of 120 basis points per reset and a lifetime cap on the interest rate of 240 basis points over the initial annual interest rate. The initial annual interest rate on the loan is 8.64%. The loan involved points of 1.70%, other up-front financing costs paid to the lender of $4,000, and up-front financing costs paid to third party service providers of $2,000. The original LTV ratio for the loan is 79.30%. Report all answers to the nearest cent (for example, $1.23 or $1,234.56), but do not do any rounding in the Excel spreadsheet.
Question part
Answer
What is the monthly payment associated with the mortgage loan for month 29?
What is the remaining mortgage balance after the monthly payment for month 36 is made?
What is the monthly payment associated with the mortgage loan for month 65 if LIBOR is 7.53% on the relevant date for resetting the rate?
What is the remaining mortgage balance after the monthly payment for month 70 is made?
What is the monthly payment associated with the mortgage loan for month 78 if LIBOR is 8.13% on the relevant date for resetting the rate?
What is the remaining mortgage balance after the monthly payment for month 79 is made?
What is the monthly payment associated with the mortgage loan for month 87 if LIBOR is 9.81% on the relevant date for resetting the rate?
What is the remaining mortgage balance after the monthly payment for month 92 is made?
What is the monthly payment associated with the mortgage loan for month 100 if LIBOR is 5.11% on the relevant date for resetting the rate?
What is the remaining mortgage balance after the monthly payment for month 100 is made?
4. Vera just purchased a house for $714,600 and financed the purchase with a “5/1” loan that is fully amortizing, matures in 28 years, and involves monthly payments. When the annual interest rate resets, it will reset to LIBOR + 135 basis points with a maximum rate increase of 120 basis points per reset and a lifetime cap on the interest rate of 240 basis points over the initial annual interest rate. The initial annual interest rate on the loan is 8.04%. The loan involved points of 1.70%, other up-front financing costs paid to the lender of $4,000, and up-front financing costs paid to third party service providers of $2,000. The original LTV ratio for the loan is 79.30%. Report all answers to the nearest cent (for example, $1.23 or $1,234.56), but do not do any rounding in the Excel spreadsheet.
Question part
Answer
What is the monthly payment associated with the mortgage loan for month 29?
What is the remaining mortgage balance after the monthly payment for month 36 is made?
What is the monthly payment associated with the mortgage loan for month 65 if LIBOR is 7.53% on the relevant date for resetting the rate?
What is the remaining mortgage balance after the monthly payment for month 70 is made?
What is the monthly payment associated with the mortgage loan for month 78 if LIBOR is 8.13% on the relevant date for resetting the rate?
What is the remaining mortgage balance after the monthly payment for month 79 is made?
What is the monthly payment associated with the mortgage loan for month 87 if LIBOR is 9.81% on the relevant date for resetting the rate?
What is the remaining mortgage balance after the monthly payment for month 92 is made?
What is the monthly payment associated with the mortgage loan for month 100 if LIBOR is 5.11% on the relevant date for resetting the rate?
What is the remaining mortgage balance after the monthly payment for month 100 is made?
5. Vito just purchased a house for $714,600 and financed the purchase with a “5/1” loan that is fully amortizing, matures in 28 years, and involves monthly payments. When the annual interest rate resets, it will reset to LIBOR + 147 basis points with a maximum rate increase of 132 basis points per reset and a lifetime cap on the interest rate of 264 basis points over the initial annual interest rate. The initial annual interest rate on the loan is 8.64%. The loan involved points of 1.70%, other up-front financing costs paid to the lender of $4,000, and up-front financing costs paid to third party service providers of $2,000. The original LTV ratio for the loan is 79.30%. Report all answers to the nearest cent (for example, $1.23 or $1,234.56), but do not do any rounding in the Excel spreadsheet.
Question part
Answer
What is the monthly payment associated with the mortgage loan for month 29?
What is the remaining mortgage balance after the monthly payment for month 36 is made?
What is the monthly payment associated with the mortgage loan for month 65 if LIBOR is 7.53% on the relevant date for resetting the rate?
What is the remaining mortgage balance after the monthly payment for month 70 is made?
What is the monthly payment associated with the mortgage loan for month 78 if LIBOR is 8.13% on the relevant date for resetting the rate?
What is the remaining mortgage balance after the monthly payment for month 79 is made?
What is the monthly payment associated with the mortgage loan for month 87 if LIBOR is 9.81% on the relevant date for resetting the rate?
What is the remaining mortgage balance after the monthly payment for month 92 is made?
What is the monthly payment associated with the mortgage loan for month 100 if LIBOR is 5.11% on the relevant date for resetting the rate?
What is the remaining mortgage balance after the monthly payment for month 100 is made?