Question

Tommy, a former student in Dr. Smith from Economy class, opened an account at First National Bank on July 1, 2008 and made an initial deposit of $500. She then made 8 additional yearly deposits beginning with July 1, 2009. The first additional deposit was $1,000 and this decreased by $100 per deposit after that. The bank paid a nominal interest rate of 4% compounded quarterly through July 1, 2014. After July 1, 2014 it changed the nominal rate to 3.0% compounded monthly.

Immediately after making the last deposit (total 9 deposits), Sally decided to close out his account on July 1, 2016. Using Excel functions, determine how much money Sally received when she closed out the account.

Answer 1

Using Excel functions

Effective interest rate till July 1,2014	4.060%
Effective interest rate after July 1,2014	3.042%
Date	Deposit
1 July,2008	500
1 July,2009	1000
1 July,2010	900
1 July,2011	800
1 July,2012	700
1 July,2013	600
1 July,2014	500
1 July,2015	400
1 July,2016	300
NPV of cash flow from Jul 1, 2008 to Jul 1,2014	4,484.56
NPV of cash flow from Jul 1, 2015 to Jul 1,2016	528.25
Total NPV	5,012.81
Future worth	12,923.48

Showing formula in excel

Effective interest rate till July 1,2014	=EFFECT(4%,4)
Effective interest rate after July 1,2014	=EFFECT(3%,12)
Date	Deposit
1 July,2008	500
1 July,2009	1000
1 July,2010	=F5-100
1 July,2011	=F6-100
1 July,2012	=F7-100
1 July,2013	=F8-100
1 July,2014	=F9-100
1 July,2015	=F10-100
1 July,2016	=F11-100
NPV of cash flow from Jul 1, 2008 to Jul 1,2014	=NPV(F1,F5:F10)+F4
NPV of cash flow from Jul 1, 2015 to Jul 1,2016	=PV(F1,6,,-(NPV(F2,F11:F12)))
Total NPV	=F14+F15
Future worth	=FV(F2,2,-FV(F1,6,,-F16))