ASSIGNMENT

ASSESSMENT DETAILS

NATURE OF THE TASK

This is an individual assessment task. You are required to prepare an Excel spreadsheet using techniques including formatting, referencing and Excel functions taught in this Unit. You will need to submit your work using the submission link on iLearn.

REQUIRED RESOURCES

To complete this assignment, you are required to use Microsoft Office Excel for Windows or Mac. The version must be the latest version of the Microsoft Office suite. Please follow the instructions on this webpage https://students.mq.edu.au/support/technology/software/microsoft to get the full version of MS Office for free.

INSTRUCTIONS

General:

Please note that the worksheets are protected. You can only enter data into the cells that are not locked to you. In the Excel file provided, if you attempt to change the contents of a locked cell, you will receive a message saying that you are trying to change protected content. Leave the file protected or you risk getting a zero mark.

•     Files are required to be saved with an “ .xlsx” extension. Other file types will not be accepted and cannot be uploaded to the submission link.

•     Don’t switch to another computer unless you know it uses the correct version of Excel.

•     You will get a zero mark for the Excel assignment if:

o  The submission is not an accessible Microsoft Excel file.

o  You did not use the current Session/Term’s Excel Assignment Template file provided on iLearn.

o  The teacher cannot mark your submission because you have changed the format/structure/name of the worksheets of the Excel Assignment Template file.

o  The teacher cannot mark your submission because you used an old version of Microsoft Office Excel rather than the latest version available on the webpage mentioned above.

o  The  teacher  cannot  mark  your  submission  because  you  added  extra  protection  (additional password or sensitivity setting) to the Excel file.

o  The passwords that protect the Excel Assignment Template file have been removed or changed.

PART A THE “AMORTISATION SCHEDULE” WORKSHEET

In this part of the assignment, you are required to prepare a loan amortisation schedule for a mortgage loan you borrowed from the bank. The loan is to be repaid by equal month-end repayments starting from the end of the first month. The monthly repayments include both the principal and the interest (P&I) on the loan. The term of the loan is n years.

For example, if n=12, you have 12 years to repay your loan. This means you will make 144 equal month-end repayments including both the principal and the interest (P&I) over the 12 years. The loan outstanding balance keeps reducing until it becomes zero at the end of the 12 years or 144 months.

1.     Download the Excel Assignment Template file from the ‘Assignment’ section found in the ‘Assessments’ tile on iLearn.

2.     Save it with the file name: Student family name_ID number.xlsx (E.g., Wong_43567859.xlsx)

3.     Input your student number digit by digit in cells F3 to M3. You must input your own student ID otherwise a penalty of 15 marks will apply.

4.      Enter your first name and family name into the cells O3 and O4.

5.     In cell C4, show the total of the first four digits in your student ID number using the =SUM() function and referencing cell locations. This is interpreted as the number of years you have, to repay the loan.

6.     In cell C5, calculate the number of months you have, to repay the loan using a reference to cell C4.

7.     In cell C6, the nominal annual interest rate p.a. compounding monthly is computed automatically with the input of your student ID number (expressed as a percentage).

8.     In cell C7, calculate the effective monthly interest rate for the loan using a reference to cell C6.

9.     In cell C8, the amount of the original loan is computed automatically with the input of your student ID number.

10.   In cell C9, use the =PMT() function to calculate the month-end repayment that includes both the principal and the interest amount. You must show your answer as a positive number. You must use cell references among cells C4 to C8.

11.   Format the values in cells C4, C5, C7 and C9 according to the formatting requirements next to the cells. Do not round any values in these cells manually.

12.   Prepare a loan amortisation schedule including each month of the loan term using the following detailed steps (all amounts are positive):

a)    In column A, starting from cell A13, create month labels in terms of integers (e.g., 1, 2, 3, …) by using a new row for each month of the loan.

b)    In column B, show the balance of the loan outstanding at the beginning of each month. In column C, show the interest paid for each month. In column D, show the principal paid for each month. In column E, show the balance of the loan outstanding at the end of each month.

c)     In columns B, C, D, E you must use Excel formulas or functions with cell references. You will be penalised for the whole column if you hardcode any number in any cell.

d)    You need to use relative and / or absolute cell references where necessary. This  means that the contents of cells B14, C13, D13 and E13 can be copied down to the end of the columns. You will lose marks for any unnecessary inconsistencies in your functions.

13.   Use a currency format (with 2 decimal places) in all cells in columns B, C, D, E of your schedule, showing dollars and cents e.g., $12,345.67. Do not modify or round any numbers in the amortisation schedule manually.

PART B THE “ADDED QUESTIONS” WORKSHEET

1.    In the worksheet “Added Questions”, calculate the answers for Q1 to Q8 in cells C3 to C10 respectively. Please note that you cannot see the questions until you have entered your student ID digit by digit in cells F3 to M3 on the “Amortisation Schedule” worksheet.

2.    For each of your answers, you must effectively use at least one of the following Excel TVM functions:

=PV(), =FV(), =PMT(), =RATE(), =NPER(), =IPMT(), =PPMT(), =CUMIPMT(),=CUMPRINC().

3.   Do not round your answers (unless required by the question) but format your answers according to the formatting requirements stated next to the answer cell.

4.   Questions  1 to 4 are based on the   Amortisation Schedule in PART A. You are encouraged to refer to cells C4 to C9 in the “Amortisation Schedule” sheet as the arguments for TVM functions. However, DO NOT refer to any other cells / values on the “Amortisation Schedule” worksheet, otherwise you will be awarded a zero mark for the question.

5.   Questions 5 to 8 are independent scenarios which are not related to the Amortisation Schedule in Part A.

PART C THE “CAPITAL BUDGETING” WORKSHEET

1.    In  the  worksheet  “Capital  Budgeting”,  calculate  the  answers  for  Q9  to  Q17  in  cells  C16  to  C32, respectively. Please note that you cannot see the questions until you have entered your student ID digit by digit in cells F3 to M3 on the “Amortisation Schedule” worksheet.

2.    For Q9 and Q10, you must  effectively use the appropriate Excel functions in the calculation. For Q11, you may list the cumulative cash flows to help you with the calculation, but only the final answer will be marked.

3.    Do not round your answers for Q9 to Q11 (unless required by the question) but format your answers according to the formatting requirements stated next to the answer cell.

4.    For Q12 to Q17, select the correct answer from the drop-down list.

SUBMISSION INSTRUCTIONS

Submit your assignment to the Assignment Submission Link on iLearn prior to the deadline.

If you submit after the due date, your submission will be counted as a late submission and penalties will apply (see “Late Submissions” section below).

Please note that if you resubmit your work after the due date, your submission will be counted as a late submission and penalties will apply.

If you experience any problems with the online submission, you must:

(i)   Take a screenshot of the error message that is displayed (which includes the time and date)

(ii)  Contact the Senior Teacher,  Maxine Chen (j[email protected]),  immediately via your Macquarie University student email, advising the Senior Teacher of the problem.

(iii) You must attach the screenshot AND the file containing your submission.

(iv) The email must be received by the Senior Teacher before the submission deadline. Please note that if your email is received after the submission deadline, your submission will be counted as a late submission and penalties will apply (see “Late Submissions” section below).

Verify that your submission to the assignment link was successful.

You must check that your submission can be opened and viewed on iLearn after submitting it.

If you cannot access or view your submission on iLearn, you have not correctly submitted your work and will need to do so prior to the due date.

If you require assistance with submitting, you may lodge aOneHelpTicket or refer to theIT help page.Please take a screenshot to include with any requests for assistance.