Question

To answer the questions below:

• Include a statement at the end of each section. Describe the meaning of the numbers found.

• For instruction on how to submit the assignment review page 7 of the course outline.

1. [Class 9 - 21 Points] Julie has returned from her overseas posting and taken a total of $30,000 in savings and used it for a down payment on a house. The house costs $285,000, so the remaining $255,000 will be financed through a mortgage (loan) with her bank. She will be making bi-weekly payments over 25 years at 3.25% to pay off the mortgage.

1. [3 Points] Calculate Julie’s bi-weekly mortgage payment.

2. [3 Points] Of her 1st payment to the bank, how much will go to pay interest on the mortgage and

   how much will go towards paying down the loan?

3. [3 Points] What will be the total interest paid over the 25 years?

4. [3 Points] The mortgage will come up for renewal in 5 years. How much will she owe at that time?

5. [3 Points] Over the 1st 5 years, how much interest has she paid and how much has she paid on the mortgage?

6. [3 Points] Julie would like to pay off the mortgage in less than 25 years and has determined that she can afford to pay $700 bi-weekly (this should be larger than the amount you found in part a above). How long will it take Julie to pay off the mortgage. Find your answer algebraically, not simply with Goal Seek.

7. [3 Points] Construct an Excel spreadsheet and amortization table for the above question. Your spreadsheet should be laid out similar to the example in the screenshot below.

Answer 1

Interest rate per bi-week (52 weeks in a year or 26 bi-weeks in a year) = 3.25%/26 = 0.00125

No of payments = 25*26 = 650

Biweekly payment (A) is given by

A/0.00125*(1-1/1.00125^650)=255000

=> A = $573.26

Biweekly mortgage payment is $573.26

Interest till 1st payment = $255000 *0.00125 =$318.75

So, in the 1st payment , interest paid is $318.75 and amount of loan paid = $573.26 -$318.75 = $254.51

Total Interest paid over 25 years =

Total payment made to the bank - Total loan taken

=$573.26*650-$255000

= $117620.97

Mortgage amount owed after 5 years (20*26 = 520 payments remaining)

=573.26/0.00125*(1-1/1.00125^520)

=$219097.59

Over the 1st 5 years, mortgage paid = $255000-$219097.59 = $35902.41

Interest paid over 1st 5 years

= total payment made over 1st 5 years (130 payments) - principal paid over 1st 5 years

= 573.26*26*5 - 35902.41

= $38621.78