Financial Mathematics and Business Statistics - Assignment Example

Financial Mathematics and Business Statistics: work Basic financial mathematics and statistical modelling skills, using spreadsheet software (Excel – formulae, financial maths, graphical features, Data Analysis and Solver tools) as well as your awareness of the reality of how financial products work. Your answers are to be presented in an essay/report format, state and explain all assumptions, on which your answers are based; clearly indicate your answer/recommendations; no evidence of use of excel will result in a fail mark for this element of the coursework component of your mark; support any answers with the appropriate calculations to arrive at the answer; include selected screens of formulae underlying computed values. Failure to demonstrate you have created appropriate formulations on excel will be severely penalised. Despite the fact that you will be submitting the Excel file as well, your report is a stand-alone document, meaning a reader should not be required to look at the Excel file to understand your analysis, findings and recommendations; please note that adequate usage of the excel calculations in the report is important. This means that the key data/findings needs to be included in the report and appropriate referencing needs to be done, i.e. the relevant cell/table/range in the relevant tab of the excel file mentioned at the point of the report when it should be consulted. The report will have a maximum of 8 to 10 pages, and NOT MORE THAN THAT Question 3: (25%) The majority of banks, when making decisions on mortgage applications, will look at two indicators: salary and borrowing as a percentage of purchase price. On the first indicator, banks are normally willing to lend 2.5 times one’s salary or 3.25 times joint salary in a joint mortgage application, while currently most banks will lend up to 75% of the property price on their best rate with penalties for higher percentages. The penalty will be 1.5%age points if borrowing is up to 90% of the property value. Adam and Eve are getting married and decided to buy a flat to move into once they do. You have been given the following data: Adam’s current salary is £49,000 p.a. and Eve’s is £42,500 p.a. plus a bonus likely to be around £7,500 (based on previous 3 years experience); Both have jobs where they partly telecommute, so on average each works from home 2 days a week; Their total savings at the moment are £45,000; Adam owns a flat which he would sell, and has been advised that he should be able to sell it for £185,000. The mortgage outstanding on this flat is £124,000; Adam and Eve are planning to apply for a 25 year mortgage; The average price of flats in the area they would like to move into is as follows: studios £220,000; 1-bedroom £330,000; 2-bedroom £440,000; 3-bedroom £575,000; 4-bedroom £750,000 Required: a) By consulting the website of a bank in one of the "big four" UK banking groups (clearly indicate in your report the website link and date consulted): a. Explain the term Offset Mortgage; b. Identify the lowest rate the bank is currently charging for an Offset mortgage; and c. Identify the lowest rate, application fees (if applicable) and respective period the bank is charging for a fixed rate mortgage; b) Calculate the amount you advise Adam and Eve to borrow and which size flat to buy, given their financial and professional situation; c) Using the two rate identified in a) calculate which is the best mortgage that Adam and Eve can take out (assume they take out the amount you recommended in b); d) Whether that advice would change if interest rates went up or down by up to three percentage points. Question 3: (25%) The majority of banks, when making decisions on mortgage applications, will look at two indicators: salary and borrowing as a percentage of purchase price. On the first indicator, banks are normally willing to lend 2.5 times one’s salary or 3.25 times joint salary in a joint mortgage application, while currently most banks will lend up to 75% of the property price on their best rate with penalties for higher percentages. The penalty will be 1.5%age points if borrowing is up to 90% of the property value. Adam and Eve are getting married and decided to buy a flat to move into once they do. You have been given the following data: Adam’s current salary is £49,000 p.a. and Eve’s is £42,500 p.a. plus a bonus likely to be around £7,500 (based on previous 3 years experience); Both have jobs where they partly telecommute, so on average each works from home 2 days a week; Their total savings at the moment are £45,000; Adam owns a flat which he would sell, and has been advised that he should be able to sell it for £185,000. The mortgage outstanding on this flat is £124,000; Adam and Eve are planning to apply for a 25 year mortgage; The average price of flats in the area they would like to move into is as follows: studios £220,000; 1-bedroom £330,000; 2-bedroom £440,000; 3-bedroom £575,000; 4-bedroom £750,000 Required: e) By consulting the website of a bank in one of the "big four" UK banking groups (clearly indicate in your report the website link and date consulted): http://en.wikipedia.org/wiki/Central_bank Internationally, the term "Big Four Banks" has traditionally referred to the following central banks:  The US Federal Reserve  The European Central Bank  The Bank of Japan  The Bank of England a. Explain the term Offset Mortgage; The term offset mortgage means the deduction of savings from the mortgage amount which enables one to offset the interest one pays for the savings they have. So if one has £1 million as mortgage and £20,000 as savings then they can pay interest for only they have to pay for the remaining £80,0000. b. Identify the lowest rate the bank is currently charging for an Offset mortgage; Ans. Bank of England maintains Bank Rate at 0.5% and the size of the Asset Purchase Programme at £375 billion. c. Identify the lowest rate, application fees (if applicable) and respective period the bank is charging for a fixed rate mortgage; Ans. http://www.theguardian.com/money/2014/jan/25/five-year-mortgage-fix-interest-rates-rise Mortgage rates remains very low nowadays, the lowest rate now prevails is 1.5% for a two year and for five year fix it is below 3% The Bank of England has been keeping at 0.5% from March 2009 and they estimate that it would not have risen. f) Calculate the amount you advise Adam and Eve to borrow and which size flat to buy, given their financial and professional situation; Ans. Based on Adam’s salary of £49,000 p.a., he would get £122,500 as loan and Based on Eve’s salary of £42,500 p.a., she would get £106,250. So the total amount is £228,750. But when they jointly apply for mortgage they would get (£49,000+£42,500)*3.25=£297,375 which is £68,625 more than what they get individually together. This loan of £297,375 is 90% of £330,416 ie. Approx. £330,000. So if they purchase around £330,000, they would avoid the penalty of 1.5% For this situation they can prefer minimum 1 bed room house which costs around £330,000. If Adam sells his present flat @£185,000 and subtracting his mortgage amount of £124,000, he would have £61,000 in his hand. The savings of them are £45,000. If we add savings, the total amount would be £106,000. So £106,000+£330,000=£436,000 they can afford for purchasing 2 bed room house. If they apply for 25 year mortgage, the best deal to buy is 2 bedroom flat since after their marriage, they would have children and they also need separate bed rooms. This would ease the situation when the children are growing. On the other hand if they prefer 3 bedroom, £575,000 would become more burden for them to repay. The best deal is to buy 2 bedroom house. g) Using the two rate identified in a) calculate which is the best mortgage that Adam and Eve can take out (assume they take out the amount you recommended in b); Ans. As already given in section (b), the increase in mortgage rate by 3% would be a burden to Adam and Eve, so they can stick upto 2 bed room flat. h) Whether that advice would change if interest rates went up or down by up to three percentage points. Ans. Even with the increase in interest rates, the repayment is affordable for Adam and Eve. Question 5: (20%) My Velo, a manufacturer of bicycles, is planning on building a new factory and it has identified an ideal location for which it has paid £50,000 to acquire the option to buy this land. The board has identified the following cash flows if it chooses to go ahead with the investment in building the factory: Required investment in acquiring the land and building the factory is £15million, which will be spent 80% now and 20% at the end of this year; Acquiring land: 12,000,000 3,000,000 Construction of the factory and all additional infrastructure will take one year. Sales are expected to be £14million in year 2 (first year of sales), and grow 10% in each of the subsequent three years after which they will stabilise; Cost of Sales is expected to start at 50% of sales and reduce by 5 percentage points in each of the subsequent two years, after which they will remain unchanged; Administration and Selling costs will be 10% of sales; The company expects it will need to refurbish the factory every four year at a cost of £2million; My Velo expects it would be able to sell the factory for £6million at the end of 10 years The cost of capital of My Velo is 8.75% Required: a) Discuss and compare the different types of investment appraisal methods My Velo can use, including a discussion of the advantages and disadvantages of each. Ans. Year Sales Cost % of sales Cost of Sales Admn. Cost Total Refurbishing cost Profit 2nd year 14000000 0.5 7,000,000 1,400,000 8,400,000 5,600,000 3rd year 15400000 0.475 6,930,000 1,540,000 8,470,000 6,930,000 4th year 16940000 0.45125 6,776,000 1,694,000 8,470,000 2000000 6,470,000 5th year 16940000 0.45125 7,644,175 1,694,000 9,338,175 7,601,825 6th year 16940000 0.45125 7,644,175 1,694,000 9,338,175 7,601,825 7th year 16940000 0.45125 7,644,175 1,694,000 9,338,175 7,601,825 8th year 16940000 0.45125 7,644,175 1,694,000 9,338,175 2000000 5,601,825 9th year 16940000 0.45125 7,644,175 1,694,000 9,338,175 7,601,825 10th year 16940000 0.45125 7,644,175 1,694,000 9,338,175 7,601,825 Total Profit 62,610,950 Less land& bldg. 15,000,000 Cost of capital @8.75% 1312500 Total Cost 16,312,500 Total Profit-Total Cost= 46,298,450 Approx. 46 million Selling factory 6 million Overall Gain 52 million b) If My Velo had a rule that all investment projects need to payback within 3 years, would this project be accepted or rejected? Comment. Ans. Yes, since the profit is achieved by the third year, the project can be accepted. c) Using NPV and IRR, and assuming My Velo expects to run this factory for 10 years, at which point it will decide whether to sell it or continue operating, make a recommendation on whether My Velo should go ahead with the investment, including on whether it should sell or continue to operate the factory. Solution: Year Purchase Price Net Operating Income Net Sale Proceeds Total Cash Flow Present Value at 8.75% 0 (£12,000,000) (£12,000,000) (£720,000) 1 (£3,000,000) 14,000,000 130,000 114,035 2 15,400,000 136,500 105,032 3 143,325 96,740 4 150,491 825,600 £976,091 £577,924 5 6 7 8 9 10 a. IRR = 82% b. NPV = 15,193.73 Ans. Since the profit is very high, My Velo should go ahead with the investment, including on whether it should sell or continue to operate the factory. Read More