Tani Masanori 44215398 T6,2018 Excel nsert Page Layout Formulas Data Reviaw View Tell me what you want to do.. NormalBadGoodNeutral Copy . t Format Peirder Format as Check Cell Explanatory Linked Cell Formatting- Table- tyles WACT101 Excel Assignment Enter your student number digit by digit in cells F2:M2 44 2 15 3 98First/Given Name Masanori Number of years, n 16 Number of months, n'm Nominal Interest rate per 192 Family Name Tani annum, compounding 9.0% Effective monthly interest rate, % Loan amount Fixed Monthly Repayment 442,153.00 Amount. (the amount per 4 353.07 The number in cell C8 should be negative Loan Amortisation Schedule MthBlance beginning of Repayment Balance end of month Interest 10 $442.153.00 5442.153.0 5442.153.00 5442,153.00 $442,13 $442.153.00 442,153.00 $442.153.00 $442,153.00 $442,153 12 13 14 15 2.153.00 $442.153.00 $442,153.00 $442,153.00 3,316.15-$3,31615 3,316.15-$3,316.15 $3,316.15 $3,316.15 $3,316.15 3,316.15 5 53,316.15 $3,316.15 3,316.15 $3,316.15 $3,316.15 $3,316.15 3,316.15 53,316.15 442,153.00 5442.153.00 17 $442,153.00 3,316.15-53,316.15 53,316.15 $3,316.15 $3,316.15 -53,316.15 $3,316.15 $3,316 15 $3,316.15 $3,316.15 $3,316.15$3,316.15 10 .00 $442.1 12 13 24 14 5442,15 5442.153.00 $442.153.00 3,316 15 $3.316.15 53.316.15 $442,153 .153.00 $442,153.00 Schedule Added Ovestions 3,316.15-$3,316.15 Marters use Bad w- B-I 5-A- Merge & Center $ . % , u 8/ condtional Format as Check Cell , Table Number $442,153.00 $442,153.00 $442,153.00 $3,316.15 $3,316.15 3,316.15 $3,316.15 3,316.15 $3,316.15 3,316.15-$3,316.15 3,316.15$3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 3,316.15 $3,316.15 $3,316.15$3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 -$3,316.15 $3,316.15 $3,316.15 3,316.15$3,316.15 S3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 $3,316.15 316.15 3,316.15-$3,316.15 $3,316.15 $3,316.15 $3,316.15$3.316.15 $442,153.00 442,153.00 442,153.00 167 $442,153.00 $442,153.00 $442,153.00 442,153.00 $442,153.00 $442,153.00 $442,153.00 $442,153.00 $442,153.00 $442,153.00 S442.153.00 $442.153.00 $442,153.00 442,153.00 442,153.00 $442,153.00 $442,153.00 $442,153.00 $442,153 $442,153.00 $442,153.00 $442,153.00 $442,153.00 $442,153.00 83 173 187 177 188 178 189 179 190 180 191 181 192 182 193 183 $442,153.00 $442,153.00 $442,153.00 $442,153.00 $442,153.00 $442,153.00 $442.153.00 $442,153.00 442,153.00 $442,153.00 $442,153.00 $442,153. 153.00 195 185 196 186 $442.153.00 $442,15 $442,153.00 $442.153.00 $442,153.00 42,153.00 197 187 3,316.15 199 189 200 190 201 191 $442,153.00 $442.153.00 442,153.00 $442.153.00 $442,153.00 Home Insert Page Layout Formulas Date Renew oew /Te me what you want to do. % Cut Colibri 1Wap Tet B 1 u . a. 5-A- 1 Merge & Center , $ . % , General Normal Bad Good Copy Format Painter conditional Form atas Formatting Table Paste Check Cell Explanatory.. Input Alignment Number Styles A1 Use of Excel fx functions: Based on the amortisation schedule requirements, calculate answers to Q1 to Q5 in the yellow highlighted cells below (using in each answer, at least one eligible Excel fx TVM function and referencina the Amortisation Schedule cells) Calculate answers to each of the following using at least one of the fx formulals) from among PV, FV, PMT,-RATE,-NPER-IPMT,-PPMT,-CUMIPMT,Do Noi round 4(unless told to) but format as dollars and cents where relevant Useful clue: Don't use SUMD see the eligible functions Q1 Total amount of principal paid in the 3rd year (between the s beginning of month 25 to the end of month 36) nswer must be postive (greater than O) Q2 The amount of interest paxd in the final 36 months of the loan nswer must be positive igreater then y Useful Clue: Be very careful in specifying the start period in your final 36 months. Double check by looking at the amortisation schedule period 03 The amount of the loan outstanding (loan balance) at the end of eswer must be positive (greater thon ay Useful clue You won't use SUMD Use an eligible formula 9 month 48. from among those in red above 04 New Scenano A it the borrower could afford to repay an extra nd wer must be positive foreoter a You need to consider a combination of NPER and $1,000 at the end of each month. in what month will the final repayment be made? (round up to whole number using ROUNDUPO) ROUNDUP. You may actually use your amotisation schedule to first test the $1,000 effect, then ensure your answer on this sheet using TVM functions) agrees De careful with or negatives appear it was without the New Scenario A) 05 New Scenano B. At the beginning of year 5 (month 49), the wwer munt be positive igreater than Q) You may actually use your amortisation schedule to first effective monthly nerest rates increased by O 3% (The new monthly rate s the vaue in C5+0 3%) Calculate the new monthly repayment (starting from month 49) which alows the borrower to repay the loan on time (The total number of months remains the same and the loan shouid have an endna ensure your TVMM function(s) agrees. (if you effect, then this sheet the effect on Amotisation Schedule to what it was withour she Nes Scenario A 13 ease