Reference no: EM132175704

Excel Assignment Question and Detailed Instructions -

PART A: Amortisation Schedule

This is a loan to be repaid by constant month-end repayments over a period and at a rate specified by instructions below.

1. Input your student number digit by digit in cells F2 to M2.

2. Enter your given name and family name in 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, the number of months for the loan is given based on the result in cell C2.

5. In cell C4, the nominal annual interest rate compounding monthly is given as the maximum digit in your student number (expressed as a percentage).

6. In cell C5, calculate the effective monthly interest rate for the loan referencing cell locations.

7. In cells C7, the amount of the initial loan is computed automatically with the input of student number in step 1 above.

8. In cell C8, calculate the constant month-end repayment, 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:

i. Starting at cell A11, 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.

ii. Show the monthly interest amount by month, in column C referencing cell locations as inputs to your formula.

iii. Each fixed (i.e., constant throughout the entire loan term) monthly 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. Reference cell locations.

iv. Calculate the corresponding balance at the beginning (column B) and at the end of each month (column E) using formulas that reference cell locations.

v. 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.

vi. In Columns B, C, D, E you must use relative and absolute cell references where required. This means that the contents of cell B12, C11, D11 and E11, 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 formulae 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 in all cells in columns B to E of your schedule, showing dollars and cents eg. $120,030.36.

PART B - ADDED QUESTIONS

Based on the amortisation schedule inputs in Part A, calculate the answers to Q1 to Q5 in cells C5, C7, C9, C11 and C13, 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()

Some questions require you to combine the TVM function with either other TVM functions, other cell references or with numbers.

You must reference existing cells from the Amortisation Schedule for key inputs, being nper, rate, pmt, pv.

Question 5 requires you to also investigate the =ROUNDUP() function in Excel.

Attachment:- Assignment Files.rar