In: Finance
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 $70,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.0% p.a. effective.
(a ): Level quarterly payment= $3,873.35
(b ): Amount that owe after one year= $57,076.10
(c ): Interest that pays in one year= $2,569.50
(d ): In repaid in weekly payments in arrears of $ 340.33579318617 over 5 years, nominal annual rate compounded weekly= 9.74%
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 $1402.1924701853 per month in advance for the coming 5 years.
(a) If Gillian can borrow/invest money at a rate of 3.5% 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"?
Cost of delivery service per month = $1,402.1924701853
Cost Period = 5 years = 60 months
a)
Payment Frequency = Monthly
Annual Effective Interest Rate = 3.5%
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 = 3.5%
Periods per year = number of compounding periods per year = 12
Annual Nominal Interest Rate = NOMINAL (3.5%, 12) = 3.445078%
Monthly Interest Rate = Annual Nominal Interest Rate / 12 = 3.445078%/12 = 0.287090%
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.287090%
number of periods = cost period in months = 60
payment amount = Cost of delivery service per month = $1,402.1924701853
future value = 0
when-due = when is the payment made each month = beginning = 1
Equivalent amount today of Gillian's future liabilities = PV(0.287090%, 60, -1402.1924701853, 0, 1) = $77,404.40
b)
Loan Amount = $70,000
Value of Services provided monthly = $1,402.1924701853
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,402.1924701853
present value = loan amount = $70,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, 1402.1924701853, -70000, 0, 1, 0.6%) = 0.646509%
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)12
1+AER = (1+0.646509%)12
1+AER = 1.080400
Annual Effective Rate, AER = 1.080400 - 1 = 0.080400 = 8.04%