$1.$ Lisa$$s mortgage is up for renewal. There is currently $15$ years and $6$ months remaining on her $$271,500$ mortgage. Interest on the mortgage is $4\mathrm{.}79\%$ compounded semi$-$annually and the mortgage is renewable after five years.

a$.$ What is the size of each monthly payment?

b$.$ Prepare an amortization schedule for the first five$-$year term. Make sure your payment is rounded to the nearest cent. Express totals at the bottom of each column as currency.

c$.$ What is the cost of the debt for the first five$-$year term?

d$.$ If the mortgage is renewed for a further five years at $6\mathrm{.}35\%$ compounded semi$-$annually, what will be the size of the monthly payment? $2.$ Lisa would like to repay her mortgage more quickly by increasing her monthly payment.

a$.$ If Lisa increases her payment by rounding it to the next $$250,$ how many payments will be required to repay the loan? Use the roundup function to calculate the new payment.

To round up to a specific amount $($like the $$250$ here$)$ one option is to use the ROUNDUP function.

I typically specify the rounding amount and call it the $$bundle$.$ Then use ROUNDUP$($payment amount$/$bundle$,0)*$bundle$.$ The $,0$ means no decimals.

By using this function, you can round your payments to any bundle amount: $$100,$ $$250,$ $$50.$

b$.$ What is the term of the loan in years and months? By how many months is the amortization period shortened?

c$.$ Prepare an amortization schedule for the first five$-$year term. Make sure your payment is rounded to the nearest cent. Express totals at the bottom of each column as currency.

d$.$ What is the total cost of financing the debt for this five$-$year term?

e$.$ How much interest can Lisa save in the first five years if she rounds her payment up to the next $$250$ instead of making the original payment? Rubric Case $12$C

Introduction

Include a title and introduction that explains your case and what you will be analyzing.

Question Set Up including:

$$ Original questions in text boxes or as images

$$ List of initial values from question

$$ Use proper notation$/$description for the values in each cell

$$ Final answers clearly identified and actively calculated

Solving Annuity Questions in Excel:

$$ Calculate initial values using Excel including i$,$ i$2,$ n$,$ downpayments, as required

$$ Calculate required answers using Excel functions or formulas

$$ Link subsequent answers to previously calculated values

Correctness of Mathematics:

$$ Correct answers and process

$$ Values rounded as required. Round currency transactions to $2$ decimal places. Display remaining currency values to the nearest cent. Rates should be unrounded but displayed to $3$ decimal places.

Building Schedules in Excel:

$$ Active cell references, use of absolute cell references as required

$$ Schedules that balance fully in the last payment

Questions:

Put each new question on a new sheet.

Organize your solution so it is easy to follow and highlight the final answers.

Each sheet should have a consistent theme in terms of colour choices, organization, font, etc.