Buying a House In this problem, we will be calculating loun payments for different houses under a variety of loan options. The main difference in purchasing a house is that there are usually points involved. "Points" represent fees charged by the lenger for processing your loan One Point is equal to 1% of the loan balance not the purchase price). So on a $100.000 loan, one point would be equivalent to $1000. Tuo prints would be equal to $2.000. With the prices of homes in California, and the high loans required to purchase them. points can really add up! Your assignment Mr. O.B. Servant is looking to purchase a home. He knows you have been studying accounting and he has asked you for your advice on buying a house. He has looked at many different houses and isn't sure how much the monthly payment will be based on a number of alternatives. The Houses The potential houses that he has looked at are as follows: 1. Two Bedrocim with One Bath $300.000 2. Three Bedroom with One and a half Baths $350,000 1. Three Bedroom with Two Baths $400.000 The Loan Options: Hells spoken to his bank and they have provided three potential options for financing the purchase of any of the houses as follows: Loan Option 10% down, one point, and fixed interest of 7.5% Loon Option 2 15% down two points, and Fixed interest of 70% Loan Option 3 20% down, one point, and fixed interest of 702 The term on all loans is 30 years. The points must be paid up front at the time of closing. In practice points can also be wrapped into the loan rather than paying them up front. but for this exercise, let's assume they are paid up front.) Your Assignment: Prepare an analysis of these alternatives showing all the data, the resulting down payment, the loan foes, and the total duc at closing In addition, show the monthly payments for each house Fora real eye opener, calculate the total amount that you would have paid for the house over the life of the loan. This should allow you to easily see how much the financing truly costs you. See the attached example of house 3) to get you started. This is just an example. You can custom tailor it to fit your needs. Remember to keep flexibility in mind. The user should be able to enter any variables they wish and have the answer section calculate for them automatically. This means that your answer section should only contain cell references Some helpful Hinle The PMT function retums payments as negative values. If you would like the amount shows as positive number, use the ABS (Absolute function) to change the presentation. In addition, since you are multiplying by percentages, you Prepare an analysis of these alternatives showing all the data, the resulting down payment, the loan fees, and the total duc at closing. In addition, show the monthly payments for each house For a real eye opener, calculate the total amount that you would have paid for the house over the life of the loan. This should allow you to casily see how much the financing truly costs you. See the attached example of house 83) to get you stuted. This is just an example. You can custom tailor it to fit your needs Remember to keep flexibility in mind. The user should be able to enter any variables they wish and have the answer section calculate for them automatically. This means that your answer section should only contain cell references. Some helpful in The PMT function retums payments as negative values. If you would like the amount shown as a positive number, use the =ABS (Absolute function) to change the presentation. In addition, since you are multiplying by percentages, you should also consider using the =ROUND function we discussed in the payroll problem. This will cause the computer to truncate numbers and will prevent rounding moed from coming up in your work. The attached unalysis that I prepared uses there features Your file should contain the following: A worksheet of the completed model showing all three loan options for house #1. Show this in a new worksheet you create called "House 1) . A worksheet of the completed model showing all three loan options for buse #2. (Copy the data from the first worksheet into a new worksheet called "House 2" Then change the data inputs as given in the problem.) A worksheet of the completed model showing all three leun options for house #3 Copy the data from the first or second worksheet into a new worksheet called "House 3." Then change the data inputs as given in the problem NOTE: Check your answers against the provided solution to insure that you are on the right truck) . A worksheet of the completed formulas for one of the above utilizing the Chi plus communit. Show the formulas in a new tab called "Formulas Submit your work. After reviewing the file for accuracy and completeness and making sure you have all the worksheets/tatis appropriately labeled, upload your final file called "Loan Payment + Your Last Name and submit it through Canvas Debbie Johnson File Name: Loan FOR DEMONSTRATION PURPOSES ONLY Mr. O.B Servant Loan Analysis of House #3 Three Bedrooms with Two Baths Option 1 Option 2 Option 3 Data Section: Purchase Price Down Payment% Points Charged Interest Rate Term of Loan (in years) $ 400,000 10.00% 1.00% 7.50% 30 $ 400.000 15.0% 2.0% 7.0% 30 $ 400,000 20.0% 1.0% 7.0% 30 Answer Section: $ Down Payment Pionts Paid at closing Total Due at Closing 40,000.00 $ 3,600.00 43,600.00 $ 60,000.00 $ 6,800.00 66.800.00 $ 80,000.00 3,200.00 83,200.00 $2,517.17 $2 262.03 $2,128.97 Monthly Payment Cost of the House Over Life of the Loan Total Paidat Closing $ Loan Payments Total Paid over life of loans 43,600.00 $ $906,181,20 949,781.20 $ 66.800.00 $ $814,330.80 881,130.80 $ 83,200.00 $766,429.20 849,629.20 Original Purchase Price Cost of Finacing 400,000.00 400,000.00 549,781 20 $ 481,130.80 $ 400,000.00 449,629.20 Buying a House In this problem, we will be calculating loun payments for different houses under a variety of loan options. The main difference in purchasing a house is that there are usually points involved. "Points" represent fees charged by the lenger for processing your loan One Point is equal to 1% of the loan balance not the purchase price). So on a $100.000 loan, one point would be equivalent to $1000. Tuo prints would be equal to $2.000. With the prices of homes in California, and the high loans required to purchase them. points can really add up! Your assignment Mr. O.B. Servant is looking to purchase a home. He knows you have been studying accounting and he has asked you for your advice on buying a house. He has looked at many different houses and isn't sure how much the monthly payment will be based on a number of alternatives. The Houses The potential houses that he has looked at are as follows: 1. Two Bedrocim with One Bath $300.000 2. Three Bedroom with One and a half Baths $350,000 1. Three Bedroom with Two Baths $400.000 The Loan Options: Hells spoken to his bank and they have provided three potential options for financing the purchase of any of the houses as follows: Loan Option 10% down, one point, and fixed interest of 7.5% Loon Option 2 15% down two points, and Fixed interest of 70% Loan Option 3 20% down, one point, and fixed interest of 702 The term on all loans is 30 years. The points must be paid up front at the time of closing. In practice points can also be wrapped into the loan rather than paying them up front. but for this exercise, let's assume they are paid up front.) Your Assignment: Prepare an analysis of these alternatives showing all the data, the resulting down payment, the loan foes, and the total duc at closing In addition, show the monthly payments for each house Fora real eye opener, calculate the total amount that you would have paid for the house over the life of the loan. This should allow you to easily see how much the financing truly costs you. See the attached example of house 3) to get you started. This is just an example. You can custom tailor it to fit your needs. Remember to keep flexibility in mind. The user should be able to enter any variables they wish and have the answer section calculate for them automatically. This means that your answer section should only contain cell references Some helpful Hinle The PMT function retums payments as negative values. If you would like the amount shows as positive number, use the ABS (Absolute function) to change the presentation. In addition, since you are multiplying by percentages, you Prepare an analysis of these alternatives showing all the data, the resulting down payment, the loan fees, and the total duc at closing. In addition, show the monthly payments for each house For a real eye opener, calculate the total amount that you would have paid for the house over the life of the loan. This should allow you to casily see how much the financing truly costs you. See the attached example of house 83) to get you stuted. This is just an example. You can custom tailor it to fit your needs Remember to keep flexibility in mind. The user should be able to enter any variables they wish and have the answer section calculate for them automatically. This means that your answer section should only contain cell references. Some helpful in The PMT function retums payments as negative values. If you would like the amount shown as a positive number, use the =ABS (Absolute function) to change the presentation. In addition, since you are multiplying by percentages, you should also consider using the =ROUND function we discussed in the payroll problem. This will cause the computer to truncate numbers and will prevent rounding moed from coming up in your work. The attached unalysis that I prepared uses there features Your file should contain the following: A worksheet of the completed model showing all three loan options for house #1. Show this in a new worksheet you create called "House 1) . A worksheet of the completed model showing all three loan options for buse #2. (Copy the data from the first worksheet into a new worksheet called "House 2" Then change the data inputs as given in the problem.) A worksheet of the completed model showing all three leun options for house #3 Copy the data from the first or second worksheet into a new worksheet called "House 3." Then change the data inputs as given in the problem NOTE: Check your answers against the provided solution to insure that you are on the right truck) . A worksheet of the completed formulas for one of the above utilizing the Chi plus communit. Show the formulas in a new tab called "Formulas Submit your work. After reviewing the file for accuracy and completeness and making sure you have all the worksheets/tatis appropriately labeled, upload your final file called "Loan Payment + Your Last Name and submit it through Canvas Debbie Johnson File Name: Loan FOR DEMONSTRATION PURPOSES ONLY Mr. O.B Servant Loan Analysis of House #3 Three Bedrooms with Two Baths Option 1 Option 2 Option 3 Data Section: Purchase Price Down Payment% Points Charged Interest Rate Term of Loan (in years) $ 400,000 10.00% 1.00% 7.50% 30 $ 400.000 15.0% 2.0% 7.0% 30 $ 400,000 20.0% 1.0% 7.0% 30 Answer Section: $ Down Payment Pionts Paid at closing Total Due at Closing 40,000.00 $ 3,600.00 43,600.00 $ 60,000.00 $ 6,800.00 66.800.00 $ 80,000.00 3,200.00 83,200.00 $2,517.17 $2 262.03 $2,128.97 Monthly Payment Cost of the House Over Life of the Loan Total Paidat Closing $ Loan Payments Total Paid over life of loans 43,600.00 $ $906,181,20 949,781.20 $ 66.800.00 $ $814,330.80 881,130.80 $ 83,200.00 $766,429.20 849,629.20 Original Purchase Price Cost of Finacing 400,000.00 400,000.00 549,781 20 $ 481,130.80 $ 400,000.00 449,629.20