MODULE 1 – T OPIC 1
Weight: This topic is worth 50 marks out of the 100 available for the Assignment.
QUESTION 1
Weight: This question is worth 23 marks out of 100.
On the first of May 2015 Lee and Kim borrowed $250,000 from the Eastside Bank in order to buy an apartment. The loan was to be repaid through 180 monthly payments over a period of 15 years, with the first payment due on the first of June 2015, and each payment being the same size. The interest rate charged by the eastside bank was
j 12=5.04% p.a.
a) Illustrate all the cash flows associated with this loan as a fully labelled timeline diagram
PV=250 ($’000)
R R R R R R
|_______|_______|_______|___…___|_______|_______|
0 1 2 3 178 179 180 months
May’15 Jun’15 May’30 (optional extra detail)
b) Determine the size of the monthly payments. Enter this value to the nearest cent as an answer to “Assignment Module 1 – Milestones” question 1b. You can have as many attempts at this as you wish without penalty, but this question will close at 9:00pm on Wednesday 17 August. There will be no marks available for this question part after then. NB You do NOT need to include your workings for this question in your submitted assignment paper.
[1 mark]
PV = 250000, i = j 12/12 = 0.0042, n=180, we have a simple annuity
PV = R(1‐(1+i)‐n)/i => 250000 = R(1‐1.0042‐180)/0.0042 => 250000 = 126.122669R => R = 1982.197189
i.e. R = $1,982.20 to the nearest cent
Or in Excel: =PMT(0.0504/12,180,‐250000) => R = $1,982.20 to the nearest cent.
c) Describe and carry out two sanity checks on your answer to part b).
1) If the payments are going to eventually pay off the loan payments must be greater than monthly interest charge on the principal, i.e. greater than $250000 * 0.0042 = $1050. As R = $1982.20 > $1050.00, R=$1982.20 is feasible.
2) Payments must be greater than the payments if there was no interest being charged. If there was no interest change, payments would be $250000 / 180 = $1388.89. As R = $1982.20 > $1388.89, R=$1982.20 is feasible.
d) Determine the size of the outstanding principal immediately after Lee and Kim’s 20 the payment. Enter this value to the nearest cent as an answer to “Assignment Module 1 – Milestones” question 1d. You can have as many attempts at this as you wish without penalty, but this question will close at 9:00pm on Wednesday
17 August. There will be no marks available for this question part after then. NB You do NOT need to include your workings for this question in your submitted assignment paper.
OP=PV(remaining 160 payments) = R(1‐(1+i)‐160)/i = 1982.20(1‐(1.0042)‐160)/0.0042 = $230593.4046 => $230,593.40 Or In Excel: =PV(0.0504/12,160,‐1982.20) => $230,593.40
e) If the interest rate charged by the Eastside Bank increases to j 12 = 5.52% p.a. immediately after Lee and Kim’s 20 th payment, determine how many more payments it will take to pay off the loan, assuming that Lee and Kim don’t change their payment size.
New i = new j 12/12 = 0.0552/12 = 0.0046, R = 1982.20 OP = 230,593.40 (the PV of what remains to be paid)
n = ‐ln(1‐PVi/R)/ln(1+i) = ‐ln(1‐230593.40*0.0046/1982.20)/lm(1.0046) = 166.9027044
That is 166 more full sized payments, followed by a partial payment.
Or in Excel: =NPER(0.0552/12,1982.2,‐230593.40) => 166.9027044
f) Determine the size of the partial payment
PV(full payments) = R(1‐(1+i)‐n)/i = 1982.20(1‐1.0046‐166)/0.0046 = 229,276.77
PV(partial payment) = OP – PV(full payments) = 230593.40 ‐ 229276.77 = 831.63
But in 167 months this will be worth 831.63 * 1.0046 167 = 1789.74 => partial payment is $1789.74
IN Excel: PV(full payments) = PV(0.0552/12,166,‐1982.2) => 229761.77
PV(partial payment) = OP – PV(full payments) = 230593.40 ‐ 229276.77 = 831.63
But in 167 months this be =FV(0.0552/12,167,0,‐831.63) = 1789.74 => partial payment is $1789.74
g) Describe and carry out a strong sanity check on your answer to f).
Describe: Strong sanity check… the fractional part of n multiplied by the payment size should be quite similar to the partial payment size.
Carry out: 0.9027044 * 1982.20 = 1789.34, which is very similar to 1789.74, so 1789.74 is feasible.
h) Construct an amortization schedule, showing the last 3 payments . Ensure that you include example calculations for one line of the schedule, and that you show how
you got the starting OP for the table.
OP=?
1982.20 1982.20 1789.74 $
|_______|_______|_______|
165 166 167 months
OP = PV(remaining payments) = 1982.20*1.0046‐1 + 1982.20*1.0046‐2 + 1789.74*1.0046‐3 = $ 5702.48
payment interest OP
164 5702.48
165 1982.20 26.23 3746.51
166 1982.20 17.23 1781.54
167 1789.74 8.20 0.00
Example calculations:
5702.48 * 0.0046 => 26.23, 5702.48 – 1982.20 + 26.23 => 3746.51
i) Lee and Lim decide that rather than making a partial payment the month after the full payments finished, they would rather make the last full payment large enough to retire the loan then. Determine the size that this larger payment would need to be. Ensure that you explain the logic of the approach.
There are a number of ways to attack this, but the easiest is to note that at the end of period 165 they still owed 3746.51, and that in period 166 they would owe this amount plus a month’s interest, i.e.
3746.51 + 17.23 = $3763.74.
Or we could discount the partial payment by one period and add it to the full payment size:
1982.20 + 1789.74*1.0046‐1 = $3763.74
Or we could even take PV0 and move it forward by 166 months and add it to the full payment size: 831.63 * 1.0046 166 + 1982.20 = $3763.75 (slight difference is due to 831.63 being a rounded number
What we can’t do is add the period 166 and 167 payments, as these are measure in different units .
j) Referring back to question e), determine what the new payment size would need to be if Lee and Kim wanted to retire the debt by May 2030.
Very simply with have an OP of 230593.40, n=160 and new i = 0.0046
PV = R(1‐(1+i)‐n)/i => 230593.40 = R(1‐1.0046‐160)/0.0046 => 230593.40 = 113.079284 R
=> R = $2039.22 would be the new monthly payment size.
Or in Excel: =PMT(0.0552/12,160,‐230593.4) => R = $2039.22 would be the new monthly payment size.
