Question

In: Finance

ACST201 Spreadsheet Project Task 3 Today is 1 August 2018. Jimmy is 30 years old today...

ACST201 Spreadsheet Project Task 3

Today is 1 August 2018. Jimmy is 30 years old today and he is considering purchasing 5,000 units of XYZ shares today (XYZ’s current share price is $20). Jimmy will use his own savings to cover 20% of the purchase cost (i.e., $20,000) and he is planning to borrow the remaining 80% of the purchase cost (i.e., $80,000) using a 5-year personal loan (it starts from 1 August 2018) from MQU Bank.

Jimmy now has two loan package to choose between.

• Package 1.

– Jimmy will make 60 monthly repayments at the beginning of each month over the following five years (from 1 August 2018 to 31 July 2023) with the first payment being made today. This loan needs to be fully repaid by the end of 5 years (i.e., when Jimmy is 35 years old.).

– This package has an annual fee of $200. The package fee is paid on 1 August of each year during the following five years period (from 1 August 2018 to 31 July 2023). The first one being paid today.

– The interest rate of this package is j12 = 10% p.a.

• Package 2.

– Jimmy will make 60 monthly repayments at the beginning of each month over the following five years with the first payment being made today. This loan needs to be fully repaid by the end of 5 years (i.e., when Jimmy is 35 years old.).

– Jimmy can have a one year interest-only-period at the beginning of the mortgage. Jimmy’s repayments will be interest-only1 for the first year (i.e., first 12 payments will be interest-only payments), followed by payments of principal plus interest for the following 4 years.

– This package has an annual fee of $400. The package fee is paid on 1 August of each year during the following five year period (from 1 August 2018 to 31 July 2023). The first one being paid today.

– The interest rate of this package is j12 = 12% p.a.

Jimmy also plans to sell all the XYZ shares in 5 years’ time (on 1 August 2023). He predicts that the XYZ share price will grow at a rate of y% p.a. Jimmy assumes that

y = the Australian 10-year Government Bond Yield for 2017 + 10%.

You need to use FactSet to find the Australian 10-year Government Bond Yield for 20172. Jimmy assumes that XYZ shares will pay a dividend on 1 January and 1 July of each year. Jimmy predicts that there are two potential outcomes for the dividend amount.

Outcome 1: the dividend amount is assumed to be $1 on 1 January 2019 and will increase at a rate of 5% per half-year.

Outcome 2: the dividend amount is assumed to be $3 on 1 January 2019 and will increase at a rate of 2% per half-year.

[12 marks]

– Calculate the loan repayment amount (excluding the annual fee) for each

month of package 1 and package 2.

– Use Goal Seek to find the net borrowing cost for package 1 and package 2 by including the annual fee (expressed as a rate p.a. compounded monthly).

– Use a bar or column chart to compare the loan repayment amount of package 1 and package 2 over the five-year loan period.

[8 marks]
– Calculate the share price on 1 August 2023.

2 For example, if the XYZ share price is 30 on 1 August 2018 and y is assumed be 15%, the XYZ share price will be 30 from 1 August 2018 to 31 July 2019 and will be 30 × (1 + 15%) from 1 August 2019 to 31 July 2020.

– Calculate the accumulated dividend value for outcomes 1 and 2. Assume a reinvestment rate of 0.5% per month.

– Calculate the holding period rate for outcome 1 and 2. Please refer to the week 6 lecture for the holding period rate calculation. You can consider that the initial investment cost is $100,000 and the interest payment of this investment is the dividend payments. Express your answer as an annual rate of compound interest.

c. [10 marks] The cash outflow of this investment has been analysed in part a and the cash outflow of this investment has been analysed in part b. It is noticed that there are four potential outcomes for Jimmy: loan package 1 with dividend outcome 1, loan package 1 with dividend outcome 2, loan package 2 with dividend outcome 1 and loan package 2 with dividend outcome 2.

– Find the net cash flow in each month from August 2018 to July August 2023 (inclusive) for these four potential outcomes.

– Calculate the present value of the net cash flow for these four potential outcomes. Assume that we use 5% p.a. to find the present value.

– Use a bar or column chart to compare the present value of net cash flows for the four potential outcomes.

Solutions

Expert Solution

loan repayment amount:

Package 1:

PAYMENT DATE TOTAL PAYMENT
01-08-2018 $1,699.76
01-09-2018 $1,699.76
01-10-2018 $1,699.76
01-11-2018 $1,699.76
01-12-2018 $1,699.76
01-01-2019 $1,699.76
01-02-2019 $1,699.76
01-03-2019 $1,699.76
01-04-2019 $1,699.76
01-05-2019 $1,699.76
01-06-2019 $1,699.76
01-07-2019 $1,699.76
01-08-2019 $1,699.76
01-09-2019 $1,699.76
01-10-2019 $1,699.76
01-11-2019 $1,699.76
01-12-2019 $1,699.76
01-01-2020 $1,699.76
01-02-2020 $1,699.76
01-03-2020 $1,699.76
01-04-2020 $1,699.76
01-05-2020 $1,699.76
01-06-2020 $1,699.76
01-07-2020 $1,699.76
01-08-2020 $1,699.76
01-09-2020 $1,699.76
01-10-2020 $1,699.76
01-11-2020 $1,699.76
01-12-2020 $1,699.76
01-01-2021 $1,699.76
01-02-2021 $1,699.76
01-03-2021 $1,699.76
01-04-2021 $1,699.76
01-05-2021 $1,699.76
01-06-2021 $1,699.76
01-07-2021 $1,699.76
01-08-2021 $1,699.76
01-09-2021 $1,699.76
01-10-2021 $1,699.76
01-11-2021 $1,699.76
01-12-2021 $1,699.76
01-01-2022 $1,699.76
01-02-2022 $1,699.76
01-03-2022 $1,699.76
01-04-2022 $1,699.76
01-05-2022 $1,699.76
01-06-2022 $1,699.76
01-07-2022 $1,699.76
01-08-2022 $1,699.76
01-09-2022 $1,699.76
01-10-2022 $1,699.76
01-11-2022 $1,699.76
01-12-2022 $1,699.76
01-01-2023 $1,699.76
01-02-2023 $1,699.76
01-03-2023 $1,699.76
01-04-2023 $1,699.76
01-05-2023 $1,699.76
01-06-2023 $1,699.76
01-07-2023 $1,685.72

Package 2

PAYMENT DATE TOTAL PAYMENT
01-08-2018 $800.00
01-09-2018 $800.00
01-10-2018 $800.00
01-11-2018 $800.00
01-12-2018 $800.00
01-01-2019 $800.00
01-02-2019 $800.00
01-03-2019 $800.00
01-04-2019 $800.00
01-05-2019 $800.00
01-06-2019 $800.00
01-07-2019 $800.00
01-08-2019 $2,106.71
01-09-2019 $2,106.71
01-10-2019 $2,106.71
01-11-2019 $2,106.71
01-12-2019 $2,106.71
01-01-2020 $2,106.71
01-02-2020 $2,106.71
01-03-2020 $2,106.71
01-04-2020 $2,106.71
01-05-2020 $2,106.71
01-06-2020 $2,106.71
01-07-2020 $2,106.71
01-08-2020 $2,106.71
01-09-2020 $2,106.71
01-10-2020 $2,106.71
01-11-2020 $2,106.71
01-12-2020 $2,106.71
01-01-2021 $2,106.71
01-02-2021 $2,106.71
01-03-2021 $2,106.71
01-04-2021 $2,106.71
01-05-2021 $2,106.71
01-06-2021 $2,106.71
01-07-2021 $2,106.71
01-08-2021 $2,106.71
01-09-2021 $2,106.71
01-10-2021 $2,106.71
01-11-2021 $2,106.71
01-12-2021 $2,106.71
01-01-2022 $2,106.71
01-02-2022 $2,106.71
01-03-2022 $2,106.71
01-04-2022 $2,106.71
01-05-2022 $2,106.71
01-06-2022 $2,106.71
01-07-2022 $2,106.71
01-08-2022 $2,106.71
01-09-2022 $2,106.71
01-10-2022 $2,106.71
01-11-2022 $2,106.71
01-12-2022 $2,106.71
01-01-2023 $2,106.71
01-02-2023 $2,106.71
01-03-2023 $2,106.71
01-04-2023 $2,106.71
01-05-2023 $2,106.71
01-06-2023 $2,106.71
01-07-2023 $2,106.71

Net Borrwing Cost: 10.12 % for Package 1 and 10.14% for Package 2.

share price on Aug '23: 36.2

For bar graph or chart just fill in Packag 1 & 2 repayment numbers.


Related Solutions

Today is 1 August 2018. Jimmy is 30 years old today and he is considering purchasing...
Today is 1 August 2018. Jimmy is 30 years old today and he is considering purchasing 5,000 units of XYZ shares today (XYZ’s current share price is $20). Jimmy will use his own savings to cover 20% of the purchase cost (i.e., $20,000) and he is planning to borrow the remaining 80% of the purchase cost (i.e., $80,000) using a 5-year personal loan (it starts from 1 August 2018) from MQU Bank. Jimmy now has two loan package to choose...
Today is 1 July 2018. Matt is 30 years old today. Matt has a portfolio which...
Today is 1 July 2018. Matt is 30 years old today. Matt has a portfolio which consists of three Treasury bonds (henceforth referred to as bond A, bond B and bond C). There are 200 units of bond A, 300 units of bond B and 500 units of bond C. Bond C is a Treasury bond which matures on 1 January 2021. One unit of bond C has a coupon rate of j2 = 3.35% p.a. and a face value...
Today is 1 July 2018. Matt is 30 years old today. Matt has a portfolio which...
Today is 1 July 2018. Matt is 30 years old today. Matt has a portfolio which consists of three Treasury bonds (henceforth referred to as bond A, bond B and bond C). There are 200 units of bond A, 300 units of bond B and 500 units of bond C. • Bond A is a Treasury bond which matures on 1 January 2027. One unit of bond A has a coupon rate of j2 = 2.95% p.a. and a face...
Today is 1 July 2018. Matt is 30 years old today. Matt has a portfolio which...
Today is 1 July 2018. Matt is 30 years old today. Matt has a portfolio which consists of three Treasury bonds (henceforth referred to as bond A, bond B and bond C). There are 200 units of bond A, 300 units of bond B and 500 units of bond C. Bond A is a Treasury bond which matures on 1 January 2027. One unit of bond A has a coupon rate of j2 = 2.95% p.a. and a face value...
Today is 1 January 2018. Mary is 40 years old today and she is planning to...
Today is 1 January 2018. Mary is 40 years old today and she is planning to set up a university education fund for her 12 year old daughter Emily. The fund should be enough to cover Emily’s university tuition fee costs . a. Assume that Emily will attend university at age 18 to complete a three-year bachelor degree. Mary estimates that current average university tuition fees are $25,000 per person per year and are growing at a rate of 3%...
Today is 1 July 2019. John is 30 years old today. He is planning to purchase...
Today is 1 July 2019. John is 30 years old today. He is planning to purchase an apartment with the price of $800,000 on 1 January 2024. John believes that, at the time of purchasing the house, he should have savings to cover 20% of the house price (i.e., $160,000) on 1 January 2024. John has a portfolio which consists of two Treasury bonds and a bank bill (henceforth referred to as bond A, bond B and bank bill C)....
Jimmy and Jane Have Goals Jimmy Johnson is 25 years old. He and his wife Jane...
Jimmy and Jane Have Goals Jimmy Johnson is 25 years old. He and his wife Jane have two children, Emmitt and Patricia, ages 2 and 4 respectively. Jimmy wants to retire in 40 years and build boats. He would like a nice retirement home with some land on a peaceful lake in the mountains of Georgia. Jimmy believes that to purchase a home and lot in 40 years would cost $300,000 in today’s prices. In forty years Jimmy also believes...
You are 30 years old today and decided to apply for a postgraduate in finance. Your...
You are 30 years old today and decided to apply for a postgraduate in finance. Your current annual salary is RM36,000 and is expected to grow by 4% annually. Graduates in finance earns RM50,000 upon graduation, with salaries growing by 3.5% yearly. Cost of the 2 years of study is RM25,000 per year which should be paid at the end of each study year. So, If your retirement age is 67 and the discount rate is 7% annually, is it...
Sara is 30 years old today and wants to set aside an equal amount at the...
Sara is 30 years old today and wants to set aside an equal amount at the end of each of the next 30 years into a retirement fund earning 10% p.a. so that he can retire at age 60. At retirement , the funds will be transferred to an investment account earning 6% p.a. He expects to live to age 80 and wants to be able to withdraw $375,000 per year from the account on his 61st through 80th birthdays....
You are 30 years old today and are planning for retirement. You would like to work...
You are 30 years old today and are planning for retirement. You would like to work for 30 more years and retire when you are 60. You currently earn $100,000 per year. You expect inflation will average 3% per year over the next 30 years. To simplify this problem, assume your income is paid annually and you receive a salary of $100,000 today. You will make your first retirement withdrawal in 31 years at the age of 61. To simplify...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT