NEED HELP WITH EVERYTHING IN GREEN

B | C | F HOUSE PURCHASE 2 House Price 3 Increase in House value/ Year $200,000 2% APARTMENT RENTAL Apartment Rent/ Month Rent Increase / Year $750 5% --- Credit Score Additional Interest Rate 1000000% 600 3.00% 650 1.50% 700 0.50% 750 0.15% 800 0% | 7 LOAN TERMS Term (years) 9 Down payment 10 Base Interest Rate TO 30 Personal Profile Credit Score Annual Income 720 $74,000 Income Interest Multiplier 20% 5% 50000 100000 12 Value of House @ sale $362,272.32 Part 2: Total Rental Cost @relocati $597,949.63 16 Additional Interest Rate 17 Total Interest Rate 18 Monthly Payment 19 Total Payments 20 Gain@ relocation Interest Multiplier Part 1 & 2 Part 3 0.50% 1.00% 5.500% 6.00% $908.46 $959.28 $327,046.46 $345,341.10 ($4,774.15) ($23,068.79) R&B in Michiana Randy and Brandi (R&B) have moved to the Michiana area recently. They are debating whether they should purchase a house or rent an apartment. They are planning to stay for at least 30 years in the Michiana area. Based on their preferences and initial research, they found the following housing details about the Michiana area. Average house price: Average apartment rental: The average increase in rents/year: The average increase in house Housing loan term: $200000 $750 5% 2% 30 years Current housing market conditions require them to pay at least 20% down payment. The average standard interest rate on housing loans is 5%. This is the minimum interest rate that anyone has to pay. Borrowers also need to pay additional interest rates based on their credit profile. Additional interest rates for a loan depend on the credit score, and it is provided below in a table. Assume that there is no other mortgage loan related costs (i.e., closing costs, points, etc...). Credit score >=800 750-799 700-749 650-699 600-649 Additional Interest rate 0% 0.15% 0.50% 1.5% 3.0% (1) If R&B decide to purchase a house and their combined credit score is 720, what will be their monthly housing payments? However, your worksheet should be able to determine monthly payments for any credit score. (2) What will be the total cost of renting an apartment for over 30 years? What will be the total cost of purchasing a house for over 30 years? Compare and find what should be their decision if they want to stay in Michiana for 30 years. (3) Tightening credit situations in the housing industry may require banks to decide to double the additional interest rates if the annual household income is less than $100000 and triple the additional interest rates if the annual household income is less than $50000. Incorporate this in your results and determine new monthly household payments. R&B's combined household income is $74000. Your worksheet should be able to determine monthly payments for any credit score. Guidelines 1. Enter data as per SolnH8Data.PDF. In cell J2, we are putting arbitrarily large interest rate to convey that the loan will not be approved below the credit score of 600. It is not necessary, but just to convey a complete score spectrum, we can do this. 2. The first task is to determine Value of House @ the time of sale after 30 years in cell B12. Use the Future Value (FV) function to determine this value. Make sure that all parameters that you enter in Future Value function must be in an annual unit rather than a monthly unit of measurement. 3. Use future value (FV) function to determine the Total rental cost @ relocation in cell F12. Remember to convert everything into a yearly format. For example, the rent per month is $750, and it is increasing at 5% a year. To use them for future value, you will multiply $750 by 12, use 5% interest, and 30 years to find out the total rental cost in 30 years. To determine additional interest rate in cell B16, use Vlookup. The lookup value should be the credit score, and the table array should be 12 to J7. The total interest rate should be the sum of the Base Interest rate and additional interest rate. Determine monthly payment by using the PMT function. Remember to use everything monthly here (divide the total interest rate by 12, for example). Total payments should be monthly payments over the term of a loan (30 years). Gain at relocation after 30 years should be the value of house @ sale subtracted by monthly payments and down payments. 5. Now, use nested if or vlookup to multiply an additional interest rate based on a table in 110 to J12 6. Your final solution should look like SolnH8Final.PDF B | C | F HOUSE PURCHASE 2 House Price 3 Increase in House value/ Year $200,000 2% APARTMENT RENTAL Apartment Rent/ Month Rent Increase / Year $750 5% --- Credit Score Additional Interest Rate 1000000% 600 3.00% 650 1.50% 700 0.50% 750 0.15% 800 0% | 7 LOAN TERMS Term (years) 9 Down payment 10 Base Interest Rate TO 30 Personal Profile Credit Score Annual Income 720 $74,000 Income Interest Multiplier 20% 5% 50000 100000 12 Value of House @ sale $362,272.32 Part 2: Total Rental Cost @relocati $597,949.63 16 Additional Interest Rate 17 Total Interest Rate 18 Monthly Payment 19 Total Payments 20 Gain@ relocation Interest Multiplier Part 1 & 2 Part 3 0.50% 1.00% 5.500% 6.00% $908.46 $959.28 $327,046.46 $345,341.10 ($4,774.15) ($23,068.79) R&B in Michiana Randy and Brandi (R&B) have moved to the Michiana area recently. They are debating whether they should purchase a house or rent an apartment. They are planning to stay for at least 30 years in the Michiana area. Based on their preferences and initial research, they found the following housing details about the Michiana area. Average house price: Average apartment rental: The average increase in rents/year: The average increase in house Housing loan term: $200000 $750 5% 2% 30 years Current housing market conditions require them to pay at least 20% down payment. The average standard interest rate on housing loans is 5%. This is the minimum interest rate that anyone has to pay. Borrowers also need to pay additional interest rates based on their credit profile. Additional interest rates for a loan depend on the credit score, and it is provided below in a table. Assume that there is no other mortgage loan related costs (i.e., closing costs, points, etc...). Credit score >=800 750-799 700-749 650-699 600-649 Additional Interest rate 0% 0.15% 0.50% 1.5% 3.0% (1) If R&B decide to purchase a house and their combined credit score is 720, what will be their monthly housing payments? However, your worksheet should be able to determine monthly payments for any credit score. (2) What will be the total cost of renting an apartment for over 30 years? What will be the total cost of purchasing a house for over 30 years? Compare and find what should be their decision if they want to stay in Michiana for 30 years. (3) Tightening credit situations in the housing industry may require banks to decide to double the additional interest rates if the annual household income is less than $100000 and triple the additional interest rates if the annual household income is less than $50000. Incorporate this in your results and determine new monthly household payments. R&B's combined household income is $74000. Your worksheet should be able to determine monthly payments for any credit score. Guidelines 1. Enter data as per SolnH8Data.PDF. In cell J2, we are putting arbitrarily large interest rate to convey that the loan will not be approved below the credit score of 600. It is not necessary, but just to convey a complete score spectrum, we can do this. 2. The first task is to determine Value of House @ the time of sale after 30 years in cell B12. Use the Future Value (FV) function to determine this value. Make sure that all parameters that you enter in Future Value function must be in an annual unit rather than a monthly unit of measurement. 3. Use future value (FV) function to determine the Total rental cost @ relocation in cell F12. Remember to convert everything into a yearly format. For example, the rent per month is $750, and it is increasing at 5% a year. To use them for future value, you will multiply $750 by 12, use 5% interest, and 30 years to find out the total rental cost in 30 years. To determine additional interest rate in cell B16, use Vlookup. The lookup value should be the credit score, and the table array should be 12 to J7. The total interest rate should be the sum of the Base Interest rate and additional interest rate. Determine monthly payment by using the PMT function. Remember to use everything monthly here (divide the total interest rate by 12, for example). Total payments should be monthly payments over the term of a loan (30 years). Gain at relocation after 30 years should be the value of house @ sale subtracted by monthly payments and down payments. 5. Now, use nested if or vlookup to multiply an additional interest rate based on a table in 110 to J12 6. Your final solution should look like SolnH8Final.PDF