In: Finance
Please answer in EXCEL
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 $78,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.2% p.a. effective.
(a)Geoffrey believes that the overall benefit from this agreement amounts to $369.52448601134 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 $78,000 and this weekly benefit of $369.52448601134, 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 $1557.7922862927 per month in advance for the coming 5 years.
(a) If Gillian can borrow/invest money at a rate of 3.7% 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"?
Given:
Single Payment to Gillian = $ 78,000
Tenure = 5 years
Interest rate = 4.2%
Solution 1a:
Overall Benefits from the agreement = $369.52448601134 per week
Initial cost to be considered = $ 78,000
The interest rate that represents the return on this investment, (compounding weekly) can be calculated using RATE function in Excel as shown below:
In this case, Tenure is considered in weeks, as benefits are taken per week and compunding is done weekly.
One year has 52 weeks, Hence Tenure = 5 * 52.1775 weeks = 260.714 weeks
Loan amount | 78,000 |
Weekly Payment | 369.524486 |
Tenure | 260.714 |
Interest rate | 0.17% |
Annual Interest rate | 8.74% |
Formulas used are:
RATE(nper, pmt, pv) formula is used, where
nper is the number of periods = 260.714
pmt is the periodic payment value = $369.52448601134
pv is the present value = $ 78,000
The result obtained is interest rate per week, hence to obtain annual interest rate, we have multiplied the result with 52.1775 (Since, one year has 52.1775 weeks)
Hence, the required Annual Interest rate is 8.74%
Solution a:
Cost of services = $ 1557.7922862927 per month
Interest rate = 3.7%
Tenure = 5 years = 60 months
The equivalent amount today of her future liabilities can be calculated using the Present Value (PV) formula in Excel as follows:
Cost of Services | 1557.792286 |
Interest rate per annum | 3.70% |
Interest rate per month | 0.003083333 |
Tenure (months) | 60 |
Present Value of future Liabilities | $85,211.64 |
Formula used as follows:
PV(rate, nper, pmt) formula has been used where
rate is the interest rate per period = 0.037
nper is number of periods = 60
pmt is te periodic payment value= 1557.7922862927
Hence, the Present Value of the Future Liabilities is $ 85,211.64
Solution b:
The money she receives from Geoffrey can be considered a loan, Hence Loan amount = $ 78,000
Repayments are the value of the services she provides in return. Hence, Repayment = $ 1557.7922862927 per month.
Tenure = 5 years = 5*12 = 60 months
Effective annual rate, she is being charged on this "loan"=?
Interest rate can be calculated using RATE function in Excel as follows:
Loan amount | 78,000 |
Repayment | 1557.792 |
Tenure (months) | 60 |
Interest rate per month | 0.61% |
Interest rate per annum | 7.36% |
Formula used in Excel:
RATE(nper, pmt, pv) formula is used, where
nper is the number of periods = 60
pmt is the periodic payment value = $ 1557.7922862927
pv is the present value = $ 78,000
The result obtained is interest rate per month, hence to obtain annual interest rate, we have multiplied the result with 12 (Since, one year has 12 months)
Hence, effective annual rate, she is being charged on this "loan" = 7.36%