Deb and Rusty have just gotten married and wish to buy a home. They both work in Boston and have a combined income of $90,000. They found a modest starter house which they are buying for $350,000. 1. They plan to use their $40,000 is savings to cover the closing costs the bank will charge them, which are 1% of the amount they borrow from the bank. The rest of the savings will be used as a down payment. Determine the largest amount they can use for a down payment and still pay the closing costs. 2. Using the amount Deb and Rusty have to borrow from part 1, open Excel and create a 20-year amortization schedule, giving monthly payments for the amount they borrowed at a 4.5% annual interest rate. You must use the pmt function in Excel to compute the monthly payment. Title this worksheet Amortization. For your answer to this question write “See Excel Workbook”. 3. Use the amortization schedule to compute the total amount of interest they will pay to the bank over the 20 years. 4. Create a separate worksheet from the amortization schedule. Title this worksheet Analysis. In this worksheet, create a column titled Income starting at $90,000 and increasing at 3% for 20 years. What is their income after 20 years? 5. Next create the following columns in your new Excel worksheet. For your answer to this question write “See Excel Workbook”. • Property Taxes which are currently $3,100 a year and will also increase by 3% a year. • Interest paid to the bank each year (careful here, your amortization schedule is monthly). 6. Suppose Deb and Rusty decided to rent an apartment instead of buying a house. Create a column that keeps track of the amount they will spend in rent every year for the next 20 years. Assume the rent is now $1000 a month and will increase by 3% each year. 7. By renting they are saving a lot of money each year! They pay less for rent than a mortgage, and they don’t pay property taxes. Assume the extra money (include the $40,000 in savings as initial deposit) they have from renting versus buying is all invested at 5% a year, and every year their extra money is added to this account. Create a column titled Extra Money. How much extra money do they have after 20 years? 8. Assume the house increases in value by 3% a year. Create a column that gives the value of the house for each of the 20 years they are paying the mortgage. Which option ends up with more money for Deb and Rusty to retire on assuming they sell the house after 20 years? 5. Next create the following columns in your new Excel worksheet. For your answer to this question write “See Excel Workbook”. • Property Taxes which are currently $3,100 a year and will also increase by 3% a year. • Interest paid to the bank each year (careful here, your amortization schedule is monthly). 6. Suppose Deb and Rusty decided to rent an apartment instead of buying a house. Create a column that keeps track of the amount they will spend in rent every year for the next 20 years. Assume the rent is now $1000 a month and will increase by 3% each year. 7. By renting they are saving a lot of money each year! They pay less for rent than a mortgage, and they don’t pay property taxes. Assume the extra money (include the $40,000 in savings as initial deposit) they have from renting versus buying is all invested at 5% a year, and every year their extra money is added to this account. Create a column titled Extra Money. How much extra money do they have after 20 years? 8. Assume the house increases in value by 3% a year. Create a column that gives the value of the house fo
Deb and Rusty have just gotten married and wish to buy a home. They both work in Boston and have a combined income of $90,000. They found a modest starter house which they are buying for $350,000. 1. They plan to use their $40,000 is savings to cover the closing costs the bank will charge them, which are 1% of the amount they borrow from the bank. The rest of the savings will be used as a down payment. Determine the largest amount they can use for a down payment and still pay the closing costs. 2. Using the amount Deb and Rusty have to borrow from part 1, open Excel and create a 20-year amortization schedule, giving monthly payments for the amount they borrowed at a 4.5% annual interest rate. You must use the pmt function in Excel to compute the monthly payment. Title this worksheet Amortization. For your answer to this question write “See Excel Workbook”. 3. Use the amortization schedule to compute the total amount of interest they will pay to the bank over the 20 years. 4. Create a separate worksheet from the amortization schedule. Title this worksheet Analysis. In this worksheet, create a column titled Income starting at $90,000 and increasing at 3% for 20 years. What is their income after 20 years?
5. Next create the following columns in your new Excel worksheet. For your answer to this question write “See Excel Workbook”. • Property Taxes which are currently $3,100 a year and will also increase by 3% a year. • Interest paid to the bank each year (careful here, your amortization schedule is monthly). 6. Suppose Deb and Rusty decided to rent an apartment instead of buying a house. Create a column that keeps track of the amount they will spend in rent every year for the next 20 years. Assume the rent is now $1000 a month and will increase by 3% each year. 7. By renting they are saving a lot of money each year! They pay less for rent than a mortgage, and they don’t pay property taxes. Assume the extra money (include the $40,000 in savings as initial deposit) they have from renting versus buying is all invested at 5% a year, and every year their extra money is added to this account. Create a column titled Extra Money. How much extra money do they have after 20 years? 8. Assume the house increases in value by 3% a year. Create a column that gives the value of the house for each of the 20 years they are paying the mortgage. Which option ends up with more money for Deb and Rusty to retire on assuming they sell the house after 20 years? 5. Next create the following columns in your new Excel worksheet. For your answer to this question write “See Excel Workbook”. • Property Taxes which are currently $3,100 a year and will also increase by 3% a year. • Interest paid to the bank each year (careful here, your amortization schedule is monthly). 6. Suppose Deb and Rusty decided to rent an apartment instead of buying a house. Create a column that keeps track of the amount they will spend in rent every year for the next 20 years. Assume the rent is now $1000 a month and will increase by 3% each year. 7. By renting they are saving a lot of money each year! They pay less for rent than a mortgage, and they don’t pay property taxes. Assume the extra money (include the $40,000 in savings as initial deposit) they have from renting versus buying is all invested at 5% a year, and every year their extra money is added to this account. Create a column titled Extra Money. How much extra money do they have after 20 years? 8. Assume the house increases in value by 3% a year. Create a column that gives the value of the house for each of the 20 years they are paying the mortgage. Which option ends up with more money for Deb and Rusty to retire on assuming they sell the house after 20 years
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 14 images
