Question

Show how to solve via excel spreadsheet please

A mechanical engineering graduate who wanted to have his own business borrowed $350,000 from his father as start-up money. Because he was family, his father charged interest at only 4% per year. If the engineer was able to pay his father $15,000 in year 1, $36,700 in year 2, and amounts increasing by $21,700 each year, how many years did it take for the engineer to repay the loan?

Answer 1

ANSWER:

i have solved in excel and manually via trial and error.

year	0	1	2	3	4	5	6
cash flows	-350,000	15,000	36,700	58,400	80,100	101,800	123,500
npv	$17.52

the answer is 6 years.

As we know that the amount increases via $21,700 each year starting year 2,

manually:

Present value = $350,000

i = 4%

first amount = $15,000

increase in amount = $21,700

pv = first amount(p/a,i,n) + increase in amount(p/g,i,n)

350,000 = 15,000(p/a,4%,n) + 21,700(p/g,4%,n)

solving via trial and error when we put n = 6 we get

350,000 = 15,000(p/a,4%,6) + 21,700(p/g,4%,6)

350,000 = 15,000 * 5.242 + 21,700 * 12.506

350,000 = 78,630 + 271,380.2

350,000 = 350,010.2

so we can see that n = 6 years the present value is slightly more then $350,000 and so it will take 6 years to repay the loan.