Please do SPT2. Questions are included in PDF attached, Please answer in Excel xls or xlsx file.And also i have attached last assignment questions(SPT1) and answers so you know how it should be handled. thank you
Spreadsheet Project Task 2 Ruby purchased a 10-year 4.2% p.a. Treasury bond with a face value of $100 at a price of 98.545 10 years ago. The bond was redeemable at par. Immediately following the receipt of each coupon, Ruby deposited the coupon into a bank account earning a particular reinvestment rate. Over the 10 years the reinvestment rates Ruby earns are shown in table 1. At the time of purchase, Ruby used her financial modelling skills to model and predict the market rates of return rates for the next 10 years which are shown in table 2 below. Assume her 10-year forecast (in table 2) is actually correct. Hence, these rates represent the appropriate rates to discount Ruby’s future cash flows. Note that these rates are not constant, so Ruby would value a dollar paid at t = 3.5 years as $1 (1+0.045/2)(1+0.044/2) if her valuation date was at t = 2.5 years (25 marks). Table 1: Reinvestment rates Year 1—Year 3 (inclusive) j2 = 4.3% Year 4—Year 7 (inclusive) j2 = 4.6% Year 8—Year 10 (inclusive) j2 = 4.7% Table 2: Market rates Year 1—Year 3 (inclusive) j2 = 4.4% Year 4—Year 7 (inclusive) j2 = 4.5% Year 8—Year 10 (inclusive) j2 = 4.1% a. [8.5 marks ] Calculate the accumulated value of Ruby’s reinvested coupons at the end of years 3, 6 and 9 (your calculation should refer to rates in table 1). b. [10 marks ] Calculate Ruby’s sale price at the end of years 3, 6 and 9 (your calculation should refer to rates in table 2). c. [6.5 marks ] Calculate Ruby’s holding period yield if she sells the bond after 3, 6 or 9 years (Express your answer as a j2 rate). Calculate Ruby’s total realised 1 ACST201 Financial Modelling Spreadsheet Project Task 2 S2 2018 compound yield rate (express your answer as a j2 rate given that she holds the bond to maturity). Use a bar chart to plot your results of three holding period yields and the total realised compound yield rate. The deadline for Spreadsheet Project Task 2 submission is October 2nd 2018, 9:00AM. Present your answers to the above questions in a functional Excel spread- sheet (it should be a XLSX or XLS file), giving each solution on a separate sheet (labelled Part a, Part b, and Part c). Your spreadsheet should be clearly labelled and easy to understand. Make sure you identify what the inputs and outputs are. Please include the necessary information e.g., title, axis titles, etc., in your plot. Please document and describe your working steps. Please note that uploading a file can take up to 10 or 15 minutes. You need to submit your file at least 20 minutes before the deadline to ensure a successful submission. End 2 ACST201 Financial Modelling Spreadsheet Project Task 2 S2 2018 Spreadsheet Project Task 1 Today is 1 July 2018. MQU bank is offering a 25 year $1,000,000 loan product from July 2018 to June 2043 (inclusive) to its customers.(25 marks). a. [9 marks ] This loan product requires customers to make monthly repayment. Payment will be paid at the beginning of each month with an amount of $6,500. Use the Goal Seek to find the implied annual nominal rate of interest payable monthly (i.e., j12) charged by MQU bank. Assume that there is an annual fee of 350 paid on 1 July of each year and the first payment is on today. b. [10 marks ] For this loan product, each customer can have an option to defer the repayment by 6 months. Specifically, customers can borrow $1,000,000 on 1 July 2018 and their payment will be paid at the beginning of each month with an amount of $6,500 from January 2019 to December 2043. Assume that there is an annual fee of 350 paid on 1 July of each year from 2018 to 2043 and the first payment is on today. For this option, MQU will charge an additional fee with amount of $35,000 on 1 January 2019. Use the Goal Seek to find the implied annual nominal rate of interest payable monthly (i.e., j12) charged by MQU bank. c. [6 marks ] Assume that another bank, QMU bank, is also offering a 25 year $1,000,000 loan product to its customers. Customers have the half yearly, quarterly payment and monthly options. Using these options, customers are required to make payment at the start of each period (i.e., at start of each year, half-year and quarter). In this product, customers can borrow $1,000,000 on 1 July 2018 and their payment will be paid at the beginning of each half year, each quarter or each month from January 2019 to December 2043 . Assume that there is no annual fee and the QMU bank charges an annual nominal rate of interest j1 = 6.25% p.a. for these payment options. Find the customer’s half yearly, quarterly and monthly payment amount. Use a bar or column chart to plot the quarterly, half yearly and yearly monthly payment amount. The deadline for Spreadsheet Project Task 1 submission is September 4th 2018, 9:00AM. Present your answers to the above questions in a functional Excel spread- sheet (it should be a XLSX or XLS file), giving each solution on a separate sheet (labelled Part a, Part b, and Part c). Your spreadsheet should be clearly labelled and easy to understand. Make sure you identify what the inputs and outputs are. 1 ACST201 Financial Modelling Spreadsheet Project Task 1 S2 2018 Please include the necessary information e.g., title, axis titles, etc., in your plot. Please document and describe your working steps. Please note that uploading a file can take up to 10 or 15 minutes. You need to submit your file at least 20 minutes before the deadline to ensure a successful submission. End 2 ACST201 Financial Modelling Spreadsheet Project Task 1 S2 2018