ACST851 Mathematics of Finance

In answering this assignment, you may consult your notes or any textbooks you like, but you may  not  seek  assistance  from  any  humans  in  any  way whatsoever.  This  includes  seeking assistance  in  interpreting  what  the  questions  mean.  You  should  not  discuss  any  of  the assignment with any of your class mates until after the deadline for submitting it has passed. Spreadsheets The assignment involves construction of several spreadsheets. You may use any spreadsheet package you wish. The computers in the student computer labs have Excel. There are also free open source spreadsheet packages available online, such as LibreOffice. (www.libreoffice.org)  While  LibreOffice  doesn’t  have  all  the  functionality  of  Excel,  it  contains everything needed to complete this assignment. Submission of Assignment The submission deadline is as stated in the Unit Guide. The assignment must be submitted by uploading files using the tool on this unit’s iLearn web site. You  may  edit  your  submission  at  any  time  up  until  the  deadline.  (Hence,  you  can  test  that  you understand the submission process by uploading an irrelevant file and then removing it.) What you should submit •Cover sheet. The individual assessment cover sheet is here: http://www.businessandeconomics.mq.edu.au/faculty_docs/postgraduate/student_support/BC1370_-_approved_v2_SH_-_20150817_Individual_coversheet.pdf Print  the  form  to  paper  and  sign  it.  Then  scan  the document  to  pdf  format.  Alternatively,  if you have a touch sensitive screen, you may be able to write your signature directly to the pdf file. With  respect  to  the  3rd  bullet  point  in  the  “Declaration”  section  of  this cover  sheet,  no collaborative preparatory work is approved for this assignment.  It must be entirely  your own work. •A pdf file containing your spreadsheet output for the calculations requested in parts (b) to (e) below. (If necessary, we will accept 4 separate pdf files, but ideally  you should learn how to get  multiple  pages  of  output  in  a  single  pdf  file. It  helps  if  you  build  the  spreadsheets  as separate sheets in a single file,ratherthan as 4 separate files.) Set your page size to A4. •The spreadsheet file itself. For example, if you use Excel, this would be in xls or xlsx format.   •A file containing the calculations requested in (a)and (e)(i). If you are not already skilled with an  equation  editor  then  mathematical  typing  is  slowwork.  Hence  these  may  be  handwritten and then scanned. If you do type them, you can combine them with the next item. •A pdf file containing the typed answer to the question in (e)(iii) and the survey. Create it in a word processor or text editor and print to pdf format. Some spreadsheets can save directly to pdf format. If your software doesn’t include this feature, you may  download  free  open  source  software  such  as  PDFC reator  which  allows  you  to  print  to  pdf format (http://www.pdfforge.org/pdfcreator)  The  software  adds  itself  to  your  list  of  printers.  Use your software’s normal print method, select PDFCreator from the list of printers, and your software will then “print” to a pdf file rather than to a physical printer. Copyright © 2017 Macquarie University. 2 The Question Housing loans usually have monthly repayments, and  the interest rates quoted for them are nominal annual  rates  compounded  monthly.  However,  to  make  the  loan  repayment  schedules  shorter,  thisassignment  uses  loans  with  quarterly  repyments,  and  the  interest  rates  are  nominal  annual  rates compounded quarterly. Ann  takes  out  a  housing  loan  of  $450,000  from  the  Primary  Bank,  repayable  by  quarterly instalments over 40 years. Interest is charged at 8% p.a. compounded quarterly. The repayments are constant within each year, and increase geometrically in yearly steps at 4.5% p.a. effective. The  repayment  pattern  described  above  is  one  we  examined  in  lectures.  We  stated  that  it  is straightforward  to  determine  the  initial  instalmentby  calculator,  but  more  complex  calculations such as determining loans outstanding are better done by spreadsheet. (a) Determine the size of the quarterly instalment in the first year by calculator. Submit your full working for this part. Round your final answer up to a whole number of cents, so that the loan should fully repay rather than having a few cents still outstanding at theend.  (b)  Use  a  spreadsheet  to  construct  a  loan  repayment schedule  for  the  160  quarters  of  the  loan. Include  a  column  showing  the  instalment  each  quarter.  Also include  a  column  showing  the interest rate applying in each quarter. (In this part the interest rate is constant throughout, but later parts of this assignment will change the interestrate partway through the loan.) Spreadsheets  have  a  facility  to  vary  the  number  of decimal  places  displayed.  Use  this  to display  all  numbers  in  the  schedule  to  cents.  That is,  we  are  only  controlling  the  number  of decimal displayed, but the spreadsheet is still calculating the numbers to many more decimal places.  You should find the loan outstanding at the end of the last quarter is negative. (You can check that  dropping  the  initial  instalment  by  1  cent  leaves  a  positive  loan  outstanding,  but  you  are not required to submit a printout showing that check.) Adjust  the  last  instalment  to  make  the  loan  outstanding  exactly  zero  at  the  end  of  the  last quarter. Submit a printout of the resulting schedule, clearly labelled “Part (b) Solution”. (c)    Spreadsheets  also  include  a  function  for  explicitly  rounding  numbers,  so  that  the  rounded result  is  stored.  In  most  spreadsheets,  this  function  is  called  ROUND,  and  in  the  following explanation  we’ll  use  that  label.  Use  the  on-screenhelp  to  learn  how  to  use  the  ROUNDfunction.  (If  your  spreadsheet  package  gives  different  rounding  options,  use  “round  to earest” rather than up or down.) Youspreadsheet from (b) is unrealistic in that it only truncates the output to cents rather than ounding  the  underlying  numbers.  Now  we’llmakeour spreadsheet  more  realistic  by explicitly rounding using the ROUND function. Use  the  ROUND  function  to  explicitly  round  each  calculated  interest  amount  to  the  nearest cent.  Also   use   the   ROUND   function   to   round   the   instalments.   When  calculating   quarterly instalments for year 2 onwards, we could get slightly different answers depending on whether a particular year’s quarterly instalment is found by: (i) Taking the quarterly instalment for year 1, inflating that at 4.5% p.a. compound for the appropriate number of years, then rounding that result; or  (ii)     Taking the previous year’s quarterly instalment which has already been rounded, inflate that at 4.5% p.a. for 1 year, and round that resultCopyright © 2017 Macquarie University. 3 For  this  exercise,  use  approach  (ii).  To  the  mathematically  inclined,  this  probably  seems  the less  sensible  option,  since  it  increases  the  risk  of  rounding  errorsaccumulating  over  time. However,  it  is  the  approach  that  is  probably  less  confusing  to  the  less  mathematical customers,  since  they  are  only  likely  to  compare  a particular  year’s  quarterly  instalment  to that of the previous year. There  is  no  need  to  round  the  “loan  repaid”  and  “loan  outstanding”  columns,  since  they  are calculated by finding the difference of other numbers which are already rounded to cents, and are hence already rounded appropriately.  Again,  adjust  the  final  instalment  to  whatever  amount  is  required  to  exactly  repay  the  loan. You may find that number is slightly different to that in (b).  Submit a printout of the resulting schedule, clearly labelled “Part (c) Solution”. Part  (c)  better  reflects  reality,  so  when  you  build  your  spreadsheets  for  (d)  and  (e),  use  your part (c) as your starting point, not part (b). At  the  end  of  the  8thinterest rate  to  10%  p.a. compounded quarterly. That is, the new interest rate first applies in the 9thquarter. This is bad news for Ann as she recently changed professions. In doing so she accepted a significant pay cut, but her future salary prospects look promising.  Ann  has  enough  savings  to  cope  with  the  interest  rate  increase  for  the  rest  of  the  3rd  year, so  she decides to let the loan repayment schedule continueon the original instalment pattern for the whole of the 3rd year of the loan, while she considers what to do. Parts (d) and (e) consider two different changes to the repayment pattern that Ann is considering. In both cases, the change will be implemented from the beginning of the 4th year. (d)     Ann thought about setting the instalment for year 4 at whatever level is required to repay the loan in the original planned term, allowing for instalment growing at 4.5% p.a. thereafter, but she found she could not afford the resulting year 4 nstalment. She prefers to pay less than that in  year  4  but  more  later  on,  when  she  expects  to  b  earning  a  better  salary.  So  she  instead decides to set her quarterly instalment to $6,500 for the 4th year, and increase the growth rate of her repayments from 4.5% p.a. to 7% p.a. Construct the repayment schedule on this scenario. The term of the loan will change and there will  be  a  final  smaller  instalment.  (The  schedule  should  show  the  entire  history  of  the  loan from  the  start,  not  just  the  period  since  the  change  in  interest  rate  or  the  period  since  the change in the instalment.) Submit a printout of theresultingschedule, clearly labelled “Part (d) Solution”.  (e) After  seeing  the  result  of  (d),  Ann  is  concerned  at  how  high  the  loan  outstanding  becomes. Ann notices that the Secondary Bank is advertising a particularly low interest rate for the first year of a housing loan, so Ann wants to investigate refinancing her debt. She plans to take out a loan from the Secondary Bank, using it to repay the outstanding loan with the Primary Bank at the end of the 3rd year of the original loan.  The Secondary Bank loan has a special interest rate of 2% p.a. compounded quarterly for the first  year (for new customers like Ann), but the interest rate jumps to 10% p.a. compounded quarterly thereafter. The new loan taken out must be repaid by quarterly instalments over 40 years,  with  instalments  increasing  annually.  To  compare  her  results  to  (d),  Ann  decides  to keep the rate of increase of her quarterly payments at 7% p.a. (i) Determine  the  size  of  the  quarterly  instalment in  the  first  year  of  the  new  loan  by calculator.  Submit  your  full  working  for  this  part.  Round  your  final  answer  up  to  a  whole Copyright © 2017 Macquarie University. 4 number  of  cents,  so  that  the  loan  should  fully  repay  rather  than  having  a  few  cents  still outstanding at the end.  (ii)     Construct Ann’s loan repayment schedule, showing both the first 3 years of the original loan and the full 40 years of the new loan. Submit a printout of the resulting schedule, clearly labelled “Part (e) Solution”.  (iii)   Did  Ann  achieve  her  goal  of  reducing  the  maximum  loan  outstanding  throughout  the loan?  Comment  on  why  she  did  or  didn’t  achieve  her goal.  In  your  answer  refer  to  the variables  that  changed  from  scenario  (d)  to  (e),  and  whether  the  change  acted  to  reduce  or increase this maximum amount.Survey The  university  commits  significant  funds  to  maintaining  computer  labs  on  campus.  Please  help  us collect data on whether this is money well spent. We’ve made this multi-choice so you can answer very quickly, but if you want to provide additional comments on the on-campus computer facilities, feel free. (a) What  computer(s)  did  you  use  to  build  the  spreadsheets  and  pdf  output  in  this  assignment? You  may  list  more  than  one  answer.  “Computer”  includes  desktop  computers  and  portable devices. A. A computer in a university lab or library. B. A computer owned and used solely by me. C. A computer shared with family members or others in my household. D. A computer at my place of employment. E. Other. (Please elaborate.) (b)    You  may  have  printed  the  cover  sheet  on  paper  to  be  able  to  sign  it.  You  may  have  printed other parts of the assignment to paper for easier checking, before printing the final version to pdf. What printers did you use? You may list more than one answer. A. A printer in a university lab or library. B. A printer owned and used solely by me. C. A printer shared with family members or others in my household. D. A printer at my place of employment. E. Didn’t print anything to paper. F. Other. (Please elaborate.) (c)You may have scanned handwritten material or a signed cover sheet to a pdf file. What device did  you  use  to  do  this? You  may  list  more  than  one answer.  “Scanner”  includes  a  combined printer/scanner.A. A scanner in a university lab or library. B. A scanner owned and used solely by me. C. A scanner shared with family members or others in my household. D. A scanner at my place of employment. E. Didn’t scan anything. F. Other. (Please elaborate.)