FIN 111 Introductory Principles of Finance Spring 2022 Assessment 2
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
FIN 111 Introductory Principles of Finance
Spring 2022
Assessment 2: Written Report and Excel Spreadsheet
(15% weighting)
This document outlines the following:
1. Objectives
2. Details of questions
3. Formatting requirements
4. Submission requirements
1. Objectives
In week 4 (Discounted cash flows and valuation), you were introduced to the concept of annuity and perpetuities. 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 requires you to write a report by utilising real life information from the internet and aims to calculate the total amount you can borrow for a mortgage. Part II requires you to prepare the Excel mortgage amortisation schedule based on your research obtained in Part I.
2. Details of questions
Prepare an amortization schedule for a 20-year mortgage in an Excel spreadsheet. Your task is to research the home mortgage industry in Australia and obtain the latest data (interest rate and fees) from the financial institutions websites.
Use the first six digits of your student number as the actual amount borrowed for a mortgage which calls for equal fortnightly payments over 20 years. Attach the lending information you have chosen from the internet and explain why that financial institution has been chosen for this assignment in the report (max 800 words). Finally 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 are going to buy 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 fee, and loan application fee). i.e. the amount borrowed excludes the 20% deposit and all the associated costs.
3. 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 20 years.
4. After 4 years, the interest rate will increase by 2.5% and remain unchanged for the next four years (i.e. Year 5, 6, 7 and 8). It will then increase by a further 1.5% and remain unchanged for the rest of the loan term (Year 9 onwards).
5. You will get a big promotion in 12 year’s time. Therefore you plan to repay extra per fortnight from Year 13 onwards. Use the last digit of your student number as the extra hundred(s) of dollars repayment. (If your student number end with zero (0), use $100 as the extra payment).
E.g. Student no. 5151515 – the extra payment per fortnight will be $500
6. Please ignore any government subsidies for first home buyers.
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 learnt that there are many different types of financial institutions which provide home loans (mortgage) 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 rate and fees) from the financial institutions’ websites. Compare products (such as basic home loan, one with offset account, variable and/or fixed lending rate…) 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 lending rate to calculate the followings:
1. Initial fortnightly payment of a 20-year mortgage using the PVA formula. [3 marks]
2. Fortnightly payment in Year 5 - 8 if the interest rate is increased by 2.5% after
4 years using the PVA formula. [3 marks]
3. Fortnightly payment from Year 9 onwards if the interest rate is further increased by 1.5% after 8 years using the PVA formula. [3 marks]
4. Effective annual interest rate(s) for Year 1-4, Year 5-8, and Year 9-20. [6 marks]
5. Average Effective annual interest rate of this mortgage. [2 marks]
b. Explain how the extra fortnightly repayment affects the term of the mortgage. [3 marks]
Instructions:
In week 4 we learnt 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:
PVA n = xl 1 - ]
Calculate the fortnightly payment using the PVA formula.
To calculate the fortnightly payment in year 5 and 9, you will need to find the balance
of the loan after 4 and 8 years respectively.
c. Reflective summary (500 words) [5 marks]
Your reflection should detail and explain:
a. the steps you have taken,
b. the difficulties and problems you encountered in preparing this assignment,
c. how this assignment relates to the real world.
2.2 Requirements of the Excel spreadsheet (Part II) [55 marks]
a. Fill in the following items using the excel functions (where appropriate) in the spreadsheet provided: [20 marks]
Mark
Name of Lender |
|
0.5 |
Start date of mortgage |
|
0.5 |
Total amount borrowed (1st six digits of your student number) |
|
1 |
Loan Period (in years) |
20 |
(given) |
Frequency of payment |
Fortnightly |
(given) |
No. of payment per year |
|
0.5 |
Nominal annual interest rate |
|
1 |
Interest rate in Year 5 – 8 |
|
1 |
Interest rate in Year 9 onwards |
|
1 |
Extra payments per period |
|
1 |
Mortgage Summary |
||
Scheduled initial payment |
|
3 |
Fortnightly Payment in Year 5 - 8 |
|
2 |
Fortnightly Payment in Year 9 onwards |
|
2 |
Total no. of scheduled payments |
|
0.5 |
Total no. of actual payments |
|
1 |
Total interest |
|
1 |
Total cost of loan |
|
1 |
Effective annual interest rate |
|
3 |
Instructions:
Download the “FIN111 Mortgage Amortisation template” from Moodle. To compute the scheduled initial payment, and payments in Year 5 - 8 and Year 9
onwards, use the following Excel function:
PMT = (rate, nper, PV)
Additional rows can be added for computation.
b. Based on the computations above in the spreadsheet, prepare the amortisation schedule with the following headings: [35 marks]
No. |
Payment Date |
Beginning Balance |
Repayment |
Extra Payment |
Interest |
Principal |
Ending Balance |
Instructions:
- Refer to week 4 lecture, complete the table using the Excel functions.
3. Format Requirements [10 marks]
3.1 Report (Part I)
3.1.1 Your written report must be typed in Word document (including computations) using any preferred font style.
3.1.1 The report should be typed in size 12 with 1.5 line spacing. There is no margin requirement.
3.1.2 Word limit is 800 words for the written report (not including reflection, computations, table/graphs, reference list, and appendix).
3.1.3 Use your own words. Reference wherever necessary using the Harvard referencing style. Please be guided by the UOW Style Guides from http://uow.libguides.com/refcite.
3.1.4 Reference list (or bibliography) should be provided at the end of your report.
3.1.5 Ensure that your document is edited with no spelling and grammatical errors.
3.1.6 The use of an appendix is allowed.
3.2 Excel Spreadsheet (Part II)
3.2.1 In your answer, provide up to 2 decimal places.
3.2.2 You must use Excel functions for computations in the spreadsheet. Formulas should be shown in a formula bar whenever an Excel function is used.
4 Submission Requirements
4.1 The assignment is due by 1.30pm (AEST) on Wednesday, 21 September 2022 in Week 9.
4.2 Do not wait until the last minute to submit your work. Issues such as power/network outrages, computer breakdowns, USB failures, and the like will not be considered for academic consideration.
4.3 Late assignments will be penalised by a deduction of 5% for each day late. For details, please refer to the subject outline “Submission, Retention and Collection of Written Assessment” section.
4.4 FIN111 students are required to submit a soft-copy of the completed assignment via
Moodle.
Please use the following format as the file name :
Your Surname name_Student number (eg. Smith_6543210)
For the submission of the soft-copy,
Click on ‘Report and Excel Spreadsheet submission”
Below the grading criteria, click on “ Add Submission”
Check the box
upload a single Word document or Pdf file (.doc or .pdf) and a single Excel file
(in .xls or xlsx) by clicking the file icon.
after uploading the files, press “save changes” .
Submission status will appeared after you have successfully uploaded the files. You can edit submission or remove submission before the due date.
Failure to submit a soft-copy of each part will result in a zero mark for the part not
submitted.
Acceptable range of similarity is no more than 30% on Turnitin.
4.5 This is an individual assessment task and you are not allowed to collaborate with anyone for this assignment.
4.6 Marks will be deducted if you fail to meet the submission requirements listed above.
4.7 Marked assignments with comments will be available within three weeks via Moodle.
2022-09-18