You decide to purchase the rental property from Project 1. Use the provided Excel template to perform the following tasks: 1. On the Amortization tab of the spreadsheet, calculate your down payment, your monthly payment and create a loan amortization table for the life of the loan. Review to the loan amortization tutorial in D2L. Note the total interest you paid at the bottom of the table. You must show your work by using formulas in Excel. 2. On the Closing tab of the spreadsheet, complete the Buyer's and Sellers' closing statements based on the following data: On June 15, Wilbur and Wilma Wildcat ("Sellers") listed their Flagstaff rental property for $285,000 with Re/Max Peak Properties and agreed to pay a 6% commission on the sale price. They have a mortgage for $140,000 remaining on the home. You offered $270,000 and Wilbur and Wilma accepted the offer. At that time, you put down $1,000 of earnest money. You will put 20% down and finance the rest through Bear Down Bank with a 15-year mortgage at a fixed rate of 4.75%. The sale will close on July 1st at which time you will take possession. Bear Down requires a loan origination fee of 1% of the value of the loan along with a property appraisal which will cost $400 and a credit report at a cost of $25. Property taxes for the current year were $1,264 and were paid in full by the Sellers. The Buyer's title insurance policy will cost $800, and the Sellers' title insurance policy will be $1,690. Homeowner's insurance will cost the Buyer $1,586. Recording the deed will cost $50 (equally split between the Buyer and Sellers). Endorsements of $150 would be paid by the Buyer. Courier fees will be $25 for the Buyer and $25 for the Sellers. All prorations are based on a 30-day month. Calculate the costs to the Buyer and the Sellers as a percentage of the purchase/sales price. 1) Use the data provided in the case description to complete the Buyer's and Sellers' Closing Statements below: 5 6 Summary of Buyer's Transaction Summary of Sellers' Transaction 7 Description Contract Purchase Price 8 9 10 Settlement Charges 11 12 13 14 15 16 Items Payable in Connection with a Loan Loan Origination Fee Appraisal Fee Homeowner's Insurance Credit Report Title Charges Title Service and Title Insurance Deed Preparation 17 18 19 20 21 Government Recording Charges: 22 Endorsements 23 Courier Fees 24 25 Gross Amount Due from Borrower 26 27 Amounts Paid by or on Behalf of Borrower 28 Deposit of Earnest Money 29 Principal Amount of New Loan 30 31 32 33 34 35 36 Gross Amount Due from Borrower Adjustments for Items OverPaid by Seller Property Taxes * Total Paid by the Borrower 37 Less: Total Paid by the Borrower 38 Cash at Settlement from Borrower 39 40 Total Expenses 41 Purchase Price 42 Expenses as % of Price 43 Cash 270,000.00 400.00 1,586.00 25.00 800.00 25.00 150.00 25.00 Description Contract Purchase Price Gross Amount Due to Seller Reductions in Amount Due Seller Brokerage Commission Payoff of Mortgage Loan Title Charges Title Service and Title Insurance Deed Preparation Government Recording Charges: Courier Fees Adjustments for Items OverPaid by Seller Propery Taxes Total Reductions in Amount Due Seller Gross Amount Due to Seller Less Reductions in Amount Due Seller Cash at Settlement to Seller Total Expenses Purchase Price Expenses as % of Price Cash 140,000.00 25.00 25.00 1,264.00 *As the closing date does not often coincide with the due date for property taxes, a portion of the annual property taxes is usually 44 prorated between the buyer and the seller. In this case, the full year of taxes was paid by the seller on January 1st. They owned the 45 home for only half of the year, so 50% of the taxes must be returned to the seller from the buyer. 46