Excel Assignment And Quiz Submission Guidelines

ACST101 Excel Assignment
	Number of months, Interest Only (IO) repayments.	60		Enter your student number digit by digit in cells F2:M2	4	4	2	7	9	4	1	8	First/Given Name	Pehwar
	Number of years, n, with Principal & Interest (P&I) repayments	10	sum of first three digits of student number							Family Name	Ali
	Number of months, n*m of P&I repayments	120
	Interest rate p.a (compounding monthly) %	6.00%
	Interest rate per month, i/m, %	0.500%	this is formatted as a percentage. i.e 6.00% per annum divided by 12 is 0.06/12 per month and displays as 0.500% in cell C6
	Loan amount	$5,00,000
	Repayment from month 61 to end of loan period (Amount to be a negative)	-$5,551.03	DO NOT Round any amounts in the Amortisation Schedule.Use format commands to display as currency.
Loan Amortisation Schedule
Mth	Balance beginning of month	Interest	Repayment	Balance end of month
1	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
2	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
3	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
4	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
5	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
6	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
7	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
8	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
9	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
10	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
11	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
12	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
13	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
14	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
15	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
16	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
17	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
18	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
19	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
20	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
21	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
22	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
23	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
24	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
25	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
26	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
27	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
28	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
29	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
30	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
31	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
32	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
33	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
34	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
35	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
36	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
37	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
38	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
39	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
40	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
41	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
42	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
43	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
44	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
45	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
46	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
47	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
48	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
49	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
50	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
51	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
52	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
53	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
54	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
55	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
56	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
57	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
58	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
59	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
60	$5,00,000	$2,500.000	-$2,500.00	$5,00,000
61	$5,00,000	$2,500.000	-$5,551.03	$4,96,949
62	$4,96,949	$2,484.745	-$5,551.03	$4,93,883
63	$4,93,883	$2,469.413	-$5,551.03	$4,90,801
64	$4,90,801	$2,454.005	-$5,551.03	$4,87,704
65	$4,87,704	$2,438.520	-$5,551.03	$4,84,592
66	$4,84,592	$2,422.958	-$5,551.03	$4,81,463
67	$4,81,463	$2,407.317	-$5,551.03	$4,78,320
68	$4,78,320	$2,391.599	-$5,551.03	$4,75,160
69	$4,75,160	$2,375.802	-$5,551.03	$4,71,985
70	$4,71,985	$2,359.926	-$5,551.03	$4,68,794
71	$4,68,794	$2,343.970	-$5,551.03	$4,65,587
72	$4,65,587	$2,327.935	-$5,551.03	$4,62,364
73	$4,62,364	$2,311.819	-$5,551.03	$4,59,125
74	$4,59,125	$2,295.623	-$5,551.03	$4,55,869
75	$4,55,869	$2,279.346	-$5,551.03	$4,52,598
76	$4,52,598	$2,262.988	-$5,551.03	$4,49,310
77	$4,49,310	$2,246.548	-$5,551.03	$4,46,005
78	$4,46,005	$2,230.025	-$5,551.03	$4,42,684
79	$4,42,684	$2,213.420	-$5,551.03	$4,39,346
80	$4,39,346	$2,196.732	-$5,551.03	$4,35,992
81	$4,35,992	$2,179.961	-$5,551.03	$4,32,621
82	$4,32,621	$2,163.106	-$5,551.03	$4,29,233
83	$4,29,233	$2,146.166	-$5,551.03	$4,25,828
84	$4,25,828	$2,129.142	-$5,551.03	$4,22,406
85	$4,22,406	$2,112.032	-$5,551.03	$4,18,967
86	$4,18,967	$2,094.837	-$5,551.03	$4,15,511
87	$4,15,511	$2,077.556	-$5,551.03	$4,12,038
88	$4,12,038	$2,060.189	-$5,551.03	$4,08,547
89	$4,08,547	$2,042.735	-$5,551.03	$4,05,039
90	$4,05,039	$2,025.193	-$5,551.03	$4,01,513
91	$4,01,513	$2,007.564	-$5,551.03	$3,97,969
92	$3,97,969	$1,989.847	-$5,551.03	$3,94,408
93	$3,94,408	$1,972.041	-$5,551.03	$3,90,829
94	$3,90,829	$1,954.146	-$5,551.03	$3,87,232
95	$3,87,232	$1,936.162	-$5,551.03	$3,83,617
96	$3,83,617	$1,918.087	-$5,551.03	$3,79,985
97	$3,79,985	$1,899.923	-$5,551.03	$3,76,333
98	$3,76,333	$1,881.667	-$5,551.03	$3,72,664
99	$3,72,664	$1,863.320	-$5,551.03	$3,68,976
100	$3,68,976	$1,844.882	-$5,551.03	$3,65,270
101	$3,65,270	$1,826.351	-$5,551.03	$3,61,546
102	$3,61,546	$1,807.728	-$5,551.03	$3,57,802
103	$3,57,802	$1,789.011	-$5,551.03	$3,54,040
104	$3,54,040	$1,770.201	-$5,551.03	$3,50,259
105	$3,50,259	$1,751.297	-$5,551.03	$3,46,460
106	$3,46,460	$1,732.299	-$5,551.03	$3,42,641
107	$3,42,641	$1,713.205	-$5,551.03	$3,38,803
108	$3,38,803	$1,694.016	-$5,551.03	$3,34,946
109	$3,34,946	$1,674.731	-$5,551.03	$3,31,070
110	$3,31,070	$1,655.349	-$5,551.03	$3,27,174
111	$3,27,174	$1,635.871	-$5,551.03	$3,23,259
112	$3,23,259	$1,616.295	-$5,551.03	$3,19,324
113	$3,19,324	$1,596.622	-$5,551.03	$3,15,370
114	$3,15,370	$1,576.849	-$5,551.03	$3,11,396
115	$3,11,396	$1,556.979	-$5,551.03	$3,07,402
116	$3,07,402	$1,537.008	-$5,551.03	$3,03,388
117	$3,03,388	$1,516.938	-$5,551.03	$2,99,354
118	$2,99,354	$1,496.768	-$5,551.03	$2,95,299
119	$2,95,299	$1,476.497	-$5,551.03	$2,91,225
120	$2,91,225	$1,456.124	-$5,551.03	$2,87,130
121	$2,87,130	$1,435.649	-$5,551.03	$2,83,015
122	$2,83,015	$1,415.073	-$5,551.03	$2,78,879
123	$2,78,879	$1,394.393	-$5,551.03	$2,74,722
124	$2,74,722	$1,373.610	-$5,551.03	$2,70,545
125	$2,70,545	$1,352.723	-$5,551.03	$2,66,346
126	$2,66,346	$1,331.731	-$5,551.03	$2,62,127
127	$2,62,127	$1,310.635	-$5,551.03	$2,57,887
128	$2,57,887	$1,289.433	-$5,551.03	$2,53,625
129	$2,53,625	$1,268.125	-$5,551.03	$2,49,342
130	$2,49,342	$1,246.710	-$5,551.03	$2,45,038
131	$2,45,038	$1,225.189	-$5,551.03	$2,40,712
132	$2,40,712	$1,203.559	-$5,551.03	$2,36,364
133	$2,36,364	$1,181.822	-$5,551.03	$2,31,995
134	$2,31,995	$1,159.976	-$5,551.03	$2,27,604
135	$2,27,604	$1,138.021	-$5,551.03	$2,23,191
136	$2,23,191	$1,115.956	-$5,551.03	$2,18,756
137	$2,18,756	$1,093.780	-$5,551.03	$2,14,299
138	$2,14,299	$1,071.494	-$5,551.03	$2,09,819
139	$2,09,819	$1,049.097	-$5,551.03	$2,05,317
140	$2,05,317	$1,026.587	-$5,551.03	$2,00,793
141	$2,00,793	$1,003.965	-$5,551.03	$1,96,246
142	$1,96,246	$981.229	-$5,551.03	$1,91,676
143	$1,91,676	$958.380	-$5,551.03	$1,87,083
144	$1,87,083	$935.417	-$5,551.03	$1,82,468
145	$1,82,468	$912.339	-$5,551.03	$1,77,829
146	$1,77,829	$889.146	-$5,551.03	$1,73,167
147	$1,73,167	$865.836	-$5,551.03	$1,68,482
148	$1,68,482	$842.410	-$5,551.03	$1,63,773
149	$1,63,773	$818.867	-$5,551.03	$1,59,041
150	$1,59,041	$795.207	-$5,551.03	$1,54,285
151	$1,54,285	$771.427	-$5,551.03	$1,49,506
152	$1,49,506	$747.529	-$5,551.03	$1,44,702
153	$1,44,702	$723.512	-$5,551.03	$1,39,875
154	$1,39,875	$699.374	-$5,551.03	$1,35,023
155	$1,35,023	$675.116	-$5,551.03	$1,30,147
156	$1,30,147	$650.737	-$5,551.03	$1,25,247
157	$1,25,247	$626.235	-$5,551.03	$1,20,322
158	$1,20,322	$601.611	-$5,551.03	$1,15,373
159	$1,15,373	$576.864	-$5,551.03	$1,10,399
160	$1,10,399	$551.993	-$5,551.03	$1,05,400
161	$1,05,400	$526.998	-$5,551.03	$1,00,376
162	$1,00,376	$501.878	-$5,551.03	$95,326
163	$95,326	$476.632	-$5,551.03	$90,252
164	$90,252	$451.260	-$5,551.03	$85,152
165	$85,152	$425.762	-$5,551.03	$80,027
166	$80,027	$400.135	-$5,551.03	$74,876
167	$74,876	$374.381	-$5,551.03	$69,700
168	$69,700	$348.498	-$5,551.03	$64,497
169	$64,497	$322.485	-$5,551.03	$59,268
170	$59,268	$296.342	-$5,551.03	$54,014
171	$54,014	$270.069	-$5,551.03	$48,733
172	$48,733	$243.664	-$5,551.03	$43,425
173	$43,425	$217.127	-$5,551.03	$38,092
174	$38,092	$190.458	-$5,551.03	$32,731
175	$32,731	$163.655	-$5,551.03	$27,344
176	$27,344	$136.718	-$5,551.03	$21,929
177	$21,929	$109.647	-$5,551.03	$16,488
178	$16,488	$82.440	-$5,551.03	$11,019
179	$11,019	$55.097	-$5,551.03	$5,523
180	$5,523	$27.617	-$5,551.03	-$0
Use of Excel fx functions:
Based on your amortisation schedule, calculate answers to Q1 to Q4  in the  yellow highlighted cells below (using excel fx TVM functions and referencing the Amortisation Schedule cells)
Answer each question using one of the  fx formula(s) from among =PV, =FV, =PMT, =RATE, =NPER, =IPMT, =PPMT, =CUMIPMT, =CUMPRINC. Do NOT round but format as dollars and cents.
Total Loan cost (all repayments)	$8,16,123.01	Answer must be positive (greater than 0)	Useful clue:Total loan cost is just the total of all amounts paid over the whole loan term ignoring time value of money. Don’t use =SUM() as it is not in the list of eligible formulas above. You can use one of the eligle formulas in conjunction with a multiply or divide or plus or minus.
The amount of the loan outstanding at the end of month 70.	$4,68,794.03	Answer must be positive (greater than 0)	Useful clue:You won’t use =SUM(). Use an eligible formula from among those in red above.
Suppose you could afford to repay an extra $1000 each month (in addition to the required repayment) from month 61 onwards. In that case, the loan would be fully repaid sometime in month:	97.00	Answer must be positive (greater than 0). Must be a whole number.	Useful clue: You need to consider the NPER with the higher repayment. However, you should exercise thought as to how to denote the answer…given you must indicate which month allowing for 60 months of interest free. Investigate the =ROUNDUP() function to ensure your answer is a whole number.
The monthly loan repayment if there was no interest-only period and you agreed to make Principal and Interest repayments commencing from first month rather than month 61 (using same total loan term being equal to 60 plus contents of cell  C4).	$4,219.28	Answer must be positive (greater than 0)	Useful clue: You need to modify the repayment based on total loan period of C4 plus 60.	C4 Amortisation Schedule contents are here, n*m:	120
AMORTISATION SCHEDULE		Possible Marks
Calculations
	Cell C3 number of years (using =SUM()) and ref cells	10	10	(-1/2 possible)
	Interest only repayment. Shows as negative.	-2500	-$2,500	(-1/2 possible)
	Repayment (shows as negative)	-$    5,551.03	-$5,551.03	(-1 mark possible)
			Put a Y against each below to indicate correct
Within Schedule	Column B uses formulas with cell refs	Y	(-1/2 possible)
	Column C uses formulas with cell refs	Y	(-1/2 possible)
	Column D uses formulas with cell refs	Y	(-1/2 possible)
	Column E uses formulas with cell refs	Y	(-1/2 possible)
	TOTAL THIS SECTION NOT LESS THAN 0	Sub Total	2
Use of absolute cell references	Put a Y against each below to indicate correct. N otherwise
	Column B where relevant	Y	(-1/2 possible)
	Column C where relevant	Y	(-1/2 possible)
	Column D where relevant	Y	(-1/2 possible)
	Column E where relevant	Y	(-1/2 possible)
	TOTAL THIS SECTION NOT LESS THAN 0	Sub Total	2
Format
	Gridlines, Yes? (Y)	Y	(-1/2 possible)
	Currency format, yes? (Y)	Y	(-1/2 possible)
	TOTAL THIS SECTION NOT LESS THAN 0	Sub Total	1
ADDED QUESTIONS
Q1	Total Loan cost (all repayments)	$8,16,123.01	$          8,16,123.01	(-1/2 possible)
Q2	The amount of the loan outstanding at the end of month 70.	$4,68,794.03	$4,68,794.03	(-1/2 possible)
Q3	Suppose you could afford to repay an extra $1000 each month (in addition to the required repayment) from month 61 onwards. In that case, the loan would be fully repaid sometime in month:	97.00	157	(-1/2 possible)
Q4	The monthly loan repayment if there was no interest-only period and you agreed to make Principal and Interest repayments commencing from first month rather than month 61 (using same total loan term being equal to 60 plus contents of cell  C4).	$4,219.28	$4,219.28	(-1/2 possible)
	TOTAL THIS SECTION NOT LESS THAN 0	Sub Total	2
	OTHER DEDUCTIONS, wrong Student id, wrong filename or lodgement
			Total	out of 7