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 XXXXXXXXXXyears 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
co
ect. 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 dolla
paid at t = 3.5 years as $1
XXXXXXXXXX/ XXXXXXXXXX/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).
. [10 marks ] Calculate Ruby’s sale price at the end of years 3, 6 and 9 (you
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
efore 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.
. [10 marks ] For this loan product, each customer can have an option to defe
the repayment by 6 months. Specifically, customers can bo
ow $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 yea
$1,000,000 loan product to its customers. Customers have the half yearly,
quarterly payment and monthly options. Using these options, customers are
equired 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 bo
ow $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 XXXXXXXXXXAssume
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
efore the deadline to ensure a successful submission.
End
2
ACST201 Financial Modelling Spreadsheet Project Task 1 S2 2018