Please solve it in Excel

Introduction:

There are many methods of financing the purchase of residential property, each having advantages which make it the method of choice under a given set of circumstances. The selection of one method from several for a given set of conditions is the topic of this project. Three methods of financing are described in detail. You are asked to re-evaluate Plan A and Plan B with modified values assigned below, and perform some additional analysis.

1. Use the following modified values to work on your Project 1.
   • Price of the house is $160,000.
   • House will be sold in 10 years for $185,000.
2. The project must be completed in Excel. Answer the questions listed below.
   • Evaluate Plan A and Plan C.
   • Select the best financing method.
   • What is the total amount of interest paid in Plan A and Plan C through the 10-year period, respectively?

CASE STUDY FINANCING A HOUSE Introduction When a person or a couple decide to purchase a house, one of the most important considerations is the financing. There are many methods of financing the purchase of residential property, each having advantages which make it the method of choice under a given set of circumstances. The selection of one method from several for a given set of conditions is the topic of this case study. Three methods of financing are described in detail. Plans A and B are evaluated; you are asked to evaluate plan C and perform some additional analyses. The criterion used here is: Select the financing plan which has the largest amount of money remaining at the end of a 10-year period. Therefore, calculate the future worth of each plan, and select the one with the largest future worth value. \begin{tabular}{cc} Plan & Description \\ \hline A & 30-yearfixedrateof10%peryearinterest,5%downpayment \\ B & 30 -year adjustable-rate mortgage \\ (ARM), 9% first 3 years, 9%% in year \\ & 4,10%%inyears5through10(assumed),5%downpayment15-yearfixedrateof9%%peryearinterest,5%downpayment \\ & \end{tabular} Other information: - Price of house is $150,000. - House will be sold in 10 years for $170,000 (net proceeds after selling expenses). - Taxes and insurance (T\&I) are $300 per month. - Amount available: maximum of $40,000 for down payment, $1600 per month, including T\&I. - New loan expenses: origination fee of 1%, appraisal fee $300, survey fee $200, attomey's fee $200, processing fee $350, escrow fees $150. other costs $300. - Any money not spent on the down payment or monthly payments will earn tax-free interest at y% per month. The amount of the loan is $142,500. The equivalent monthly principal and interest ( P&I) payment is determined at 10%/12 per month for 30(12)= 360 months. A=142,500(A/P,10%/12,360)=$1250.56 When T\&I are added to P\&I, the total monthly payment PMTA is PMTA=1250.56+300=$1550.56 We can now determine the future worth of plan A by summing three future worth amounts: the remaining funds not used for the down payment and up-front fees (F1A) and for monthly payments (F2A), and the increase in the value of the house (F3A). Since nonexpended money carns interest at 1/% per month, in 10 years the first future worth is F1A=(40,00010,425)(F/P,0.25%,120)=$39,907.13 The available money not spent on monthly payments is $49.44=$16001550.56. Its future worth after 10 years is F2A=49.44(F/A,0.25%,120)=$6908.81 Net money available from the sale of the house is the difference between the net selling price and the balance of the loan. The balance of the loan is Loanbalance===142,500(F/P,10%/12,120)1250.56(F/A,10%/12,120)385.753.40256,170.92$129,582.48 Since the net proceeds from the sale of the house are $170,000, F3A=170,000129,582.48=$40,417.52 The total future worth of plan A is FA=F1A+F2A+F3A=39,907.13+6908.81+40,417.52=$87,233.46 Plan B: 30-Year Adjustable Rate Mortgage Adjustable rate mortgages are tied to some index such as U.S. Treasury bonds. For this example, we have assumed that the rate is 9% for the first 3 years, 9%% in year 4 , and 10% in years 5 through 10 . Since this option also requires 5% down, the up-front money required will be the same as for plan A, that is, $10,425. The monthly P\&I amount for the first 3 years is based on 9% per year for 30 years. A=142,500(A/P,9%/12,360)=$1146.58 The total monthly payment for the first 3 years is PMTB=$1146.58+300=$1446.58 At the end of year 3, the interest rate changes to 9%% per year. This new rate applies to the balance of the loan at that time: Loanbalanceatendofyear3=142,500(F/P,0.75%,36)1146.58(F/A,0.75%,36)=$139,297.08 The P\&I monthly payment for year 4 is now A=139,297.08(A/P.9.5%/12,324)=$1195.67 The total payment for year 4 is PMTB=1195.67+300=$1495.67 At the end of year 4 , the interest rate changes again, this time to 10% per year, and it stays at this rate for the remainder of the 10-year period. The loan balance at the end of year 4 is Loanbalanceatendofyear4=139,297.08(F/P,9.5%/12,12)1195.67(F/A.9.5%/12,12)=$138,132.42 The new P\&I amount is A=138,132.42(A/P,10.25%/12,312)=$1269.22 The new total payment for years 5 through 10 is PMTB=1269.22+300=$1569.22 The loan balance at the end of 10 years is Loanbalanceafter10years=138,132.42(F/P,10.25%/12,72)1269.22(F/A,10.25%/12,72)=$129,296.16 The future worth of plan B can now be determined using the same three future worth values. The future worth of the money not spent on a down payment is the same as for plan A. F1B=(40,00010.425)(F/P,0.25%,120)=$39.907.13 The future worth of the money not spent on monthly payments is more complex than in plan A. F2B===(16001446.58)(F/A,0.25%,36)(F/P,0.25%,84)+(16001495.67)(F/A,0.25%,12)(F/P,0.25%,72)+(16001569.22)(F/A,0.25%,72)7118.61+1519.31+2424.83$11,062.75 The amount of money left from the sale of the house is F3B=170,000129,296.16=$40,703.84 The total future worth of plan B is FB=F1B+F2B+F3B=$91,673.72 Case Study Exercises 1. Evaluate plan C and select the best financing method. 2. What is the total amount of interest paid in plan A through the 10-year period? 3. What is the total amount of interest paid in plan B through year 4 ? 4. What is the maximum amount of money available for a down payment under plan A, if $40,000 is the total amount available? 5. By how much does the payment increase in plan A for each 1% increase in interest rate? 6. If you wanted to "buy down" the interest rate from 10% to 9% in plan A, how much extra down payment would you have to make? CASE STUDY FINANCING A HOUSE Introduction When a person or a couple decide to purchase a house, one of the most important considerations is the financing. There are many methods of financing the purchase of residential property, each having advantages which make it the method of choice under a given set of circumstances. The selection of one method from several for a given set of conditions is the topic of this case study. Three methods of financing are described in detail. Plans A and B are evaluated; you are asked to evaluate plan C and perform some additional analyses. The criterion used here is: Select the financing plan which has the largest amount of money remaining at the end of a 10-year period. Therefore, calculate the future worth of each plan, and select the one with the largest future worth value. \begin{tabular}{cc} Plan & Description \\ \hline A & 30-yearfixedrateof10%peryearinterest,5%downpayment \\ B & 30 -year adjustable-rate mortgage \\ (ARM), 9% first 3 years, 9%% in year \\ & 4,10%%inyears5through10(assumed),5%downpayment15-yearfixedrateof9%%peryearinterest,5%downpayment \\ & \end{tabular} Other information: - Price of house is $150,000. - House will be sold in 10 years for $170,000 (net proceeds after selling expenses). - Taxes and insurance (T\&I) are $300 per month. - Amount available: maximum of $40,000 for down payment, $1600 per month, including T\&I. - New loan expenses: origination fee of 1%, appraisal fee $300, survey fee $200, attomey's fee $200, processing fee $350, escrow fees $150. other costs $300. - Any money not spent on the down payment or monthly payments will earn tax-free interest at y% per month. The amount of the loan is $142,500. The equivalent monthly principal and interest ( P&I) payment is determined at 10%/12 per month for 30(12)= 360 months. A=142,500(A/P,10%/12,360)=$1250.56 When T\&I are added to P\&I, the total monthly payment PMTA is PMTA=1250.56+300=$1550.56 We can now determine the future worth of plan A by summing three future worth amounts: the remaining funds not used for the down payment and up-front fees (F1A) and for monthly payments (F2A), and the increase in the value of the house (F3A). Since nonexpended money carns interest at 1/% per month, in 10 years the first future worth is F1A=(40,00010,425)(F/P,0.25%,120)=$39,907.13 The available money not spent on monthly payments is $49.44=$16001550.56. Its future worth after 10 years is F2A=49.44(F/A,0.25%,120)=$6908.81 Net money available from the sale of the house is the difference between the net selling price and the balance of the loan. The balance of the loan is Loanbalance===142,500(F/P,10%/12,120)1250.56(F/A,10%/12,120)385.753.40256,170.92$129,582.48 Since the net proceeds from the sale of the house are $170,000, F3A=170,000129,582.48=$40,417.52 The total future worth of plan A is FA=F1A+F2A+F3A=39,907.13+6908.81+40,417.52=$87,233.46 Plan B: 30-Year Adjustable Rate Mortgage Adjustable rate mortgages are tied to some index such as U.S. Treasury bonds. For this example, we have assumed that the rate is 9% for the first 3 years, 9%% in year 4 , and 10% in years 5 through 10 . Since this option also requires 5% down, the up-front money required will be the same as for plan A, that is, $10,425. The monthly P\&I amount for the first 3 years is based on 9% per year for 30 years. A=142,500(A/P,9%/12,360)=$1146.58 The total monthly payment for the first 3 years is PMTB=$1146.58+300=$1446.58 At the end of year 3, the interest rate changes to 9%% per year. This new rate applies to the balance of the loan at that time: Loanbalanceatendofyear3=142,500(F/P,0.75%,36)1146.58(F/A,0.75%,36)=$139,297.08 The P\&I monthly payment for year 4 is now A=139,297.08(A/P.9.5%/12,324)=$1195.67 The total payment for year 4 is PMTB=1195.67+300=$1495.67 At the end of year 4 , the interest rate changes again, this time to 10% per year, and it stays at this rate for the remainder of the 10-year period. The loan balance at the end of year 4 is Loanbalanceatendofyear4=139,297.08(F/P,9.5%/12,12)1195.67(F/A.9.5%/12,12)=$138,132.42 The new P\&I amount is A=138,132.42(A/P,10.25%/12,312)=$1269.22 The new total payment for years 5 through 10 is PMTB=1269.22+300=$1569.22 The loan balance at the end of 10 years is Loanbalanceafter10years=138,132.42(F/P,10.25%/12,72)1269.22(F/A,10.25%/12,72)=$129,296.16 The future worth of plan B can now be determined using the same three future worth values. The future worth of the money not spent on a down payment is the same as for plan A. F1B=(40,00010.425)(F/P,0.25%,120)=$39.907.13 The future worth of the money not spent on monthly payments is more complex than in plan A. F2B===(16001446.58)(F/A,0.25%,36)(F/P,0.25%,84)+(16001495.67)(F/A,0.25%,12)(F/P,0.25%,72)+(16001569.22)(F/A,0.25%,72)7118.61+1519.31+2424.83$11,062.75 The amount of money left from the sale of the house is F3B=170,000129,296.16=$40,703.84 The total future worth of plan B is FB=F1B+F2B+F3B=$91,673.72 Case Study Exercises 1. Evaluate plan C and select the best financing method. 2. What is the total amount of interest paid in plan A through the 10-year period? 3. What is the total amount of interest paid in plan B through year 4 ? 4. What is the maximum amount of money available for a down payment under plan A, if $40,000 is the total amount available? 5. By how much does the payment increase in plan A for each 1% increase in interest rate? 6. If you wanted to "buy down" the interest rate from 10% to 9% in plan A, how much extra down payment would you have to make