Chapter 7, The Time Value of Money Video Solutions, Financial Analysis with Microsoft Excel

Problem 1

Upon starting your new job after college, you've been confronted with selecting the investments for your $401(\mathrm{k})$ retirement plan. You have four choices for investing your money:
- A money market fund that has historically returned about $1 \%$ per year.
- A long-term bond fund that has earned an average annual return of $4.5 \%$.
- A conservative common-stock fund that has earned $6.5 \%$ per year.
- An aggressive common-stock fund that has earned $11 \%$ per year.
a. If you were to contribute $$\$ 5,500$$ per year for the next 35 years, how much would you accumulate in each of the above funds?
b. Now, change your worksheet so that it allows for less than annual investments (monthly, biweekly, etc.). Your total annual investment will remain unchanged, but it may be made in smaller, but more frequent, amounts.
c. Set up a scenario analysis that shows your accumulated value in each fund if you were to invest quarterly, monthly, biweekly, and weekly. Create a scenario summary of your results.
d. What relationship do you notice between the frequency of investment and the future value? Create a Column chart of the results that more clearly shows the outcome from more frequently investing.

Check back soon!

Problem 2

Given the following set of cash flows:
$$
\begin{array}{|c|r|}
\hline \text { Period } & \text { Cash Flow } \\
\hline 1 & \$ 45,000 \\
\hline 2 & 40,000 \\
\hline 3 & 35,000 \\
\hline 4 & 30,000 \\
\hline 5 & 25,000 \\
\hline
\end{array}
$$
b. Now, suppose that you are offered another investment that is identical, except that the cash flows are reversed (i.e., cash flow 1 is 25,000 , cash flow 2 is 30,000 , etc.). Is this worth more, or less, than the original investment? Why?
c. If you paid $$\$ 130,000$$ for the original investment, what average annual rate of return would you earn? What return would you earn on the reversed cash flows?
d. Still assuming that your required return is $7 \%$, would you be willing to purchase either of these investments? Explain why, or why not.

Check back soon!

03:38

Problem 3

Your five-year-old daughter has just announced that she would like to attend college. The College Board has reported that the average cost of tuition, room, board, and other expenses at public four-year colleges is $$\$ 18,391$$ in the 2013-2014 academic year. ${ }^{11}$ The cost has risen $3.2 \%$ over the last year. You believe that you can earn a rate of $8 \%$ on investments to meet this goal.
a. If costs continue to rise at $3.2 \%$ per year, how much will it cost for the first year of tuition in 13 years?
b. Assuming that you plan to have enough money saved in 13 years to cover all four years of college costs, how much will you need to have accumulated by that time? Note that the tuition, room, and board is a graduated annuity growing $3.2 \%$ per year, and assume that you will pay all costs at the beginning of each year.
c. If you were to invest a lump sum today in hopes of covering your daughter's college costs, how much would you have to invest?
d. If you decided to invest annually instead, how much would you have to invest each year? What if you make investments monthly?
e. You just learned of a $$\$ 10,000$$ inheritance and plan to invest it in your daughter's college fund. Given this new source of funds, how much will you now have to invest each year?

Kayleah Tsai

Numerade Educator

Problem 4

You have decided to invest in a small commercial office building that has one tenant. The tenant has a lease that calls for annual rent payments of $$\$ 20,000$$ per year for the next three years. However, after that lease expires you expect to be able to increase the rent by $5 \%$ per year for the next seven years. You plan to sell the building for $$\$ 300,000$$ ten years from now.
a. Create a table showing the projected cash flows for this investment assuming that the next lease payment will be made in one year.
b. Assuming that you need to earn $11 \%$ per year on this investment, what is the maximum price that you would be willing to pay for the building today? Use the NPV function.
c. Notice that the cash flow stream starts out as a three-year regular annuity, but it then changes into a seven-year graduated annuity plus a lump sum in year 10 . Use the principal of value additivity to calculate the present value of the cash flows.
d. Suppose that the current owner of the building is asking $$\$ 200,000$$ for the building. If you paid this price, what annual rate of return would you earn? Should you buy the building at this price?

Check back soon!

04:42

Problem 5

Congratulations! You have just won the State Lottery. The lottery prize was advertised as an annuitized $$\$ 105$$ million paid out in 30 equal annual payments beginning immediately. The annual payment is determined by dividing the advertised prize by the number of payments. You now have up to 60 days to determine whether to take the cash prize or the annuity.
a. If you were to choose the annuitized prize, how much would you receive each year?
b. The cash prize is the present value of the annuity payments. If interest rates are $4.5 \%$, how much will you receive if you choose the cash option?
c. Now suppose that, as many lotteries do, the annuitized cash flows will grow by $3 \%$ per year to keep up with inflation, but they still add up to $$\$ 105$$ million. In this case, the first payment will be $$\$ 2,207,022.23$$ today. If you took the cash prize instead, how much would you receive (before taxes)?

Charles Carter

Numerade Educator