Description

Excel Project: Loan Amortization Scenario

In this Excel Project you will create an Amortization Schedule for a potential loan. You will research buying a home or a car, answer the following questions and create an Excel spreadsheet that includes a screenshot of the home or car, interest rate offer and an amortization schedule for that loan. Choose one of the following scenarios (buying a home or buying a car) and follow the instructions. Here is the video that you will be referencing for this project: Loan Amortization Schedule

Buying a Home (80 points)

1.       Go to www.har.com, www.zillow.com, or other area real estate website and search for a home in an area that you desire that you can “afford”. Take a screenshot of that page and insert the image in your Excel spreadsheet on sheet 1. In a textbox on Sheet 1, in a paragraph labeled “House” write why you chose this house and why you think you can “afford” it. This can be a fictional reason based on a future (a job you plan to have when you graduate, etc.) or potential (you’ve saved x amount for y years, etc.) scenario. (5 pts)

2.       Calculate the customary 20% down payment on sheet 1 of your Excel spreadsheet. Make sure to LABEL it so that I can find it. (5 pts)

3.       Research loan offers and find the best mortgage rate for the down payment you calculated. That is, research a loan offer for the amount you need to take out AFTER you have paid the calculated down payment. Your mortgage must be 20 years or more. (Most mortgages are for 25 -30 years.) YOU MAY NOT USE A 0% APR. Take a screenshot of that page and insert the image in your Excel spreadsheet on sheet 1. (5 pts)

4.       Create a second sheet in your Excel Spreadsheet. Watch this video and format your second sheet with these items. (10 pts)

5.       Calculate the monthly mortgage payment, PMT, in Excel on that second sheet as shown in the video (using the formula from Ch. 1). (5 pts)

6.       Using all of this information, on the second sheet of your Excel spreadsheet, create an amortization schedule. That is finish the chart with the total number of months as shown in the video. (35 pts)

7.       Using Excel find the total amount paid over the life of the loan and the amount paid in interest. Make sure you label these items so that I can find them! (5 pts)

8.       Create a relevant chart for this data on sheet 2. Make sure that you label and that it makes sense! (15 pts)

9.       Create a third Excel sheet (sheet 3) and complete this reflection in a textbox:

a.       In a paragraph labeled “Loan Analysis” answer the following questions: Were you correct in question one? Can you afford the house or would you need to make adjustments? Would you choose a different house or a different loan offer now that you have completed the amortization schedule? Aside from choosing a different house or interest rate (often this is not possible), what would make the mortgage more affordable? (10 pts)

b.      In a paragraph labeled “Personal Reflection” answer the following questions: What did you learn while doing this project? What did your struggle with? Are there any other observations you want to offer? (5 pts)

10.   Turn in your Excel file to the Project assignment in Canvas.