Mark and Beth are looking at four different homes. They created this spreadsheet to estimate escrow calculations more easily. They will pay the property tax and homeowner’s insurance each month with their mortgage payment. The bank will hold these two amounts in escrow until those bills need to be paid, which is every 6 months. Each line represents data for a different home they are looking at. Mark and Beth input values for the mortgage, property tax, and homeowner’s insurance in rows 2–5, columns A, B, and C.

a. Write the spreadsheet formula for cell D2 that will compute the escrow balance after 1 month. If the monthly escrow payments get 1% interest compounded monthly, Mark and Beth can compute the value of the escrow account in 6 months. Look at this as finding the future value of a periodic deposit. Recall the formula from Section 3-8 shown at the left.

b. Write the spreadsheet formula for cell E2 that will compute the escrow balance after 6 months, with the given interest rate and monthly compounding.

c. Fill in the missing entries.

Future Value of a Periodic Deposit

where B = balance at the end of the 6 months
P = periodic deposit amount, which is the monthly escrow
r = annual interest rate expressed as a decimal
n = number of times the interest is compounded annually t = length of the investment in years

Transcribed Image Text:

A Monthly 1 Mortgage 2 1,435 3 1,987 4 2,081 5 1,873 B Annual Property Tax 5,900 8,766 8,944 7,711 C Annual Homeowner's Insurance 1,234 1,567 1,540 1,564 Monthly Escrow Payment C. g. i. E Escrow Balance with Interest after Six Months d. f. h. j.