Question

Geoffrey decides not to buy the car mentioned earlier. Instead, he is now considering a food delivery service "You, bars, meats" that his friend Gillian has recently started. Gillian has agreed that for a single payment of $80,000 today to help her launch her business, she will provide all the delivery services that Geoffrey needs for his business for the next 5 years. Geoffrey is considering borrowing the full amount from his business account.

Suppose that Geoffrey makes level quarterly repayments over the coming 5 years, the first payment being exactly 3 months from today. Again, the interest rate on Geoffrey's account is 4.6% p.a. effective.

(a) Calculate the size of the level quarterly repayment.

(b) How much money does Geoffrey owe on this loan after 1 year?

(c) How much interest does Geoffrey pay in the first year?

(d) Geoffrey believes that the overall benefit from this agreement amounts to $386.00549595166 per week in arrears (this would include money he would have spent on alternative delivery services, estimated additional profits from using Gillian's services, etc).

By considering only the initial cost of $80,000 and this weekly benefit of $386.00549595166, calculate the interest rate that represents the return on this investment, expressed as a nominal annual rate compounding weekly.

Gillian has entered the agreement with Geoffrey described above. She estimates that the costs of the delivery services she has promised to Geoffrey (petrol, insurance, wear and tear, etc) amount to $1608.9831851444 per month in advance for the coming 5 years.

(a) If Gillian can borrow/invest money at a rate of 4.1% p.a. effective, what is the equivalent amount today of her future liabilities? Note that this calculation should not involve the payment she receives from Geoffrey today.

(b) The money she receives from Geoffrey can be considered a loan, with repayments being the value of the services she provides in return. What is the
interest rate, expressed as an effective annual rate, she is being charged on this "loan"?

Answer 1

1.

a)

Loan amount = $80,000

Loan Period = 5 years

Payment Frequency = Quarterly

Annual Effective Interest Rate = 4.6%

Annual Nominal Interest Rate for Quarterly compounding can be calculated from Annual Effective Rate by using the NOMINAL function in spreadsheet

NOMINAL (Effective Rate, Periods per year)

Where, Effective Rate = Annual Effective Rate = 4.6%

Periods per year = number of compounding periods per year = 4

Annual Nominal Interest Rate = NOMINAL (4.6%, 4) = 4.522714%

Quarterly Interest Rate = Annual Nominal Interest Rate / 4 = 4.522714%/4 = 1.130679%

The Quarterly loan payment can be calculated using the PMT function in spreadsheet

PMT(rate, number of periods, present value, future value, when-due)

Where, rate = quarterly interest rate = 1.130679%

number of periods = no.of quarterly periods = 5*4 = 20

present value = loan amount = $80,000

future value = loan amount after the loan period = 0

when-due = when is the payment made each quarter = end = 0

Quarterly loan payment = PMT(1.130679%, 20, 80000, 0, 0) = $4,491.78

b)

Money owed by Geoffrey after a year can be calculated using the PV function in spreadsheet

PV(rate, number of periods, payment amount, future value, when-due)

Where, rate = quarterly interest rate = 1.130679%

number of periods = number of periods remaining in the loan = (5-1)*4 = 16

payment amount = Quarterly loan payment = $4,491.78

future value = loan amount after the loan period = 0

when-due = when is the payment made each quarter = end = 0

Money owed by Geoffrey after a year = PV(1.130679%, 16, -4491.78, 0, 0) = $65,405.86

c)

Total Payments made by Geoffrey in the first year = Quarterly payment * 4 = $4,491.78*4 = $17,967.11

Principal Payment made in first year = Principal balance at the start of the loan - principal balance after 1 year

= $80,000 - $65,405.86 = $14,594.14

Interest payment made in first year = total payments made in first year - principal payment made in first year

= $17,967.11 - $14,594.14 = $3,372.97

d)

Initial Cost = $80,000

Weekly benefit = $386.00549595166

No. of weekly payments = 5*52 = 260

Weekly return on this investment can be calculated using the RATE function in spreadsheet

RATE(number of periods, payment per period, present value, future value, when-due, rate guess)

Where, number of periods = no.of weekly periods = 260

payment per period = weekly benefit = $386.00549595166

present value = Initial Cost = $80,000

future value = 0

when-due = when is the benefit received each week = end = 0

rate guess = a guess of the weekly return = 0.2%

Weekly return on this investment = RATE(260, $386.00549595166, -80000, 0, 0, 0.2%) = 0.180962%

This is a weekly rate. To convert this to a nominal annual rate we have to multiply this value by 52.

Nominal annual rate = 0.180962% *52 = 9.41%

2.

Cost of delivery service per month = $1,608.9831851444

Cost Period = 5 years = 60 months

a)

Payment Frequency = Monthly

Annual Effective Interest Rate = 4.1%

Annual Nominal Interest Rate for monthly compounding can be calculated from Annual Effective Rate by using the NOMINAL function in spreadsheet

NOMINAL (Effective Rate, Periods per year)

Where, Effective Rate = Annual Effective Rate = 4.1%

Periods per year = number of compounding periods per year = 12

Annual Nominal Interest Rate = NOMINAL (4.1%, 12) = 4.024914%

Monthly Interest Rate = Annual Nominal Interest Rate / 12 = 4.024914%/12 = 0.335409%

Present Value of Gillian's Future liabilities can be calculated using the PV function in spreadsheet

PV(rate, number of periods, payment amount, future value, when-due)

Where, rate = monthly interest rate = 0.335409%

number of periods = cost period in months = 60

payment amount = Cost of delivery service per month = $1,608.9831851444

future value = 0

when-due = when is the payment made each month = beginning = 1

Money owed by Geoffrey after a year = PV(0.335409%, 60, -1608.9831851444, 0, 1) = $87,605.83

b)

Loan Amount = $80,000

Value of Services provided monthly = $1,608.9831851444

Loan Period = 5 years = 60 months

Monthly interest rate on this loan can be calculated using the RATE function in spreadsheet

RATE(number of periods, payment per period, present value, future value, when-due, rate guess)

Where, number of periods = no.of months of loan period = 60

payment per period = monthly value of services = $1,608.9831851444

present value = loan amount = $80,000

future value = 0

when-due = when is the payment made each month = beginning = 1

rate guess = a guess of the monthly interest rate = 0.6%

Monthly interest rate on this loan = RATE(60, 1608.9831851444, -80000, 0, 1, 0.6%) = 0.661247%

This is a monthly rate. To convert this to an effective annual rate (AER) we have to use the formula

1+Annual Effective Rate = (1+monthly interest rate)¹²

1+AER = (1+0.661247%)¹²

1+AER = 1.082300

Annual Effective Rate, AER = 1.082300 - 1 = 0.082300 = 8.23%