> 5747 =)bjbjUU "V7|7|$Blttttttt8$B)zB(Q,}(((((((.* N,h(t(tt(tt((RNtt$nRq$$)0B)$,qH,$ttttChapter 9 Update
Last Name:First Name:Team Number:Instructions: Do not delete any of the problems or instructions. Fill your answers in where you see boxes that are shaded yellow. Please keep all fonts at Times New Roman 12 point and do not increase the size of any equations produced by the equation editor. Dont forget to sign the last page before you submit this.
Important Note: In this Update, all computations for future and present value are to be based on the assumption that all investments are compounded monthly and grow according to the given Assumed Rate of Investment. You are encouraged to use Excel financial formulas to check your answers and to create a spreadsheet that you can easily alter to see how your clients investments will behave over time. (In fact, you should do this.) However, for this Update, you should use the appropriate formulas in the text to gain practice with them and demonstrate you know how to use them. The formula EMBED Equation.DSMT4 should NOT be used in this update because it does not assume discrete (monthly) compounding.
LISTNUM \l 1 First, lets do some preliminary calculations that pertain to your clients working life (between now and their retirement.) Assuming discrete compounding, we will need to know the interest rate per period (i) and the total number of compounding periods for our calculations below. Compute the values of i and n, assuming monthly compounding and other relevant given data. Give i to at least six (6) decimal places.
Result i:i = 0.000000Result n:n = Calculations:
LISTNUM \l 1 Consider your clients current Stock Portfolio balance. After you add in the lump sum your client invested into the stock option and any resulting profits, assume that no more money is invested into this portfolio. Determine the future value of the Portfolio at your clients retirement age. Show all calculations.
Result:$0.00Calculations:
LISTNUM \l 1 Now consider your clients 401K retirement account. Recall you were given the current balance and the monthly contribution your client will make to this account. Use the appropriate annuity formula to compute the future value of the 401K account at the clients proposed age of retirement. Show all calculations.
Result:$0.00Calculations:
LISTNUM \l 1 We now turn to your clients Roth IRA account. Recall you were given the current balance and the monthly contribution your client will make to this account. Use the appropriate annuity formula to compute the future value of the Roth IRA at the clients proposed age of retirement. Show all calculations.
Result:$0.00Calculations:
LISTNUM \l 1 Use all of your results from above to compute the total future value of ALL of your clients financial investments at the age of retirement.
Result:$0.00Calculations:
LISTNUM Keep in mind that your result in #( REF _Ref152142153 \r \h 5) is the future value of your clients investments at the time of his/her retirement. The spending power of that amount will be diminished by the effects of inflation. If you assume the inflation rate given to your team, what is the present value , in todays dollars, of your clients potential retirement account. Important: Assume that the effects of inflation are only taken into account on an annual basis rather than a monthly basis. Compute the ratio PV/FV to get an idea of how much spending power your client loses due to inflation. Show your calculations.
Result:$0.00PV/FV:0.000Calculations:
LISTNUM Once your client enters retirement, we will want to explore things such as how much money they might receive each year, the present value of such money, how their retirement balance will decline over time, etc. Before we do that, however, we will need to compute (potentially) new values for i and n that will apply to your clients investments while in retirement. Compute these values below and report them below. Include six (6) digits for i.
Result i:i = 0.000000Result n:n = Calculations:
LISTNUM Now lets return to the Future Value of your clients retirement assets at retirement age as reported in #( REF _Ref152142153 \r \h 5). Suppose your client chooses to annuitize that entire amount over the years between their Retirement Age and Expected Age of Death. Use an appropriate annuity formula to find the (future value of the) monthly and annual amount of money they can expect to receive if their remaining financial investments compound monthly and grow according to the Assumed Rate of Growth during retirement. (This rate should be lower than the assumed rate of growth while working toward retirement.) Show all of your calculations but note that you only have one set of calculations to doonce you know the monthly income, multiply by 12 to get the annual income.
Monthly Income:$0.00Annual Income:$0.00Calculations:
LISTNUM Once again, keep in mind that the annual income amount computed in #( REF _Ref152144229 \r \h 8) is a value in the future. What is the present value of your clients annual retirement after you take into account the effect of inflation? Assume the effects of inflation are annual, as we did in #( REF _Ref152144507 \r \h 6). Do this computation only for their first year of retirement. Well do the computations the rest of their retirement period in the second update for this chapter. Show all calculations.
Result:$0.00Calculations:$0.00
LISTNUM Compute the ratio PV/FV for your clients annual income in their first year of retirement and compare it to what you got in #( REF _Ref152144507 \r \h 6). Is it the same or different? Explain your result.
PV/FB:0.00Explanation:
LISTNUM Build a table with two columns. The first column should be for your clients age, starting with your clients retirement age and increasing each step by one year to the Expected Age of Death. The second column should be for the present value of your clients annual income in the corresponding year. (Note that these values should drop over time.) A sample table is shown. Hint: Youll want to do these calculations with Excel and record your results carefully.
Your Table:Age
Present Value of Annual Retire $
LISTNUM When you have built your table, draw a Scatter-Plot graph that shows the relationship between Age (x axis) and Present Value of Annual Retirement Income. Change your scales on the graph so that the minimum value is 55 and the maximum is 95. Past your graph below:
Graph:
LISTNUM Your client reported to you their financial goal at retirmement. That is, they indicated how much the hoped to have saved up at retirement. Did they reach the goal? And whether or not they did so, what advice would you give them about their retirement planning? Are they saving enough? Will they be able to retire comfortably? How would you make such a determination? Discuss your recommendations.
Your Say:
LISTNUM In situations like this, clients often realize they may not have enough money to retire comfortably. There are at least three primary ways to improve a clients financial status at retirement: (1) Contribute more on a monthly basis to the retirement account, (2) Put off retirement until a later age, and (3) Try to earn a higher average annual return on investments. Each one of these has its own set of drawbacks. Build a spreadsheet in Excel that allows the user to change the Retirement Age, the Assumed Rates of Growth both before and during retirement, and the amounts contributed to both the clients 401K account (monthly) and their Roth IRA(yearly max is $4000). The spreadsheet should then update itself to show the clients projected total financial resources at the age of retirement (including money from their stock option venture) as well as the clients first year of annual income during retirement. Have this ready for your teams oral presentation.
OPTIONAL CHALLENGES
LISTNUM For a more challenging task, edit your spreadsheet (see #( REF _Ref152149980 \r \h 13)) so that a graph of your clients annual income, adjusted for inflation, is automatically generated and updated as other parameters are changed. This is not required, but its a lot of fun and if you can do this, you can consider yourself an Excel Expert.
LISTNUM Finally, if you really want a challenge, edit your spreadsheet to systematically compute AND graph the MONTHLY BALANCE of your clients retirement account, and track that balance from their retirement age to their expected age of death. (Extra Excel bonus if you can track it from their current age all the way through their expected age of death!) The sheet should update if you change any of the important parameters, as discussed above. This is also not required, but will garner you the title Excel Ace.
Honor Code: With my signature below, I affirm that while I may have worked with other team members on this assignment, the final product is my own work. This means (a) I typed the work in this assignment myself and did not copy or allow another person to copy this file and (b) the work reflects my own understanding of the material. Violations of this code are subject to disciplinary action with the Vice President of Student Affairs. This assignment must be signed in order to be grades.
Signature_________________________________________________________Date___________
Page PAGE 4 of NUMPAGES \* MERGEFORMAT 4
*,9;HzGHWX) *
"
0
3
4
C
D
T\cqtu$256?@de 56]6]
jEHUjWI
CJUVaJ jU>*5\55B*phR*+,9:;yzp8p<pnn$$Ifl0 % 0
t0644
lal$If(<)zFG
!
"
0
\@\@$$Ifl0$ 'D
t0644
laL$$$$Ifa$ $$$If$$
0
1
2
3
lje\l@\l $$$If$$$$Ifl0$ 'D
t0644
laL$$$$Ifa$
T\@\$$Ifl0$ 'D
t0644
laL$$$$Ifa$ $$$If$$
T\bcqrst#c@ca\$$$$Ifl0$ 'D
t0644
laL$$$$Ifa$ $$$If
#$2345x@ocxaaocx8oc$$$$Ifa$ $$$If$$Ifl0$ 'D
t0644
laLe~kxab{|}.5CFGPQ~AW B*phj^UjUjdU 56]6]55\>* jUjUMx@ocxVTocx@$$^`$$$$Ifa$ $$$If$$Ifl0$ 'D
t0644
laL c@cVT$$^`$$Ifl0$ 'D
t0644
laL$$$$Ifa$ $$$If .45CDEFlXcl@clV$$^` $$$If$$Ifl0 '
t0644
laL$$$$Ifa$ FX`fgu{|}[baTa\$$$$Ifl0 '
t0644
laL$$$$Ifa$ $$$If
WX`gu}~!"#%&[bhuxyjqWcMW[\ef"0#3#G#H#I#R#S########$$$$&&(((()))))))j0JCJUaJCJaJjkU5\>* jXUjU jU5 B*phNbghuvwxVl<cUlHC$$$$^`
$$
!$If $$$If$$Ifl0 '
t0644
laL$$$$Ifa$VWcgvvvl$K$L$Ifl0064
la
$$$$IfK$a$ $$$If$$MWxssj^xQsj$$^`$$$$Ifa$ $$$If$$$$Ifl0$ 'D
t0644
laL WXYZ[2#3#G#H#$obb]SSS] %^`%$$$$^`$$Ifl0$ 'D
t0644
laL $$$If $$&&(((((((())))):);)$a$
! ^` .$$$d%d&d'dNOPQ]^ ^`$$ %^`%)))))))6)7)8)9):)=)CJaJ0JmHnHu0JCJaJmHnHuj0JCJUaJ0JCJaJ;)<)=)
! 1h/ =!"#$%Dd[@J
CA?"2IʓT;5{!.%DN`!ʓT;5{!. `S xڝRkAf6&8="6)I@f
^?W[CNDH4eҡE/w2&>JL'Wj
{]ěxe/5GVI
8E56ʹ?@ABCDEFGHIJKLMNOPQRRoot Entry
FW8@Data
,WordDocument "VObjectPoolRW_1225873362FRROle
CompObjiObjInfo
FMathType 5.0 EquationMathType EFEquation.DSMT49qc8<DSMT5WinAllBasicCodePagesTimes New RomanSymbolCourier NewMT Extra!/ED/APG_APAPAE%B_AC_AE*_HA@AHA*_D_E_E_A
F==PeEquation Native 1Table<,SummaryInformation( DocumentSummaryInformation8rtOh+'0
<HT
`lt|Chapter 10hapIT ServicesT ST SNormal.dotLawrence M6wrMicrosoft Word 9.0@02+@H(@2N+@ìY}՜.+,D՜.+,Xhp
"Seattle Central Community CollegenAq%
Chapter 10TitleH 6>
MTWinEqns
FMicrosoft Word Document
MSWo
i8@8NormalCJ_HaJmH sH tH R@R Heading 1$<@&5CJ KH OJQJ\^JaJ :@: Heading 2$$@&
5CJ\2@2 Heading 3$@&5\<@< Heading 4$$$@&a$5\<A@<Default Paragraph FontDC@DBody Text Indent ^` ,@,Header
!, @,Footer
!&)@!&Page NumberRY@2RDocument Map-D M
CJOJQJ^JaJ4>@B4Title$@&a$5CJaJNR@RNBody Text Indent 2$$%^`%,B@b, Body Text6]=%V *+,9:;yzFGX!"0123D
T
\
b
c
q
r
s
t
#$2345@
.45CDEFQX`fgu{|}[bghuvwxVWcgMWXYZ[f23GHS ""$$$$$:%>%00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 00000000
00000000000000000000000000
0000000000000@0
0 AAADeW)=)#)z0
T#FbVW$;)=) !"$%&'(*<)GW3C t
5?d
a|FP~}"%x[eHR =%:ZZZZZZZZZZZZZZZZ <>D!8@0(
B
S ?
_Ref151706840
_Ref152142153
_Ref152144507
_Ref152144229
_Ref1521499804u
6\>%4u
6\>%)*$$$$$$$%%%%%;%>%(sz!"$$$$$$$%%%%%%%;%>%33333333333333 KQRR{{EE5@daJ\.3BBF"""$$$$$$$$%%%%%%%%;%>%
Lawrence M'X:\SCCC\116\Updates\Ch09UpdatePart1.doc
Lawrence M'X:\SCCC\116\Updates\Ch09UpdatePart1.doc
Lawrence M'X:\SCCC\116\Updates\Ch09UpdatePart1.doc
Lawrence M'X:\SCCC\116\Updates\Ch09UpdatePart1.doc
Lawrence M"X:\SCCC\116\Updates\Ch09Update.doc
Lawrence M"X:\SCCC\116\Updates\Ch09Update.doc
Lawrence M"X:\SCCC\116\Updates\Ch09Update.doc
Lawrence MhC:\Documents and Settings\Lawrence M\Application Data\Microsoft\Word\AutoRecovery save of Ch09Update.asd
Lawrence M"X:\SCCC\116\Updates\Ch09Update.doc
Lawrence M"X:\SCCC\116\Updates\Ch09Update.docR~t
hh^h`hH)
^`hH)
88^8`hH)
^`hH()
^`hH()
pp^p`hH()
^ `hH.
@@^@`hH.
^`hH.R~tTk R~tm R~to k`)ky)l)Hlb()ld()l0()ly.(m.`m(.m`.ny.Tn.nb)nd()n0()4oy()lo()o(()(p`.`py..p...pb.... qd
.....hq0 ......qy
.......r
........Xr(
.........*+,9:;z&G!"012E
! T
\
b
c
q
r
s
#$2345
.45CDEX`fgu{|[bghuvwVWcgMWXY">%y0y0y0y0y0y0
y0@=%P@UnknownGz Times New Roman5Symbol3&z Arial5&z!Tahoma"1h3īҫ1&Y}AQ$x0dq%M
"2qHP
Chapter 10IT Services
Lawrence MCompObjjrdDocWord.Document.89q