Question

Your dad would like to finance you to complete college. He agrees to lend you $12,000 on your 21st birthday when you join college and agrees to increase the amount lent each year by $2,000 for the next 3 years. If you have to pay him back $18,000 per year on your 28th birthday to your 32nd birthday, what internal rate of return per year compounded yearly did you end up paying for the amount borrowed from your dad?

3.5% per year compounded yearly 4.9% per year compounded yearly 5.7% per year compounded yearly 6.9% per year compounded yearly

please show work in excel

Answer 1

Year	Age	Cashflow	PV factor@ 5%	PV @ 5%	PV factor@ 7%	PV @ 7%
0	21	12,000	1.000	12,000	1.000	12,000
1	22	14,000	0.952	13,333	0.935	13,084
2	23	16,000	0.907	14,512	0.873	13,975
3	24	18,000	0.864	15,549	0.816	14,693
4	25		0.823	-	0.763	-
5	26		0.784	-	0.713	-
6	27		0.746	-	0.666	-
7	28	(18,000)	0.711	(12,792)	0.623	(11,209)
8	29	(18,000)	0.677	(12,183)	0.582	(10,476)
9	30	(18,000)	0.645	(11,603)	0.544	(9,791)
10	31	(18,000)	0.614	(11,050)	0.508	(9,150)
11	32	(18,000)	0.585	(10,524)	0.475	(8,552)
				(2,758)		4,574
	IRR	=Lower rate + Difference in rates*(NPV at lower rate)/(Lower rate NPV-Higher rate NPV)
		=5%+2%*(-2758/(-2758-4574)
		5.75%