The spreadsheet calculations should be set up in a systematic manner. Your set-up should contain a list of the given values, and

as many calculated values as possible. Make your spreadsheet as active as possible by using cell references (so that if one value

is changed, subsequent calculations will automatically update). Use absolute cell references in special situations.

Bob and Angelique Mackenzie bought a property valued at $84,000 for $15,000 down with the balance amortized over

20 years. The terms of the mortgage require equal payments at the end of each month. Interest on the mortgage is 3.4%

compounded semi-annually and the mortgage is renewable after five years.

a.

What is the size of the monthly payment?

b.

Prepare an amortization schedule for the first five-year term. Make sure your payments are rounded to the nearest

cent.

c.

What is the cost of financing the debt during the first five-year term?

d.

If the mortgage is renewed for a further five years at 4.2% compounded semi-annually, what will be the size of each

monthly payment?