Homework answers / question archive / Project 2 Home Loan Analysis Instructions this file with instructions for the ProjectProject 2 -Home Loan Analysis Template

Project 2 Home Loan Analysis Instructions this file with instructions for the ProjectProject 2 -Home Loan Analysis Template

Finance

Share With

---

Project 2 Home Loan Analysis Instructions this file with instructions for the ProjectProject 2 -Home Loan Analysis Template.xlsx–the file for your workSave the Template file and change the name to:Project 2 -Home Loan Analysis Lastname_Firstname.xlxsusing your own name instead.You will submit yourcompleted Excel filecontaining your calculations and answers to the questions.

Project 2 – Home Loan Analysis 

TO GET STARTED:  Download the two files from Canvas:

Project 2 – Home Loan Analysis Instructions.pdf  - this file with instructions for the Project Project 2 - Home Loan Analysis Template.xlsx – the file for your work

 

Save the Template file and change the name to:  

Project 2 - Home Loan Analysis Lastname_Firstname.xlxs using your own name instead.  You will submit your completed Excel file containing your calculations and answers to the questions.  

 

EXCEL LEARNING RESOURCES:

NOVA provides several tutorials in using Excel.  Check out:  https://blogs.nvcc.edu/lomastermath/resources-for-mth-154/

Microsoft provides an excellent collection of short xcel topic tutorials (bite-size lessons with 1-2 minute videos – the first 8 are highly recommended): https://support.microsoft.com/en-us/office/excel-video-training-9bc05390-e94c-46af-a5b3d7c22f6990bb?ui=en-us&rs=en-us&ad=us

YouTube has thousands of useful videos.  Here are some good ones:

Excel beginners video (20 minute video covering basic stuff – recommended): 

https://www.youtube.com/watch?v=rwbho0CgEAE&list=PL_iwD7O7FG7jzLQIYm69Gx3hvXVUG7C5&index=1&t=436s

Intermediate Excel Skills video (more than you need):

https://www.youtube.com/watch?v=lxq_46nY43g&list=PL_iwD7O7FG7jzLQIYm69Gx3hvXVUG7C5&index=2

 

PROJECT BACKGROUND:   

People can purchase homes costing far more money than they have on hand.  What makes this possible is the availability of home mortgage loans that are paid back monthly over long periods of time, say 20 to 30 years.  One common type is the fixed interest loan, which has the same interest rate and monthly payment over the life of the loan.  You will analyze such loans here.

In this project, you will use Excel to analyze the loan costs for purchasing a townhouse costing $300,000.  You will ignore other costs including taxes, insurance, maintenance, homeowner association fees, etc. and just focus on the loan. You will compare two alternative repayment strategies to see if they may save money.

You will begin with an Excel template into which you will enter values and formulas based on detailed requirements below.  Based on the results you generate, you will answer several questions in the cells provided in the spreadsheet.

             

First, some definitions.  

• Sale Price (or Purchase Price):  This is the price for the house if it were purchased for cash. Few buyers have this much cash, so most purchase using a mortgage loan.
• Down Payment:  This is the fraction of the sale price that is actually paid in cash.  The remainder must be borrowed with a loan. Down payments of 5%, 10% and 20% are common.
• Loan Amount: This is the sale price minus the down payment.  This is also the initial Principal amount.
• Loan Period: This is the duration over which the loan will be paid back.  Home loans are usually paid back over 20 or 30 year periods, meaning 240 or 360 monthly payments.
• Interest Rate:  The percentage rate of interest charged annually on the loan. 
• Periodic Rate: The annual interest rate divided by the number of periods in a year.  Monthly is most common, so divide the annual interest rate by 12. 
• Monthly Payment:  Most loans are paid in equal monthly payment amounts over the entire period of the loan.  Each payment is part principal P and part interest I.
• Principal: The loan amount which must be paid back.  The Principal Balance is the amount remaining to be paid off at any time, which decreases as payments are made. The Principal Balance reaches -0- when the loan has been entirely paid off.
• Interest: The payment amounts made to the lender in return for giving you the loan.  This is in addition to the principal that must be repaid.  Interest is usually compounded with each period (usually monthly), meaning that it is added to the remaining Balance.
• Amortization: The process of paying off the loan in regular installments.  “Mort” comes from Latin meaning “kill”, so amortization is “killing off” the loan.
• Amortization Schedule: A monthly breakdown of each payment showing the amount of interest, the amount of principal, and the remaining balance after the payment.

In the “old days”, banks would print out an amortization schedule for you when you got your loan.  Today, you can generate it easily using spreadsheet software like Excel.  You will do that for this project.

You will need to use the following Excel capabilities:

• Entering values into cells
• Using cell references and values in formulas
• Using absolute and relative cell references
• Copying formulas into a large number of related cells
• Using built in Excel formulas including loan payment calculations [function PMT( ) ]and summation of groups of cells [function SUM( ) ]

PROJECT REQUIREMENTS:

First download the file “Project 2 - Home Loan Analysis Template.xlsx” for your use.  Rename it to be “Project 2 - Home Loan Analysis Lname-Fname.xlsx” where you use your own name. Open the file in Excel and enter your name in Cell C4 and your class section in Cell I4.  A portion of this file is shown below.

 

 

 

NOTE:  In this Project, ALL the values you will enter directly are in the gray-shaded fields, and ONLY in the gray-shaded fields.  The rest of the cells in the spreadsheet are to be calculated, using formulas and references that you will provide.  Although there are 360 rows in the Amortization table, you will properly complete ONE of the rows, then when it is correct, COPY it to the remaining 359 rows.  You’re welcome.

STARTING VALUES:  You will purchase a townhome with a sales price of $300,000.  You will make a 10% down payment, and secure a mortgage loan for the remainder at a 6% annual interest rate for a period of 30 years.  

PART 1 – BASE AMORTIZATION (35 POINTS)

First, build the Base Amortization table on the left side.

1. Enter the appropriate values in Cells D7 through D1
2. Add simple formulas in Dells D12 through D15 to set up the loan calculations
3. Using references to Cells D13 through D15, use the built-in Excel function PMT( ) to calculate the monthly payment in Cell C17.  Read the Help information for function PMT(_) for information about its inputs.  If you got these right, the calculated payment will be NEGATIVE as indicated by its formatting in Red with parentheses as in (1,000.00)              

Month -0- of the table consists only of the initial amount of the loan in Cell E23.  No other calculations are needed in this row.

For each succeeding month: 

1. The Monthly Interest will be the Monthly Int Rate times the previous month’s Balance, and it will be positive.
2. The Monthly Principal will be the Monthly Interest plus the Monthly Payment.  Since the Monthly Payment is negative, the Monthly Principal result should be negative.
3. The Balance will be the previous month’s Balance plus this month’s Principal.  It should be a bit smaller than the previous month’s Balance.

IMPORTANT:  

• First, work out these formulas for Month 1.  ONLY use cell references in your formulas – do NOT retype any of the input values in these formulas.  
• Use absolute cell references of the type $D$14 for references to the Monthly Int Rate and similarly to the calculated Monthly Payment.
• Use relative cell references to the previous month’s Balance, e.g. D23. Now, complete the rest of the table
• When you are happy with the row for Month 1, select Cells C24 through E24 (that is, Range C24:E24) and Copy them
• Then Paste these into cells in the range C25:E383.  Boom! 
• Add Column Totals for Total Interest in Cell C385, and Total Principal in Cell D385

If your formulas are correct, you should see that the final Balance entry for Month 360 is -0-, and the Total Principal repaid is the negative of the loan amount.  Now go back to Cell D18 and enter a reference to the Total Interest you calculated in Cell C385.

Answer the following questions in the Cells provided in the Worksheet in Col M.  Remember that the total cost to you over the 30 years will be the sale price (paid as down payment + principal repayment) plus the total interest.  For Questions 2 and 3, calculate your answers by entering new Input Data in Cells D9 or D10 to update the entire amortization and note how much things change.

1. (5 Points) For the original loan, how much total interest will you pay over 30 years?  Is it more or less than the purchase price of the house?
2. (5 Points) “What if” analysis.  If you could get a more favorable interest rate reduced to 5%, how much total interest would you pay over 30 years?  Are you surprised at the difference that 1% in interest rate makes?
3. (5 Points) If you could reduce your down payment to from 10% to 5% (but interest stayed at 6%), would it change the amount of interest that you pay?  Would it be more or less?

             

PART 2 – EXTRA PAYMENT AMORTIZATION (35 PTS)

For this part, you will build a second amortization table.  It is similar to the first, but adds in a payment of additional principal each month.  This strategy can reduce the time until the loan is paid off and potentially save some money for the homeowner.

Use the same loan values you entered/calculated in Cells D8:D17.  Enter a new (negative) value of (200.00) in Cell J8 for extra principal.  You will type in -200.00 and Excel will format it using red parentheses.  Now build the new amortization table similar to how you did before, except that you will insert an absolute reference to $J$8 in cell J24, and update your formula for the New Balance to be the (Previous Month Balance + this month’s Interest +this month Principal + this month Additional Principal).  

IMPORTANT:  This new table will NOT use ANY of the monthly line amounts in Cols C, D and E calculated from the original amortization. DON’T DO IT.  Use only the loan and payment amounts you calculated in Cells D12 through D17, and create new but similar formulas in Cols  H, I, J and K.  

Once Month 1 is working correctly, copy Cells H24:K24 into the Range H25:K385.  You should see a constant -200.00 in each cell in Col J, and the New Balance should decrease a bit more rapidly than the first table.  

At some month, the New Balance will switch from positive to negative.  That first negative month represents when the loan will be paid off.  In actuality, the Bank would reduce your final payment to make it result in a zero balance, but we won’t worry about that here. 

Create one final formula in Cell J12 by using the SUM( ) function to sum the interest from cells in the Range H24 to the cell in Col H where the New Balance goes negative.  

Answer the following questions in Cells in Column M.

4. (5 Pts) In what month will the loan be paid off?
5. (5 Pts) How does the New Total Interest compare to the original Total Interest?
6. (5 Pts) What factors should the homeowner consider when deciding whether to pay additional principal each month?

REFLECTIONS – BONUS 5 POINTS

Give your reflections on the project. What did you learn about Excel that helped you most in this project?    For example, you might reflect on what you learned about organizing many similar calculations, or what were the most useful Excel functions, or what problems you encountered. 

Did you discover any useful Excel techniques?  Just share your insights.