Question

EXCEL

The idea of buying a new machine to replace the older one arose at the last management team meeting. The older one would not be sold but could be used as backup if necessary. If Pecan Peanuts does decide to replace the older machine and buy a new one, the finance dept has indicated that the maximum monthly payment they could afford, based on current profit forecasts for the next six years, would be £2000 p.m. The Company’s bankers would be able to offer an interest rate of 9% to borrow the £96,000 cost of the new machine. What range of months would you recommend that the loan is taken over to keep within Pecan Peanuts’ budget?

{Insert calculation IN EXCEL and summarise result approx. 200 words

Insert calculation IN EXCEL and summarise result approx. 200 words}

Answer 1

Monthly payment that is within Pecan Peanuts’ budget= 2000

Converting the annual interest rate of 9% offered by the bankers into

monthly rate , ie. 9%/12= 0.75% or 0.0075 p.m.

for a period of n mths

for the principal loan amount borrowed= 96000

So, using the present value of ordinary annuity formula,

PV=Periodic Pmt.*(1-(1+r)^n)/r

where, the PV of the loan is given as 96000

Periodic pmt.=the maximum monthly pmt, ie. 2000

r= the monthly interset rate, 0.0075

n= no.of months to be found out----??

so, plugging in the above values, in the formula,

96000=2000*(1-(1+0.0075)^n)/0.0075

Solving for n,we get   n= 59.7278 mths.

so, the no.of months recommended that the loan is taken over to keep within Pecan Peanuts’ budget is

59 to 60 months

(Answer)