Order from us for quality, customized work in due time of your choice.
Math 154: Project 2 – Refinance
Plagiarism Warnings: You are expected to work on this project alone. You must
start with a blank Excel document. You may not share your document with
anyone. If you are caught sharing your document, or copying someone else’s
document, then this will be considered a Major Plagiarism Honor Code violation
and it will be reported as such. If found guilty, you may receive an F for the
course.
You may not use any online resources or receive any help from anyone other
than me, this will also be considered a Major Plagiarism Honor Code violation,
and punishment will be the same as above.
Overview: In this project you will be determining how much, if any, money will be saved by a household
deciding to refinance their mortgage at a lower interest rate. The house was originally financed for
$420,000 with a 30-year mortgage that has an interest rate of 6.2%. After five years, mortgage rates
have declined, and the household is considering refinancing options. You will be computing how much
money is saved in interest by each of the options available.
Definitions: Refinancing meaning to get a new loan for something you are already financing. In this
case, you will be getting a new loan for the house which was originally mortgaged at 6.2% for 30 years.
When refinancing, the amount of time remaining on the loan is reset to whatever the length of the new
loan is. For example, if after 5 years they decide to refinance their loan at a lower interest rate for 30
years, the number of remaining years on the loan would reset to 30 years (this means it would take a
total of 35 years to pay off the house from the original purchase date).
Interest Rate: The interest rate is an annual interest rate.
Term: Number of years you have to pay off the loan.
Borrowed amount: This is how much money is being borrowed from the bank to finance your house.
Closing Costs: This is a fee paid to the bank which is refinancing your loan. Typically closing costs are
about 2% to 6% of the borrowed amount. In our project, we will be assuming closing costs of 4%.
Objective: You will be starting with a blank excel workbook and then creating four different sheets
within that workbook. The first sheet will be the amortization schedule for the original loan. This will be
very similar to what was done in excel assignment 3. The next two sheets will contain the amortization
schedules for a 15-year refinance and a 30-year refinance. The 15-year refinance will have a 4.12%
annual interest rate, and the 30-year refinance will have a 4.75% annual interest rate. For each of the
refinanced loans, you will have to pay closing costs which will be added directly into the principal
(borrowed amount) of the loans. On all three of these sheets, you will need to compute the monthly
payments, and total interest along with amortization schedules. For the second and third sheet, the
total interest must include the interest already paid on the original loan. On the fourth sheet you will
provide answers to a set of questionsAllowed Excel Functions/Operations: First, all computations much be done in excel using appropriate
references and using the dollar sign ($) to prevent references when changing whenever appropriate.
The only built-in excel functions you are allowed to use for this project are the PMT function (which will
be used for computing the monthly payments) and the SUM function (which will be used for computing
the total interest). All other computations in excel must be done using only the plus “+”, minus “-“,
multiply “*”, and divide “/” operations. Any deviation from this may result in significant point
deductions.
Finally, cells must be appropriately formatted. Any cell containing currency should be formatted as
currency in US dollars, and any cell that contains a percent must be formatted as a percentage. Any
deviation from this will result in a point deduction.
Submission: Your final submission should be an Excel Workbook. Title your Excel Workbook as “Project
2 Workbook – (your last name)”.
Grading: Your grade on this project is distributed as follows:
Original Amortization Schedule Sheet: 35 points
Refinance 15 years Sheet: 25 points
Refinance 30 years Sheet: 25 points
Answers Sheet: 12 points
Appropriate Cell Formatting Throughout the Entire Document: 3 points.
Total: 100 points
Sheet 1: Original Amortization Schedule
Originally, the house was purchased using a $420,000 loan at 6.2% interest for 30 years. You will use the
first sheet of the excel workbook to create the amortization schedule for this loan:
• First, change the name of the sheet from Sheet1 to Original Loan
• In cells A1 – A4 enter the following labels in separate cells: Principal, Interest Rate, Term,
Monthly Payment.
• In cells B1 – B3, enter the appropriate values in each cell from the information given previously.
• In cell B4, you will use the PMT function to compute the monthly payment. Recall that the first
entry in the PMT function is the interest rate per period, which in this case would mean the
monthly interest rate. The second entry is the total number of periods, and the final entry is
borrowed amount. You will notice that the PMT function will produce a number that is red and
has parenthesis around it, this is because the number comes out as negative. The reason for
this was discussed in class. However, we want the number to come out positive to make it
easier to use. To do this, place a minus sign “-“ in front of the PMT function.
• In cell A5 input the label Amortization Schedule
• In cells A6, B6, C6, D6, E6 we will enter the following labels: Payment #, Balance, Monthly
Interest, Monthly Principal, New Balance
• Next, merge and center the cell A5 so that it is centered on the table below.
You will now create the amortization schedule for the entire 30-year loan. You must do all the
computations in excel with appropriate references including the use of dollar signs ($) in your
formulas where appropriate. You may not change the design of the table.
• In cell C1 enter the label Total Interest, and then in cell D1 compute the total interest paid on
this loan.
Sheets 2 and 3: 15- and 30-Year Refinance Schedules
After 5 years, the decision is made to refinance the house using the lower available interest rates.
Create two new sheets, one labeled Refinance 15, and one labeled Refinance 30. On both sheets, the
setup will be similar to the first sheet with some slight modifications.
• On both sheets, cells A1 – A4, and C1 will be the same.
• On sheet Refinance 15, cell A5 will be relabeled to 15 Year Refinance Schedule
• On Sheet Refinance 30, cell A5 will be relabeled to 30 Year Refinance Schedule
• On both sheets, the Principal amount (cell B1) will be balance of the original loan after
exactly 5 years of payments, plus 4% closing cost. These closing costs should be directly
added to the balance of the loan. For example, if the remaining balance of the loan was
$100,000 (note that it will be way more than this), then after refinancing the new balance
would be $104,000 (that is, a 4% increase from the remaining balance).
• On Sheet Refinance 15, the Interest Rate will be 4.12%
• On Sheet Refinance 30, the Interest Rate will be 4.75%
• On Sheet Refinance 15, the Term will be 15.
• On Sheet Refinance 30, the Term will be 30.
• You will now do all the same computations for the Monthly Payment and the Amortization
Schedule as in Sheet one (remember that Refinance 15 is only a 15-year loan, so the number
of payments should be significantly fewer).
• Now, in cells D1 on both sheets you will compute the Total Interest Paid. This will include
any interest already paid on the original loan. You will have to get those values from the
first sheet.
Sheet 4: Questions
On sheet 4, you will need to answer the following questions:
1. In cell A1, answer the following question: How much money in interest is saved if the 15-year
refinance option is chosen?
2. In cell A2, answer the following question: How much money in interest is saved if the 30-year
refinance option is chosen?
3. In cell A3, answer the following question: Which of the three options (keep the original loan,
refinance to a 15-year mortgage, or refinance to a 30-year mortgage) would be the best choice
assuming you could afford the monthly payments? Explain your reasoning.
4. In cell A4, answer the following question: Which of the three options would be the best choice
for a family on a limited monthly budget? Explain your reasoning.
Project Checklist
Order from us for quality, customized work in due time of your choice.