Question

The goal of this project is to explore the topic of the time value of money. The project requires you to work in Excel with the provided spreadsheet. Be sure to fill in the yellow boxes in the Excel file for full credit. Make sure you reference cells in Excel where necessary and do not simply type in answers. Also, type up a one-page report in Word with a separate section for the introduction (description, variables, context, etc.), findings (results, plots, variable relationships, etc.), and conclusion (summary, lessons, etc.). Failure to follow all instructions will result in a deduction of points.

Time Value of Money

Part 1: Future Value

In this part, calculate the future values. Use the Excel FV function to compute the future values.

1. You have just made a $4,000 contribution to your individual retirement account. Assuming you earn an annual rate of return of 5 percent and make no additional contributions, what will your account be worth when you retire in 30 years? (7 Points)

2. Calculate the future values in the table below using the FV Excel function. (7 Points)

Present value	Years	Interest rate	Future value
$3,950	8	15%	?
$8,500	15	6%	?
$85,300	20	8%	?
$221,600	25	4%	?

Part 2: Present Value

In this part, calculate the present values. Use the Excel PV function to compute the present values.

3. You are committed to owning a $200,000 home. If you believe your mutual fund can achieve an annual return of 10 percent, and you want to buy the home in 15 years, how much must you invest today? (7 Points)

4. Calculate the present values in the table below using the PV Excel function. (7 Points)

Future value	Years	Interest rate	Present value
$19,500	16	6%	?
$47,390	9	12%	?
$312,200	13	11%	?
$629,380	25	13%	?

Part 3: Interest Rate

In this part, calculate the interest rates. Use the Excel Rate function to compute the interest rates.

5. In 2018, one of the first copper pennies struck at the Philadelphia mint in 1793 was sold for $300,000. What was the rate of return on this investment? (7 Points)

6. Calculate the interest rates in the table below using the Excel Rate function. (7 Points)

Present Value	Years	Future Value	Interest Rate
$850	10	$1,380	?
$900	14	$1,750	?
$16,000	25	$142,000	?
$70,500	16	$312,500	?

Part 4: Number of Periods

In this part, calculate the number of periods. Use the Excel Nper function to compute the number of periods.

7. You expect to receive $20,000 at graduation. You plan on investing it at 6 percent until you have $130,000. How long will you wait from now? (7 Points)

8. Calculate the number of periods in the table below using the Excel Nper function. (7 Points)

Present Value	Interest Rate	Future Value	Years
$190	6%	$870	?
$2,175	12%	$3,590	?
$47,850	11%	$326,890	?
$38,571	16%	$213,674	?

Part 5: Relation between Future Value and Number of Periods

In this part, you need to examine the relation between the future value and the number of periods.  

1. For a present value of $5,000, create a table that shows the FV at 0%, 5%, and 10% for 0, 2, 4, 6, 8, and 10 years. (7 Points)

10. In a single graph (scatter plot with straight lines and markers), graph the table above with years on the horizontal axis and FV on the vertical axis for each interest rate and be sure to label the y-axis, x-axis, chart title, and legend. (7 Points)

Answer 1

(a) Since I have contributed in the starting of the month, therefore Future value of $4000 @ 5% is calculated as follows:

= $4000*(1+0.05)³⁰

= $17287.77

(b) Calculation of Future value:

Present Value	Years	Interest rate	Future value
3950	8	15%	= 3950*(1+0.15)8              =12,083.14
8500	15	6%	= 8500*(1+0.06)15 = 20,370.74
85300	20	8%	= 85300*(1+0.08)20 = 397,579.64
221600	25	7%	= 221600*(1+0.07)25 = 1,202,719.07

(c) Since I have to contribution today, therefore Present value of $200000 @ 10% is calculated as follows:

= $200000/(1+0.10)¹⁴

= $52666.25

(d) Calculation of Present value:

Future Value	Years	Interest rate	Present value
19500	16	6%	= 19500/(1+0.06)16 = 7,676.10
47390	9	12%	= 47390/(1+0.12)9 = 17,089.31
312200	13	11%	= 312200*(1+0.11)13 = 80,395.95
629380	25	13%	= 629380*(1+0.13)25 = 29,645.03