代写FIN 111 Introductory Principles of Finance Autumn 2024 Assessment 2: Written Report and Excel Spre
- 首页 >> Matlab编程FIN 111 Introductory Principles of Finance
Autumn 2024
Assessment 2: Written Report and Excel Spreadsheet
(15% weight)
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.
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 25 years.
4. 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.00% (5.5%+1.5%).
5. 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
6. 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]
2. Fortnightly payment in Year 6 - 10 if the interest rate is increased by 1.5% after 5 years using the PVA formula. [3 marks]
3. Fortnightly payment from Year 11 - 15 if the interest rate is further increased by 0.6% after 10 years using the PVA formula. [3 marks]
4. Fortnightly payment from Year 16 – 25 if the interest rate is further increased by 1.2% after 15 years using the PVA formula. [3 marks]
5. 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]
6. 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.
b. Explain how the extra fortnightly repayment (from year 20 onwards) affects the term of the mortgage. [3 marks]
c. 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.
2.2 Requirements of the Excel spreadsheet (Part II) [55 marks]
a. Fill in the following items using Excel functions (where appropriate) in the spreadsheet provided: [17 marks]
Mortgage terms 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) |
25 |
(given) |
|
Frequency of payment |
Fortnightly |
(given) |
|
No. of compounding periods per year |
|
0.5 |
|
Initial annual interest rate |
|
1 |
|
Interest rate in Year 6 – 10 |
|
0.5 |
|
Interest rate in Year 11 - 15 |
|
0.5 |
|
Interest rate in Year 16 onwards |
|
0.5 |
|
Extra payments per period |
|
0.5 |
|
Mortgage Summary |
||
|
Scheduled initial payment |
|
2 |
|
Fortnightly Payment in Year 6- 10 |
|
1.5 |
|
Fortnightly Payment in Year 11- 15 |
|
1.5 |
|
Fortnightly Payment in Year 16 onwards |
|
1.5 |
|
Total no. of scheduled payments |
|
0.5 |
|
Total no. of actual payments |
|
0.5 |
|
Total interest paid |
|
1 |
|
Total cost of loan |
|
1 |
|
Average Effective annual interest rate |
|
2 |
Instructions:
. Download the “FIN111 Mortgage Amortisation template” from Moodle.
. To compute the scheduled initial payment, and payments in Year 6 - 10 and Year 11 –
15. And Year 16 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: [38 marks]
|
No. |
Payment Date |
Beginning Balance |
Repayment |
Extra Payment |
Interest paid |
Principal paid |
Ending Balance |
Instructions:
- Refer to Week 4 lecture, and complete the table using the Excel functions.
3.Formatting Requirements [10 marks]
3.1 Report (Part I)
3.1.1 Your written report must be typed in a 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 fromhttp://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 4.00pm (AEDT) on Friday, 3 May 2024 in Week 9.
4.2 Do not wait until the last minute to submit your work. Issues such as power/network outages, 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) for the report and a single Excel file (in .xls or xlsx) of the spreadsheet by clicking the file icon. N.B. We do not accept share file online.
. after uploading the files, press “save changes”.
. Submission status will appear after you have successfully uploaded the files.
. You can edit or remove your 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.
. The acceptable rate 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.