Question

It is September 1, 2019 and Richard Spender has a problem... HE SPENDS TOO MUCH! Richard has managed to rack up some impressive debts over the past few years; however, he has another problem. He has four kids: a 14 year old son, a 13 year old daughter, and twins (a boy and a girl) aged 11 who will all be going to university. Each child will begin university in September of the year they turn 18 (so for his 14 year old son, there are exactly 4 years to go, for his 13 year old daugter there are 5 years to go, and for his twins
there are 7 years to go). Each child will require								$5,266
per year for four years, for tuition payments payable each September. Richard would like to set up a savings plan to cover this expense. As his Financial Advisor, you can offer him an interest rate of 3% compounded monthly for a college savings plan. However, Richard must take care of his other debts as well:
Type of Debt			Outstanding Principal
Credit Card 1			$6,800
Credit Card 2			$200
Credit Card 3			$1,900
Credit Card 4			$6,030
Line of Credit			$105,200
Car Loan			$46,000
Mortgage			$360,000
You have offered to consolidate all of Richard's debts into a single loan with a 10 year term and interest at 6% compounded monthly. Because he would like to continue his spending ways, Richard would like to pay as little as possible and will not accumulate any additional savings during the 10 years beyond what he is saving to meet his children's tuition expenses. Richard would like to make EQUAL payments at the end of each month that will save exactly enough to pay for his children's education and eliminate all of his debts.
a) How much must Richard save each month in the college
savings plan?
b) How much must he pay each month towards his debts?
									↑

Answer 1

		a)	College Saving Plan
		Rate	Annual interest Rate	3%
		Pmt	Annual expense in each year	$5,266
		Nper	Number of years	4
			Present Value(PV) of expenses for each child	$20,161.47	(Using PV function of excel with Rate=3%,Nper=4,Pmt=-5266,Type=1
					(Note :Type=1,because expenses are at the beginning of years)
				N	E	PW=E/(1.03^N)
				Year	Expense	Worth of expense today
			PV of expense at year 4(for 14 year old son)	4	$20,161.47	17913.20265
			PV of expense at year 5(for 13year old daughter)	5	$20,161.47	17391.45888
			PV of expense at year 7(for twins)	7	$40,322.93	32786.23599
					SUM	68090.89752
			PRESENT WORTH OF TOTAL EDUCATIONAL EXPENSES	$68,091
		Rate	Monthly interest rate for college saving plan	0.0025	(3/12)%
		Nper	Number of months of savings	120	(10*12)
		Pv	Present Worth of savings required	$68,091
		PMT	Monthly savings required in college saving	$657.49	(Using PMT function of excel with Rate=0.0025,Nper=120,Pv=-68091)
		b)	Payment Towards Debt
		Pv	Total Consolidated Loan	$526,130	(6800+200+1900+6030+105200+46000+360000)
		Rate	Monthly interest =(6/12)%=	0.005
		Nper	Number of months of payment	120	(10*12)
		PMT	Monthly Payment Required towards debt	$5,841.12	(Using PMT function of excel with Rate=0.005,Nper=120,Pv=-526130)