Please solve it in Excel and post the excel file or screen shot ! 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.
- 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.
- 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?
-
5128632ci bla18632_ch04.qxd 09/23/2004 2:38 PM Page 162 162 CHAPTER 4 Nominal and Effective Interest Rates CASE STUDY Any money not spent on the down payment or monthly payments will earn tax-free interest at %% per month FINANCING A HOUSE Introduction When a person or a couple decide to purchase a house, one of the most important considerations is the financ- ing. 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 cir- cumstances. 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. Analysis of Financing Plans Plan A: 30-Year Fixed Rate The amount of money required up front is (a) Down payment (5% of $150,000) (b) Origination fee (1% of $142,500) (C) Appraisal (d) Survey (e) Attorney's fee Processing (8) Escrow (h) Other (recording, credit report, etc.) Total $7,500 1.425 300 200 200 350 150 300 S10,425 Plan The amount of the loan is $142,500. The equiva- lent 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) B Description 30-year fixed rate of 10% per year interest, 5% down payment 30-year adjustable-rate mortgage (ARM), 9% first 3 years, 94% in year 4. 10%% in years 5 through 10 (assumed), 5% down payment 15-year fixed rate of 9%% per year interest, 5% down payment $1250.56 When T&I are added to P&I, the total monthly pay- ment PMT, is PMT 1250.56 + 300 = $1550.56 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&T New loan expenses: origination fee of 1%, ap- praisal fee $300, survey fee $200, attorney's fee $200, processing fee $350, escrow fees $150, other costs $300. 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 (F) and for monthly payments (F), and the in- crease in the value of the house (F). Since non- expended money earns interest at % per month, in 10 years the first future worth is F = (40,000 - 10,425)(F/P.0.25%, 120) $39,907.13 CASE STUDY 163 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 The available money not spent on monthly payments is $49.44 = $1600 - 1550.56. Its future worth after 10 years is F = 49.44F/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 bal- ance of the loan. The balance of the loan is Loan balance = 142,500(F/P.10%/12.120) 1250.56(F/A,10%/12.120) = 385.753.40 - 256,170.92 = $129.582.48 Since the net proceeds from the sale of the house are $170,000 FA = 170,000 - 129,582.48 = $40,417.52 The total future worth of plan A is F = FA+FA+F3A = 39,907.13 + 6908.81 + 40,417.52 = $87.233.46 PMT, = 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 Loan balance at end of year 4 = 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 PMT, = 1269.22 + 300 = $1569.22 0 The loan balance at the end of 10 years is 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, 94% 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 PMT, = $1146.58 + 300 = $1446.58 At the end of year 3, the interest rate changes to 98% per year. This new rate applies to the balance of the loan at that time: Loan balance after 10 years = 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. FB = (40,000 - 10,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. F = (1600 1446.58/F/A.0.25%,36) X (F/P.0.25%,84) + (1600 1495.67) X (F/A.0.25%.12)(F/P,0.25%,72) + (1600 - 1569.22)(F/A.0.25%,72) =7118.61 + 1519.31 +2424.83 = $11,062.75 Loan balance at end of year 3 = 142,500(F/P.0.75%,36) - 1146.58(F/A.0.75%,36) = $139,297.08 164 CHAPTER 4 Nominal and Effective Interest Rates The amount of money left from the sale of the house is F = 170,000 129.296.16 = $40,703.84 The total future worth of plan B is F - F +F8 + F - $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 avail- able 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? 5128632ci bla18632_ch04.qxd 09/23/2004 2:38 PM Page 162 162 CHAPTER 4 Nominal and Effective Interest Rates CASE STUDY Any money not spent on the down payment or monthly payments will earn tax-free interest at %% per month FINANCING A HOUSE Introduction When a person or a couple decide to purchase a house, one of the most important considerations is the financ- ing. 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 cir- cumstances. 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. Analysis of Financing Plans Plan A: 30-Year Fixed Rate The amount of money required up front is (a) Down payment (5% of $150,000) (b) Origination fee (1% of $142,500) (C) Appraisal (d) Survey (e) Attorney's fee Processing (8) Escrow (h) Other (recording, credit report, etc.) Total $7,500 1.425 300 200 200 350 150 300 S10,425 Plan The amount of the loan is $142,500. The equiva- lent 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) B Description 30-year fixed rate of 10% per year interest, 5% down payment 30-year adjustable-rate mortgage (ARM), 9% first 3 years, 94% in year 4. 10%% in years 5 through 10 (assumed), 5% down payment 15-year fixed rate of 9%% per year interest, 5% down payment $1250.56 When T&I are added to P&I, the total monthly pay- ment PMT, is PMT 1250.56 + 300 = $1550.56 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&T New loan expenses: origination fee of 1%, ap- praisal fee $300, survey fee $200, attorney's fee $200, processing fee $350, escrow fees $150, other costs $300. 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 (F) and for monthly payments (F), and the in- crease in the value of the house (F). Since non- expended money earns interest at % per month, in 10 years the first future worth is F = (40,000 - 10,425)(F/P.0.25%, 120) $39,907.13 CASE STUDY 163 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 The available money not spent on monthly payments is $49.44 = $1600 - 1550.56. Its future worth after 10 years is F = 49.44F/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 bal- ance of the loan. The balance of the loan is Loan balance = 142,500(F/P.10%/12.120) 1250.56(F/A,10%/12.120) = 385.753.40 - 256,170.92 = $129.582.48 Since the net proceeds from the sale of the house are $170,000 FA = 170,000 - 129,582.48 = $40,417.52 The total future worth of plan A is F = FA+FA+F3A = 39,907.13 + 6908.81 + 40,417.52 = $87.233.46 PMT, = 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 Loan balance at end of year 4 = 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 PMT, = 1269.22 + 300 = $1569.22 0 The loan balance at the end of 10 years is 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, 94% 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 PMT, = $1146.58 + 300 = $1446.58 At the end of year 3, the interest rate changes to 98% per year. This new rate applies to the balance of the loan at that time: Loan balance after 10 years = 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. FB = (40,000 - 10,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. F = (1600 1446.58/F/A.0.25%,36) X (F/P.0.25%,84) + (1600 1495.67) X (F/A.0.25%.12)(F/P,0.25%,72) + (1600 - 1569.22)(F/A.0.25%,72) =7118.61 + 1519.31 +2424.83 = $11,062.75 Loan balance at end of year 3 = 142,500(F/P.0.75%,36) - 1146.58(F/A.0.75%,36) = $139,297.08 164 CHAPTER 4 Nominal and Effective Interest Rates The amount of money left from the sale of the house is F = 170,000 129.296.16 = $40,703.84 The total future worth of plan B is F - F +F8 + F - $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 avail- able 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
