Question

Your clients want help figuring out how much they need to save for their child's education. If your clients' child is currently 4 years old and will start college at 18 years of age, how much do they need to save at the end of each month to reach their goal? Assume the following: • You estimate that a year of tuition in line with your clients' goal is $7,000 in today's dollars • College expenses inflate at 4.4% per year • Your clients can earn an annual rate of return of 8.8% on their investments while their child is in college • Your clients want to help their child with 6 years of college • Tuition is due in full at the beginning of each year of college

Answer 1

New Microsoft Office Excel Worksheet Microsoft Excel Seed Formulas Data Home Insert Page Layout Review View Cut Σ AutoSum A A Currency Calibri 11 Wrap Text E Copy Сopy Fill BIU EB E Merge & Center Sort & Find & Paste +.0 .00 .00.0 Conditional Format Cell Insert Delete Format % 2 Clear Format Painter Filter Select Formatting as Table Styles Editing Clipboard Styles Font Alignment Number Cells fPV(8.8%,6,0,-85700,0) J3519 X G Н J K L M P Q R S T V W present value of total pv(rate,nper,pmt fv,type) rate value of fees of education at the beginning of year 18 8.8% nper 6 pmt 0 fv 85700 type PV(8.8%,6,0,- 85700,0) Using present value 3519 function in MS excel $51,666.31 = 1 pmt(rate,nper,pv, fv,type) rate= 8.8%/12 =.7333% monthly payment= using pmt function in 3520 ms excel nper 14*12 -168 PMT(0.7333% pv 0 fv ,168,0,51666. ($157.04) 51666.31 type 0 31,0) He should save 157.04 monthly to achieve the 3521 investment objective note: it is assumed that monthly payment will 3522 due in month end 3523 3524 3525 3526 3527 3528 Sheet22 Sheet26 Sheet28Sheet29 Sheet24 Sheet25 Sheet27 Sheet23 w

New Microsoft Office Excel Worksheet Microsoft Excel Seed Formulas Data Home Insert Page Layout Review View %Cut Σ AutoSum A A Wrap Text Currency Calibri 11 ECopy Сopy Fill BIU EB E Merge & Center Sort & Find & Paste +.0 .00 .00.0 Conditional Format Cell Insert Delete Format % 2 Clear Format Painter Filter Select Formatting as Table Styles Editing Alignment Styles Clipboard Font Number Cells =PMT(0.7333 %,168,0,51666.31,0) J3520 X G H K L M P Q R S T V W present value of total value of fees of pv(rate,nper,pmt fv,type) rate education at the beginning of year 18 8.8% nper 6 pmt 0 fv 85700 type PV(8.8%,6,0, 85700,0) Using present value $51,666.31 3519 function in MS excel = 1 pmt(rate,nper,pv, fv,type) rate= 8.8%/12 =.7333% nper 14*12 -168 PMT(0.7333% monthly payment = using pmt function in 3520 ms excel 168,0,51666 pv 0 fv ($157.04) 51666.31 type 0 31,0) He should save 157.04 monthly to achieve the 3521 investment objective note: it is assumed that monthly payment will 3522 due in month end 3523 3524 3525 3526 3527 3528 Sheet23 Sheet24 Sheet25 Sheet26 Sheet27 Sheet28 Sheet29 4 Sheet22 w

expected fees of college in beginning of year 18	current year fees*(1+inflation rate)^n	7000*(1.044)^14	12791.0154
expected fees of college in beginning of year 19	fees in beginning of year 18*(1+inflation rate)^1	12791.015*1.044^1	13353.8197
expected fees of college in beginning of year 20	fees in beginning of year 18*(1+inflation rate)^2	12791.015*1.044^2	13941.3877
expected fees of college in beginning of year 21	fees in beginning of year 18*(1+inflation rate)^3	12791.015*1.044^3	14554.8088
expected fees of college in beginning of year 22	fees in beginning of year 18*(1+inflation rate)^4	12791.015*1.044^4	15195.2204
expected fees of college in beginning of year 23	fees in beginning of year 18*(1+inflation rate)^5	12791.015*1.044^5	15863.8101
total value of fees			85700.062
present value of total value of fees of education at the beginning of year 18 =Using present value function in MS excel	pv(rate,nper,pmt,fv,type) rate = 8.8% nper = 6 pmt =0 fv = 85700 type = 1	PV(8.8%,6,0,-85700,0)	$51,666.31
monthly payment = using pmt function in ms excel	pmt(rate,nper,pv,fv,type) rate = 8.8%/12 = .7333% nper = 14*12 =168 pv = 0 fv = 51666.31 type = 0	PMT(0.7333%,168,0,51666.31,0)	($157.04)
He should save 157.04 monthly to achieve the investment objective
note: it is assumed that monthly payment will due in month end