This document outlines the following:

1. Objectives
2. Details of the scenario and the steps to follow
3. Formatting requirements
4. Submission requirements

1.     Objectives

The objectives of this assignment are: this assignment is to:

• Research the various mortgage products offered by financial institutions; and
  • use excel to calculate regular repayments on a loan and prepare an amortization table.

Mortgage repayments are calculated using the present value annuity formula. In Week 4 (Discounted cash flows and valuation), you were introduced to the concept of annuity. Annuity is a series of equally spaced, level cash flows over time. The calculation of annuity can be applied to determine the amount of loan repayment and also in preparing a loan amortisation schedule.

This assignment consists of two parts:

• Part I – Research the mortgage products offered by financial institutions in Australia by visiting their websites and write a report on three mortgage products that are offered including interest rates and fees charged. Please see below for details.
• Part II – Prepare an amortization schedule in Excel using a specified amount of a 25- year loan and one of the mortgage rates you researched for part I. Please see below for details.

2.     Details of the scenario and the steps to follow:

• Use the first six digits of your student number as the actual amount borrowed
  • Assume you will repay the mortgage over 25 years (term of the loan)
  • Assume you make fortnightly payments.
  • Select one of the three mortgage offers you have researched for your report to use for part II of the assignment.
  • In your report, provide details of the mortgage offer you have selected and explain why you have selected that offer (this is based on the real mortgages you have researched on the internet). Max 800 words.
  • Finally, complete the mortgage terms and mortgage summary table, and calculate how much total interest is paid over the life of the mortgage in the Excel Spreadsheet.

Assumptions:

1. You have a full-time job and you are buying your first home (owner-occupier).
2. You are eligible to apply for a mortgage.

You have already saved 20% for the deposit and all other related costs associated with buying the house and applying for the mortgage (e.g. stamp duty, conveyancing & legal fees, pest & building inspections, mortgage registration fees, and loan application fees).

i.e. the amount borrowed excludes the 20% deposit and all the associated costs.

• You make fortnightly repayments on the amount borrowed (the principal), plus you pay interest on that amount (you cannot have interest-only mortgage). You will pay off the mortgage over 25 years.
• After 5 years, the interest rate will increase by 150 basis points (i.e. initial interest rate plus 1.5%) and will remain unchanged for the next five years (Year 6 – 10). It will then increase by a further 60 basis points (i.e. interest rate in Year 6 plus 0.6%) in Year 11 and will remain unchanged for the following five years (Year 11 – 15). A further increase of 120 basis points will occur in the subsequent year. It will then remain unchanged for the rest of the loan term (Year 16 onwards).

E.g. If the initial interest rate is 5.5%, the interest rate in Year 6 will be 7.85% (5.5%+2.35%).

• You will get a big promotion in 20 years. Therefore you plan to repay extra per fortnight from Year 20 onwards. Use the last digit of your student number as the extra hundred(s) of dollars repayment per fortnight (i.e. If your student number ends with zero (0), use $100 as the extra payment).

E.g. Student no. 6000004 – the extra payment per fortnight will be $400

• Please ignore any government subsidies for first homebuyers.

To successfully complete this assignment, please read each section carefully. Please read the entire document before you start. For Part II, all computations are required to be produced in the Excel template provided.

2.1     Requirements of the Report (Part I) [35 marks]

In week 1 & 2 lectures, we learned that there are many different types of financial institutions which provide home loans (mortgages) in Australia. For example, Westpac, ANZ, NAB, CBA, IMB, Macquarie, & Newcastle Permanent.

Research the home mortgage industry in Australia and obtain the latest data (lending rates and fees) from the financial institutions’ websites. Compare products (such as basic home loans, one with offset accounts, variable and/or fixed lending rates…) from three different financial institutions and choose the best one for this assignment. Explain why you chose that financial institution and its product (in terms of interest rate and fees). Please attach a copy or provide the link(s) of the relevant information you found on the website. [10 marks]

a.       Use the first six digits of your student number as the actual amount borrowed, and the

interest rate that you have researched to calculate the followings:

1. Initial fortnightly payment of a 25-year mortgage using the PVA formula. [3 marks]
   1. Fortnightly payment in Year 6 – 10 if the interest rate is increased by 2.35% after 5 years using the PVA formula. [3 marks]
   1. Fortnightly payment from Year 11 – 15 if the interest rate is further increased by 0.8% after 10 years using the PVA formula. [3 marks]
   1. Fortnightly payment from Year 16 – 25 if the interest rate is further increased by 1.6% after 15 years using the PVA formula. [3 marks]
   1. Effective annual interest rate for each period: Year 1-5, Year 6-10, Year 11- 15, and Year 16-25 using the EAR formula. [4 marks]
   1. Average Effective annual interest rate of this 25 years mortgage. [1 mark]

Instructions:

In week 4 we learned that the Present Value of an Annuity (PVA) equation is used for calculating the payment of equal amounts of cash (CF) over several time periods such as loan repayments. The equation of PVA is:

• Calculate the fortnightly payment using the PVA formula.
• To calculate the fortnightly payment in year 6, 11 and 16, you will need to find the outstanding balance of the loan after 5, 10, and 15 years respectively. You may use the Excel spreadsheet for the loan balance for each period.

• Explain how the extra fortnightly repayment (from year 20 onwards) affects the term of the mortgage. [3 marks]
  • Reflective summary (500 words) [5 marks]

Your reflection should detail and explain:

• the steps you have taken,
• the difficulties and problems you encountered in preparing this assignment,
• what lessons did you learn from this assignment; and
• how this assignment relates to the real world.

Get Homework Help Now