# How to Start Investing in the Stock Market - Excel by rwb11526

VIEWS: 183 PAGES: 13

How to Start Investing in the Stock Market document sample

• pg 1
```									      Problem 6.31                                                                                     Present Value

Tirade Owens, a professional athlete, currently has a contract that will pay him a large amount in the first year of his contract and smaller amounts
thereafter. He and his agent, Row Rosenstinck have asked the team to restructure the contract. The team, though reluctant, obliged. Tirade and his
agent came up with a counter offer.

What are the present values of each of the contracts using a 14 percent discount rate?

Year      Current Contract        Team's Offer         Counter Offer
1         \$8,125,000.00          \$4,000,000.00        \$5,250,000.00
2         \$3,650,000.00          \$3,825,000.00        \$7,550,000.00
3         \$2,715,000.00          \$3,850,000.00        \$3,625,000.00
4         \$1,822,250.00          \$3,925,000.00        \$2,800,000.00

Hint: Find the present value of the contracts first using formulas and then by using the NPV function: NPV(rate,value1,value2, ...).
Make sure that all cells are properly formatted.

Solution using formulas                                               Solution using NPV function
Discount Rate:                                                  Discount Rate:

PV of Current                                         PV of Current Contract:
PV of Team's Offer:                                            PV of Team's Offer:
PV of Counter Offer:                                          PV of Counter Offer:

Which of the three contracts has the highest present value?

Best Value:
Problem 6.32                                                                   Future Value

Gary Kornig will turn 30 years old next year. He comes up with a plan to save for his retirement at 65 years of age.
Currently, he has saved \$6,950 in an IRA account earning 8.3 percent annually. He also currently has invested an
inheritance of \$5,000 in money market account earning 5.25 percent and plans to leave it as part of his retirement
savings. He has set himself a retirement target of \$1,000,000. He plans to put aside a fixed amount every year, starting
next year, in a mutual fund that will earn 9 percent annually.

How much will he have to save every year in order to achieve his goal?

Hint: Determine what his current savings will grow to by his retirement age, using the future value of a lump equation
as well as the FV function: FV(rate,nper,pmt,pv,type). Then solve for the monthly deposit necessary to accumulate the
difference, using an equation and then the PMT financial function: PMT(rate,nper,pv,fv,type). Make sure that all cells
are properly formatted.

Current age:                                        Retirement age:

IRA Account

Current balance in IRA:
Return on IRA account:
Years to retirement:

Value of IRA at retirement age:
Value of IRA at retirement age:

Money Market Account

Current balance in Money Market:
Return on Money Market account:
Years to retirement:

Value of Money Market at retirement age:
Value of Money Market at retirement age:

Savings Needed

Target retirement balance:
Future value of current savings:
Amount needed to reach target:
Expected return on mutual fund:
Years to retirement:

Annual deposit needed to reach target:
Annual deposit needed to reach target:
Problem 6.33                                                                 Computing Annuity Payments

Babu Baradwaj is planning to save for his son’s college tuition. His son is currently 11 years old and will begin college in seven
years. He has an index fund investment of \$7,500 earning 9.5 percent annually. College expenses in a state university in
Maryland currently costs \$15,000 per year but are expected to grow at roughly 6 percent every year. Babu plans to invest a
certain amount in a mutual fund that will earn 11 percent annually to make up the difference between the tuition cost and his
current savings. In all he will make seven payments starting today with the last payment being a year from the time the son will
begin college.

a. What will be the present value of the fours years of college expenses just when the son starts college?
Assume a discount rate of 5.5 percent.

Hint: Determine the inflated cost of each of the four years of college and then compute the present value of college expenses at
the time the son begins college. Use the future value of a lump sum equation as well as the FV function:
FV(rate,nper,pmt,pv,type). Next, use present value equations and then the NPV financial function: NPV(rate,value1,value2, ...).
Make sure that all cells are properly formatted.

Annual cost of college tuition today (t0):                   Expected increase in annual tuition costs (g):
Son's current age:                                 Son's age when entering college:

Four year tuition costs
Years from now FV calculation Tuition costs
0
1
2
3

Discount rate (i):

Present value of tuition costs (formula):
Present value of tuition costs (function):

b. What will be the value of the index mutual fund when the son just starts college?

Balance in index fund:
Return on the fund:

Value of the fund at start of college (formula):
Value of the fund at start of college (function):

c. What is the amount that the father has to have saved when his son turns 18?

PV of tuition costs - FV of investment:

d. How much will the father have to invest every year in order for him to have enough funds to cover all tuition expenses?

Hint: Solve for the PMT using the future value of an annuity equation and then use the PMT financial function:
PMT(rate,nper,pv,fv,type). Remember that the payments will begin today.

Expected return on mutual fund:
Savings required:

Required annuity payment:
Required annuity payment:
Problem 6.34                                                                           Annuities

You are now 50 years old and plan to retire at age 65. You currently have a stock portfolio worth \$150,000, a 401(k) retirement
plan worth \$250,000 and a money market account worth \$50,000. Your stock portfolio is expected to provide you annual returns
of 12 percent, your 401(k) investment will earn you 9.5 percent annually, and the money market account earns 5.25 percent,
compounded monthly.

a. If you do not save another penny for the next 15 years, how much will you have from your current savings when you
retire at age 65?

Hint: Determine the future value of each account and then the grand total of all three.
Use the future value of a lump sum equation as well as the FV function: FV(rate,nper,pmt,pv,type)

Current age:                               Retirement age:

Stock Portfolio
Current value of stock portfolio:
Expected return on portfolio:
Years to retirement:

Expected value of portfolio at age 65 (formula):
Expected value of portfolio at age 65 (function):

401k Plan
Current value of 401k portfolio:
Expected return on portfolio:
Years to retirement:

Expected value of portfolio at age 65 (formula):
Expected value of portfolio at age 65 (function):

Money Market Account
Current value of stock portfolio:
Expected return on portfolio:
Years to retirement:
Frequency of compounding:

Expected value of portfolio at age 65 (formula):
Expected value of portfolio at age 65 (function):

Total of all three investments:

b. Assume you plan to invest \$12,000 every year in your 401K plan for the next 15 years starting next year. How much
will you have in total at retirement?

Annual investment in 401k plan:
Expected return on portfolio:

Value of 401k plan investments (formula):
Value of 401k plan investments (function):

Total investment amount at retirement:

c. Assume that you expect to live another 25 years after retirement (i.e., until age 90). You now take all of your
investments (use scenario from part b), and invest it in an account paying 8 percent. If you plan to use all your savings
starting a year from retirement, how much can you withdraw every year for the next 25 years and leave nothing in your

Hint: Solve for the PMT using the present value of an annuity equation and then use the PMT function:

Amount available at retirement:
Length of planned withdrawals (years):
Expected return on investments:

Amount of each yearly withdrawal (formula):
Amount of each yearly withdrawal (function):

d. If you wanted a perpetuity, how much will you be able to withdraw each year starting a year from now without

Type of payment:
Present value of perpetuity:
Expected return on investment:

Amount of each yearly withdrawal:
Problem 6.35                                                              Loan Amortization

Trevor Diaz is looking to purchase a Mercedes Benz SL600 Roadster which has an invoice price of \$121,737 and a total cost
of \$129,482. Trevor plans to put down \$20,000 and will pay the rest by taking on a 5.75 percent five-year loan from Bank of
America. What is the monthly payment on this auto loan? Prepare an amortization table using Excel.

a. What is the monthly payment on this auto loan?

Hint: Use the present value of an annuity equation to solve for the monthly payment and then use the PMT financial function
to solve: PMT(rate,nper,pv,fv,type).
The present value of the annuity is the total amount borrowed. Make sure that all cells are properly formatted.

Cost of new car:
Down payment:
Loan amount:
Interest rate on loan:
Term of loan (years):
Frequency of payment:

Monthly payment on loan:
Monthly payment on loan:

b. Prepare an amortization table using Excel.

Hint: Insert the proper equation in each column and copy down the appropriate number of periods.
Calculate total interest, principal, payments, and ending balance using the template below.

Loan amount:
Interest rate on loan:
Term of loan:
Frequency of payment:

Payment #      Payment        Interest     Principle      Balance

\$0.00
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

Totals:
Problem 6.36                                                                   Loan Amortization

The Sundarams are buying a new 3,500 square feet house in Muncie, Indiana and will borrow \$237,000 from Bank One at a rate of
6.375 percent for 15 years.

a. What is their monthly payment?
Hint: Use the present value of an annuity equation to solve for the monthly payment and then use the PMT financial function to solve:
PMT(rate,nper,pv,fv,type). The present value of the annuity is the total amount borrowed. Make sure that all cells are properly
formatted.

Home loan amount:
Interest rate on loan:
Term of loan (years):
Frequency of payment:

Monthly payment on loan (formula):
Monthly payment on loan (function):

Prepare an amortization table using Excel.

Hint: Insert the proper equation in each column and copy down the appropriate number of periods.
Calculate total interest, principal, payments, and ending balance using the template below.

Loan amount:
Interest rate on loan:
Term of loan (years):
Frequency of payment:

Payment #        Payment             Interest          Principle          Balance

\$0.00
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180

Totals:
Problem 6.37                                                  TVM Comprehensive

Assume you will start on a job as soon as you graduate. You plan to start saving for your retirement when you turn
25 years old. (Assume you are 21 years at the time of graduation. Everybody needs a break!) Currently you plan to
retire when you turn 65 years old. After retirement, you expect to live at least until you are 85. You wish to be able
to withdraw \$40,000 (in today’s dollars) every year from the time of your retirement until you are 85 years old (i.e.,
for a period of 20 years). You can invest, starting when you turn 25 years old, in a portfolio fund. The average
inflation rate is likely to be 5 percent.

a. Calculate the lump sum you need to have accumulated at age 65 to be able to draw the desired income.
Assume that your return on the portfolio investment is likely to be 10 percent.

Hint: First calculate the inflated value of the yearly retirement income desired for the first year in retirement. Then
use the present value of a growing annuity equation to solve for the lump sum required to generate the retirement
income stream. Make sure that all cells are properly formatted.

Current age:
Age when you begin to save for retirement:
Age at which you plan to retire:
Expected life span:

Desired yearly retirement income in today's dollars:
Average expected rate of inflation:

Desired first year retirement income adjusted for inflation:

Return on portfolio investment:

Amount needed at retirement to fund retirement income:

b. What is the dollar amount you need to invest every year, starting at age 26 and ending at age 65 (i.e., for
40 years) to reach the target lump sum at age 65?

Hint: Solve for the payment in the future value of an annuity equation and then solve using the PMT financial
function: PMT(rate,nper,pv,fv,type).

Amount needed at retirement to fund retirement income:
Number of years to save for retirement:

Annuity payment required (formula):
Annuity payment required (function):
percent per year.

Return on portfolio investment:

Amount needed at retirement to fund retirement income:

Number of years to save for retirement:

Annuity payment required (formula):
Annuity payment required (function):

Return on portfolio investment:

Amount needed at retirement to fund retirement income:

Number of years to save for retirement:

Annuity payment required (formula):
Annuity payment required (function):

d. Now assume you start investing for your retirement when you turn 30 years old and analyze the situation
under rate of return assumptions of (i) 8 percent, (ii) 10 percent, and (iii) 15 percent.

Return on portfolio investment:

Amount needed at retirement to fund retirement income:

Number of years to save for retirement:

Annuity payment required (formula):
Annuity payment required (function):

Return on portfolio investment:

Amount needed at retirement to fund retirement income:

Number of years to save for retirement:

Annuity payment required (formula):
Annuity payment required (function):
Return on portfolio investment:

Amount needed at retirement to fund retirement income:

Number of years to save for retirement:

Annuity payment required (formula):
Annuity payment required (function):

e. Repeat the analysis by assuming that you start investing only when you are 35 years old.

Return on portfolio investment:

Amount needed at retirement to fund retirement income:

Number of years to save for retirement:

Annuity payment required (formula):
Annuity payment required (function):

Return on portfolio investment:

Amount needed at retirement to fund retirement income:

Number of years to save for retirement:

Annuity payment required (formula):
Annuity payment required (function):

Return on portfolio investment:

Amount needed at retirement to fund retirement income:

Number of years to save for retirement:

Annuity payment required (formula):
Annuity payment required (function):

```
To top