PART A. THE “AMORTISATION SCHEDULE” SPREADSHEET

  1. Download the Excel Assignment Template file from ‘Assessment Tasks’ section under the ‘Excel Assignment’ label in iLearn.
  2. Save it with the name: [Student family name_ID number].xlsx

(Eg. Wong_43567859.xlsx (a penalty applies for the wrong file name convention)

  1. Input your student number digit by digit in cells F2 to M2. You must input your own student ID otherwise a penalty of 7.5 marks will apply.
  2. Enter your name into the cells shown at cells O2 and O3.
  3. In cell C2, calculate the number of years for the loan as the sum of the first four digits in your student number using the =SUM() function and referencing cell locations. Marks will be deducted if you have + signs inside the brackets.
  4. In cell C3, calculate the number of months for the loan based on the result in cell C2.
  5. In cell C4, the nominal annual interest rate compounding monthly is computed automatically with the input of student number (expressed as a percentage) .
  6. In cell C5, calculate the monthly interest rate for the loan based on the result in cell C4.
  7. In cells C7, the amount of the initial loan is computed automatically with the input of student number.
  8. In cell C8, calculate the constant month-end repayment (as a positive number) using =PMT(), referencing cell locations only from among those in cells C2 to C7.
  9. Prepare a loan amortisation schedule to show the month-by-month reduction in the out-standing loan balance to zero, using the following detailed steps:
    1. Starting at cell A12, create month labels in terms of integers (e.g., 1, 2, 3, …) in column A by using a new row for each month of the loan.
    2. Show the monthly interest paid by month (as a positive amount), in column C referencing cell locations as inputs to your formula.
    3. Show the monthly principal paid by month (as a positive amount), in column D referencing cell locations as inputs to your formula.
    4. Calculate the corresponding balance at the beginning (column B) and at the end of each month (column E) using formulas that reference cell locations.
    5. Note that the dollar amounts in columns C and D are occurring at the end of the month, whereas the balance amounts in columns B and E are defined clearly in the column headings of the schedule.
    6. In Columns B, C, D, E you must use relative and absolute cell references where required. This means that the contents of cell B13, C12, D12 and E12, can be copied down the columns to the end of the loan.
  10. Note that ALL the cells in the amortisation schedule must be calculated with appropriate Excel functions referencing cell locations (that is, do not “hardcode” any number in any of the cells). Marks are deducted for ‘hardcoding’.
  11. Do not round any numbers in the amortisation schedule.
  12. Use a currency format (with 2 decimal places) in all cells in columns B to E of your schedule, showing dollars and cents e.g. $164,035.38.

 

 

PART B. ADDED QUESTIONS SPREADSHEET

  1. Based on the amortisation schedule inputs in Part A, calculate the answers to Q1 to Q5 in cells C3, C5, C7, C9, and C11, respectively, in the work-sheet `Added Questions’ by using, in each of your answers to Q1 to Q5, at least one of the following Excel TVM functions:

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

  1. Some questions require you to combine the TVM function with either other TVM functions, other cell references or with numbers.
  2. You must reference existing cells from the “Amortisation Schedule” or “Added Questions” worksheet for key inputs.

Please contact at info@myassignmentguru.com or whatsapp at +1 450 800 2020 for solution.

Click on Buy Solution and make payment. All prices shown above are in USD. Payment supported in all currencies. Price shown above includes the solution of all questions mentioned on this page. Please note that our prices are fixed (do not bargain).

After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified.

If your question is slightly different from the above question, please contact us at info@myassignmentguru.com with your version of question.