Today is 1 August 2018. Jimmy is 30 years old today and he is considering purcha
ID: 2819153 • Letter: T
Question
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%. (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.) The Australian 10-year Government Bond Yield for 2017 is 2.63.
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.
NB: Interest-only repayment means your repayments only cover the interest on the amount you have borrowed, during the interest-only period. For example, if you borrow $1,000 through a fiveyear mortgage on 1 July 2018 with a one year interest-only period at j12 = 6% during the first year (1 July 2018–30 June 2019), your monthly repayment is $1, 000×6%/12 = $5 per month. On 1 July 2019, you need to use the remaining four years to repay the borrowed $1,000. The present value on 1 July 2019 of all payments in the remaining four years should be equal to $1,000.
SHOW EACH STEP AND ALL WORKING ON EXCEL:
– 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.
Explanation / Answer
Solution:
Cost of 'XYZ' Co. Shares ($ 20 * 5,000 units) = $ 100,000
Loan Availed from MQU Bank (80% of Cost) = $ 80,000
In Case of Package - 1 :
1. No. of Monthly repayments = 60 (5 Years starting from 1st Aug. 18)
2. Annual Fees = $ 200
3. Rate of Interest = 10% PA or 0.83% Per Month
4. Assumption: Since, there is no clear mention whether repayment is in equal monthly installments (EMI) or equal principal payment. We assume that its equal principal repayment & interest is calculated on bal. outstanding amount.
Now. monthly pricipal repayment = $ 80,000 / 60 months = $ 1,333.33 per month
Below is the loan amortisation table mentioning monthly by month loan repayment, interest & outstanding balances.
Use Goal Seek to find Rate of Return by Setting Interest Total Value equal to Total int. + Package Fees, i.e set $19,666.67 for $20,667.67 & changing values for Annual Int. rate, this will revise Annual int. rate to 10.51% PA & monthly int. will be 0.88% monthly compunded.
Effective Rate (Using Goal Seek) 10.51% PA or 0.88% Per Month
Now, in case of Package -2
1. No. of Monthly repayments = 60 (12 months Interest only payments & 48 months principal repayment, total 5 Years starting from 1st Aug. 18)
2. Annual Fees = $ 400
3. Rate of Interest = 12% PA or 1.00% Per Month
4. Assumption: Since, there is no clear mention whether repayment is in equal monthly installments (EMI) or equal principal payment. We assume that its equal principal repayment & interest is calculated on bal. outstanding amount.
Now. monthly pricipal repayment = $ 80,000 / 48* months = $ 1,666.67 per month
*(Since, 1st year only int. is payable, hence, net period available for repayment is 5-1= 4 years or 48 months)
Below is the loan amortisation table mentioning monthly by month loan repayment, interest & outstanding balances.
Use Goal Seek to find Rate of Return by Setting Interest Total Value equal to Total int. + Package Fees, i.e set $28,400.00 for $30,400.00 & changing values for Annual Int. rate, this will revise Annual int. rate to 12.85% PA & monthly int. will be 1.07% monthly compunded.
Effective Rate (Using Goal Seek) 12.85% PA or 1.07% Per Month.
Now Bar charts can be easily prepared in excel using data tables created by us, by selecting relevant columns from both tables & pasting in seperate sheet as paste special. Now insert Comparative Column graph in seperate sheet & it will look like below:
Note: Since, its not possible to share the excel file itself, hence excel tables is pasted & chart is enclosed for reference. Hope this would helpful to you.
1 2 3 4 5=(3-4)*0.83% 6=(4+5) 7 8=(4+5+6) 9=(3-4) Period Month/Date Op. Out. Bal. Repayment - Principal Part Int. Loan Repayment Excl. Package Fees Package Fees ($200 PA) Total Repayment incl. Package Fees Clo. Outstainding Bal. 0 01 August 2018 $80,000.00 $1,333.33 $655.56 $1,988.89 $200.00 $2,188.89 $78,666.67 1 01 September 2018 $78,666.67 $1,333.33 $644.44 $1,977.78 $0.00 $1,977.78 $77,333.33 2 01 October 2018 $77,333.33 $1,333.33 $633.33 $1,966.67 $0.00 $1,966.67 $76,000.00 3 01 November 2018 $76,000.00 $1,333.33 $622.22 $1,955.56 $0.00 $1,955.56 $74,666.67 4 01 December 2018 $74,666.67 $1,333.33 $611.11 $1,944.44 $0.00 $1,944.44 $73,333.33 5 01 January 2019 $73,333.33 $1,333.33 $600.00 $1,933.33 $0.00 $1,933.33 $72,000.00 6 01 February 2019 $72,000.00 $1,333.33 $588.89 $1,922.22 $0.00 $1,922.22 $70,666.67 7 01 March 2019 $70,666.67 $1,333.33 $577.78 $1,911.11 $0.00 $1,911.11 $69,333.33 8 01 April 2019 $69,333.33 $1,333.33 $566.67 $1,900.00 $0.00 $1,900.00 $68,000.00 9 01 May 2019 $68,000.00 $1,333.33 $555.56 $1,888.89 $0.00 $1,888.89 $66,666.67 10 01 June 2019 $66,666.67 $1,333.33 $544.44 $1,877.78 $0.00 $1,877.78 $65,333.33 11 01 July 2019 $65,333.33 $1,333.33 $533.33 $1,866.67 $0.00 $1,866.67 $64,000.00 12 01 August 2019 $64,000.00 $1,333.33 $522.22 $1,855.56 $200.00 $2,055.56 $62,666.67 13 01 September 2019 $62,666.67 $1,333.33 $511.11 $1,844.44 $0.00 $1,844.44 $61,333.33 14 01 October 2019 $61,333.33 $1,333.33 $500.00 $1,833.33 $0.00 $1,833.33 $60,000.00 15 01 November 2019 $60,000.00 $1,333.33 $488.89 $1,822.22 $0.00 $1,822.22 $58,666.67 16 01 December 2019 $58,666.67 $1,333.33 $477.78 $1,811.11 $0.00 $1,811.11 $57,333.33 17 01 January 2020 $57,333.33 $1,333.33 $466.67 $1,800.00 $0.00 $1,800.00 $56,000.00 18 01 February 2020 $56,000.00 $1,333.33 $455.56 $1,788.89 $0.00 $1,788.89 $54,666.67 19 01 March 2020 $54,666.67 $1,333.33 $444.44 $1,777.78 $0.00 $1,777.78 $53,333.33 20 01 April 2020 $53,333.33 $1,333.33 $433.33 $1,766.67 $0.00 $1,766.67 $52,000.00 21 01 May 2020 $52,000.00 $1,333.33 $422.22 $1,755.56 $0.00 $1,755.56 $50,666.67 22 01 June 2020 $50,666.67 $1,333.33 $411.11 $1,744.44 $0.00 $1,744.44 $49,333.33 23 01 July 2020 $49,333.33 $1,333.33 $400.00 $1,733.33 $0.00 $1,733.33 $48,000.00 24 01 August 2020 $48,000.00 $1,333.33 $388.89 $1,722.22 $200.00 $1,922.22 $46,666.67 25 01 September 2020 $46,666.67 $1,333.33 $377.78 $1,711.11 $0.00 $1,711.11 $45,333.33 26 01 October 2020 $45,333.33 $1,333.33 $366.67 $1,700.00 $0.00 $1,700.00 $44,000.00 27 01 November 2020 $44,000.00 $1,333.33 $355.56 $1,688.89 $0.00 $1,688.89 $42,666.67 28 01 December 2020 $42,666.67 $1,333.33 $344.44 $1,677.78 $0.00 $1,677.78 $41,333.33 29 01 January 2021 $41,333.33 $1,333.33 $333.33 $1,666.67 $0.00 $1,666.67 $40,000.00 30 01 February 2021 $40,000.00 $1,333.33 $322.22 $1,655.56 $0.00 $1,655.56 $38,666.67 31 01 March 2021 $38,666.67 $1,333.33 $311.11 $1,644.44 $0.00 $1,644.44 $37,333.33 32 01 April 2021 $37,333.33 $1,333.33 $300.00 $1,633.33 $0.00 $1,633.33 $36,000.00 33 01 May 2021 $36,000.00 $1,333.33 $288.89 $1,622.22 $0.00 $1,622.22 $34,666.67 34 01 June 2021 $34,666.67 $1,333.33 $277.78 $1,611.11 $0.00 $1,611.11 $33,333.33 35 01 July 2021 $33,333.33 $1,333.33 $266.67 $1,600.00 $0.00 $1,600.00 $32,000.00 36 01 August 2021 $32,000.00 $1,333.33 $255.56 $1,588.89 $200.00 $1,788.89 $30,666.67 37 01 September 2021 $30,666.67 $1,333.33 $244.44 $1,577.78 $0.00 $1,577.78 $29,333.33 38 01 October 2021 $29,333.33 $1,333.33 $233.33 $1,566.67 $0.00 $1,566.67 $28,000.00 39 01 November 2021 $28,000.00 $1,333.33 $222.22 $1,555.56 $0.00 $1,555.56 $26,666.67 40 01 December 2021 $26,666.67 $1,333.33 $211.11 $1,544.44 $0.00 $1,544.44 $25,333.33 41 01 January 2022 $25,333.33 $1,333.33 $200.00 $1,533.33 $0.00 $1,533.33 $24,000.00 42 01 February 2022 $24,000.00 $1,333.33 $188.89 $1,522.22 $0.00 $1,522.22 $22,666.67 43 01 March 2022 $22,666.67 $1,333.33 $177.78 $1,511.11 $0.00 $1,511.11 $21,333.33 44 01 April 2022 $21,333.33 $1,333.33 $166.67 $1,500.00 $0.00 $1,500.00 $20,000.00 45 01 May 2022 $20,000.00 $1,333.33 $155.56 $1,488.89 $0.00 $1,488.89 $18,666.67 46 01 June 2022 $18,666.67 $1,333.33 $144.44 $1,477.78 $0.00 $1,477.78 $17,333.33 47 01 July 2022 $17,333.33 $1,333.33 $133.33 $1,466.67 $0.00 $1,466.67 $16,000.00 48 01 August 2022 $16,000.00 $1,333.33 $122.22 $1,455.56 $200.00 $1,655.56 $14,666.67 49 01 September 2022 $14,666.67 $1,333.33 $111.11 $1,444.44 $0.00 $1,444.44 $13,333.33 50 01 October 2022 $13,333.33 $1,333.33 $100.00 $1,433.33 $0.00 $1,433.33 $12,000.00 51 01 November 2022 $12,000.00 $1,333.33 $88.89 $1,422.22 $0.00 $1,422.22 $10,666.67 52 01 December 2022 $10,666.67 $1,333.33 $77.78 $1,411.11 $0.00 $1,411.11 $9,333.33 53 01 January 2023 $9,333.33 $1,333.33 $66.67 $1,400.00 $0.00 $1,400.00 $8,000.00 54 01 February 2023 $8,000.00 $1,333.33 $55.56 $1,388.89 $0.00 $1,388.89 $6,666.67 55 01 March 2023 $6,666.67 $1,333.33 $44.44 $1,377.78 $0.00 $1,377.78 $5,333.33 56 01 April 2023 $5,333.33 $1,333.33 $33.33 $1,366.67 $0.00 $1,366.67 $4,000.00 57 01 May 2023 $4,000.00 $1,333.33 $22.22 $1,355.56 $0.00 $1,355.56 $2,666.67 58 01 June 2023 $2,666.67 $1,333.33 $11.11 $1,344.44 $0.00 $1,344.44 $1,333.33 59 01 July 2023 $1,333.33 $1,333.33 $0.00 $1,333.33 $0.00 $1,333.33 $0.00 60 31 July 2023 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Total $80,000.00 $19,666.67 $99,666.67 $1,000.00 $1,00,666.67