ACST101 S1 2018 Excel Assignment (7%)

 

GETTING READY

 

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.

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

Review the Kickstart Excel Videos plus the video in Week 4 lecture.

 

SUBMISION DUE DATE

To submit, load your saved file “Studentfamilyname”_”IDnumber”.xlsx Eg. Wong_44967012.xlsx to the Excel Assignment- Submission link under iLearn’s ‘Excel Assignment and Quiz’ page BEFORE

THE DUE DATE and TIME (MON 9th April 9.55pm). Directions for ilearn submission are further below.

 

EXTENSIONS

No time extensions will be granted. (Where a Special Consideration application is made and approved, a supplementary assessment task will be offered…see the Unit Guide for link for online application).

A. THE AMORTISATION SCHEDULE SPREADSHEET WITH AN INTEREST-ONLY PERIOD of 5 YEARS

 

  1. Save it in with the name: “Studentfamilyname”_”IDnumber”.xlsx  Eg. Wong_43567859.xlsxDownload the Excel Assignment file from ‘Excel Assignment & Quiz’ in iLearn
  2. →Place your student number digit by digit in cells F2 to M2
  3. →Enter your name to the cells shown at cells O2 and O3
  4. In cell C2 is the number 60. This represents the period (in months) which the loan requires only repayments equal to the month’s interest charge. i.e. The interest-only period.

An interest-only (IO) period is more common where loans are for residential real estate purchased for investment purposes, rather than for owner-occupation. The bank requires the borrower to repay interest each month for the IO period. After the IO period, the loan reverts to Principal & Interest (P&I) basis (unless IO terms can be renegotiated).

 

  1. →In cell C3 show the total of the first three digits in your student number using the =SUM() function in Excel and referencing cells F2 to H2. This is interpreted as the number of years of further years of the loan, after the 60 months of interest only repayments. This further period is the period in which the loan will have equal monthly P&I repayments that fully amortise the loan to zero.

 

In cell C4, the number of months of the P&I repayments based on 12 times the number of years in cell C3 is shown.

 

IO period (cell C2): 60 months
P&I period (cell C4): =C3*12 = 252
TOTAL months : 312 months

 

  1. In cell C5 the nominal interest rate of 6.00% p.a. compounding monthly is given. It is displayed as a percentage. In cell C6 the monthly rate is automatically computed.
  1. Cell C7, shows the amount of the initial loan, $500,000.
  1. →Cell C8 is the repayment you must calculate being the amount that is paid each month, from month 61 to the end of the loan.
  1. You will be preparing a loan amortisation schedule showing:
  • IO period: the month-by-month repayment schedule for the 60 months interest only (IO) loan period, followed by,
  • P&I period: the month-by-month reduction in the balance of a loan to nil, based on the repayment you calculate in cell C8. Each monthly repayment amount is equal

 

(‘Repayment’ (Column D) should be shown as a negative while ‘Interest’ (Column C) is positive amount).

P&I Repayments are at each month-end, with the formula to be entered to cell C8. Inputs are the loan amount (cell C7), interest rate monthly (cell C6) and number of months cell C4).

 

  1. →Create month labels (1, 2, 3 etc) column A. There must be a row for each month of the

 

An example of what you should have in Excel so far appears below (although it does not show all the months). You should check now that you have this….with your student number and name, not the sample ones.

 

  1. →Complete the amortisation schedule for all months.
  • YOU MUST USE FORMULAS WHICH REFERENCE RELEVANT CELLS, FOR ALL ENTRIES TO COLUMN B, C, D and E WITHIN THE SCHEDULE. i.e. Don’t type any numbers into these columns.
  • (‘Repayment’ (Column D) should be shown as a negative while ‘Interest’ (Column C) is a positive amount).
  • DO NOT ROUND ANSWERS. You should format to display currency (eg $3400.86) where relevant but don’t round.
  • YOU MUST USE ABSOLUTE CELL REFERENCES IN APPROPRIATE CELLS in columns B, C, D and E of schedule TO ENABLE COPYING. (see Kickstart videos for the meaning of ‘absolute cell references’)
  1. →Format the Loan Amortisation Schedule. As a minimum, display: 
  • The relevant dollar currency format to columns (including cents).
  • ALL BORDERS [there is a shortcoming in Excel 2013 when adding borders if a worksheet is protected, as in the case of this assignment. To apply ALL BORDERS, select area you want all borders around; right click, select Format Cells. Then from the Borders tab, create the various inside and outside border lines to make all borders]. Yes, the format mark includes a half mark for achieving these borders. Source:http://www.excelforum.com/excel-general/847418-unable-to-use-all-borders-in-protected-sheet-for-excel-2007-a.html

 

(5 MARK ARE POSSIBLE FOR THIS ELEMENT ‘A’).

 

B. ADDED QUESTIONS SPREADSHEET

 

  1. →Complete the 4 questions on the ‘Added Questions’ spreadsheet using formulas referencing the inputs to your ‘Amortisation Schedule’ spreadsheet cells. The answers MUST USE EXCEL fx functions (only answer using either PV(), FV(), RATE(), NPER(), PMT(), IPMT(), PPMT(),CUMIPMT(), CUMPRINC() functions). These functions were demonstrated in lecture 4.

DO NOT round the answers. Answers must be positive (not negative). Display answers using the dollars and cents format. You MUST reference cells in Amortisation Schedule for each and every key parameter within your formula. Eg if you use =PMT(.005,252,-500000) , this will be marked incorrect as it should reference the relevant cells on the Amortisation Schedule rather than using numbers as inputs to the function)

 

(2 MARKS FOR THIS ELEMENT ‘B’ being 0.5 each question)

 

  1. Save the spreadsheet

 

The third worksheet (‘Markers Use ONLY’) is for markers’ use. It shows where marks can be deducted consistent with the requirements in these instructions (see further below).

 

SUBMISSION

  1. To submit, load your saved file “Studentfamilyname”_”IDnumber”.xlsx Eg.

 

Wong_44967012.xlsx to the Excel Assignment- Submission link under iLearn’s ‘Excel

 

Assignment and Quiz’ page BEFORE THE DUE DATE and TIME (MON 9th April 9.55pm).

 

After making payment, please send your student ID and excel file at info@myassignmentguru.com. We will send you solution within few hours.  

$45.00 – Buy 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.