Assignment Hippo Logo
LiveChat

Loading..

.

ACST101 Finance 1A Excel Assignment

Use Microsoft Excel for Windows or Mac. The version can be no older than Microsoft Office 2013. If in doubt, use a computer lab at the University. Don’t just switch to use another computer unless you know it uses the correct version. A penalty of 5 marks applies if the sheets are found to be unprotected, which can arise through the use of an incorrect version.

Review the Kickstart Excel video and more importantly ‘Excel – Amortising Loan’ video under Topic 4.

The completed Excel spreadsheet is to be submitted via uploading onto iLearn’s “Excel Assignment – Submission links”. Please follow the submission instructions on page 5.[1] Assignment cover sheet is not required.

Note that you may edit and resubmit unlimited times before the due date and time. However, whatever is there at the deadline is the file that will be marked.

No extensions will be granted. Failure to submit by the due date will imply a mark of zero, except where a Special Considerations application has been made and approved.

The worksheets are protected. You can only enter data into cells that are not locked. In the Excel template, 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. Unprotected files incur a penalty of 5 marks.

Question and Detailed Instructions

Worksheet ‘Amortisation Schedule’

  1. Input your student number digit by digit in cells F2 to M2. [2]
  2. Enter your first name and family name in cells O2 and O3.3
  3. In cell C2, calculate the number of years for the loan as the sum of the first three digits in your student number using the =SUM() function.
  4. In cell C3, calculate the number of months for the loan based on the result in cell C2.
  5. In cell C4, calculate the effective annual interest rate (in terms of integer percentage) as the maximum digit in your student number using the =MAX() function.
  6. In cell C5, calculate the effective monthly interest rate for the loan by making use of =NOMINAL() function.
  7. In cells C6 and C7, the amount of the initial loan is computed automatically with theinput of student number in step 1. above.
  8. In cell C8, calculate the amount of constant month-end repayment using the =PMT() function, based on the values in cells C2 to C7 (not necessarily all of them are required). The answer should be shown as a positive dollar amount.
  9. Prepare a loan amortisation schedule to show the month-by-month reduction in the outstanding loan balance to zero, using the following detailed steps:
    • Create month labels in terms of integers (e.g., 1, 2, 3, …) in column A (starting at cell A11) by using a new row for each month of the loan.
    • Each fixed (i.e., constant throughout the entire loan term) monthly principal repayment in column D should be shown as a negative amount whereas each interest amount in column C should be shown as a positive amount.
  • Calculate the corresponding balance at the beginning (column B) and at the end of each month (column E).
  • Note that the dollar amounts in columns C and D are occurring at the end of the period, whereas the balance amounts in columns B and E are defined clearly in the column headings of the schedule.
  1. Note that ALL the cells in the amortisation schedule must be calculated with appropriate formulae, that is, do not “hardcode” any number in any of the cells.[3]
  2. Do not round any numbers in the amortisation schedule.
  3. Use a currency format for the dollar amounts in columns B to E.

Worksheet ‘Added Questions’

  1. Based on the amortisation schedule, calculate the answers to Q1 to Q5 in cells C3, C5,C7, C9 and C11 respectively by using Excel TVM functions ONLY from among

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

[1] The steps are pretty straighforward to follow. Feel free to clarify if you have questions.

[2] Not G2 to N2 as instructed previously, if you download the instructions 6 hours within assignment release. 3Not P2 and P3 as instructed previously, if you download the instructions 6 hours within assignment release.

[3] For instance, you are not allowed to hardcode the balance at the end of last month as 0.

[4] Hint: As a form of checking the answers obtained here via those TVM functions, you may use other formula calculations (that are not allowed here) by referencing to certain appropriate cells in the loan amortisation schedule

.
Not the Exact Question you were looking for? Post your question for instant answers.
Make Money Online