Excel Activity: Time value of money

The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round intermediate calculations. Enter your answers as positive values.

Download spreadsheet Time value of money-43fdc7.xlsx

a. Find the FV of $1,000 invested to earn 12% after 5 years. Round your answer to the nearest cent.

$ fill in the blank 2

b. What is the investment's FV at rates of 0%, 6%, and 25% after 0, 1, 2, 3, 4, and 5 years? Round your answers to the nearest cent.

Year	Interest Rate
	0%	6%	25%
0	$ fill in the blank 3	$ fill in the blank 4	$ fill in the blank 5
1	$ fill in the blank 6	$ fill in the blank 7	$ fill in the blank 8
2	$ fill in the blank 9	$ fill in the blank 10	$ fill in the blank 11
3	$ fill in the blank 12	$ fill in the blank 13	$ fill in the blank 14
4	$ fill in the blank 15	$ fill in the blank 16	$ fill in the blank 17
5	$ fill in the blank 18	$ fill in the blank 19	$ fill in the blank 20

Choose the correct graph of future value as a function of time and rate. Note: blue line is for 0%, orange line is for 6%, and grey line is for 25%.

The correct graph is

graph Agraph Bgraph Cgraph D

.

A.
B.
C.
D.

c. Find the PV of $1,000 due in 5 years if the discount rate is 12%. Round your answer to the nearest cent.

$ fill in the blank 22

d. A security has a cost of $1,000 and will return $4,000 after 5 years. What rate of return does the security provide? Round your answer to two decimal places.

fill in the blank 23 %

e. Suppose California's population is 35.1 million people, and its population is expected to grow by 2% annually. How long will it take for the population to double? Round your answer to the nearest whole number.

fill in the blank 24 years

f. Find the PV of an ordinary annuity that pays $1,000 each of the next 5 years if the interest rate is 14%. Then find the FV of that same annuity. Round your answers to the nearest cent.

PV of ordinary annuity: $ fill in the blank 25 FV of ordinary annuity: $ fill in the blank 26

g. How will the PV and FV of the annuity in part f change if it is an annuity due rather than an ordinary annuity? Round your answers to the nearest cent.

PV of annuity due: $ fill in the blank 27 FV of annuity due: $ fill in the blank 28

h. What will the FV and the PV for parts a and c be if the interest rate is 12% with semiannual compounding rather than 12% with annual compounding? Round your answers to the nearest cent.

FV with semiannual compounding: $ fill in the blank 29 PV with semiannual compounding: $ fill in the blank 30

i. Find the annual payments for an ordinary annuity and an annuity due for 10 years with a PV of $1,000 and an interest rate of 11%. Round your answers to the nearest cent.

Annual payment for ordinary annuity:	$ fill in the blank 31
Annual payment for annuity due:	$ fill in the blank 32

j. Find the PV and the FV of an investment that makes the following end-of-year payments. The interest rate is 11%.

Year	Payment
1	$200
2	$400
3	$700

Round your answers to the nearest cent.

PV of investment: $ fill in the blank 33 FV of investment: $ fill in the blank 34

k. Five banks offer nominal rates of 10% on deposits, but A pays interest annually, B pays semiannually, C pays quarterly, D pays monthly, and E pays daily. Assume 365 days in a year.

1. What effective annual rate does each bank pay? If you deposit $3,000 in each bank today, how much will you have in each bank at the end of 1 year? 2 years? Round your answers to two decimal places.

   	A		B		C		D		E
   EAR	fill in the blank 35	%	fill in the blank 36	%	fill in the blank 37	%	fill in the blank 38	%	fill in the blank 39	%
   FV after 1 year	$ fill in the blank 40		$ fill in the blank 41		$ fill in the blank 42		$ fill in the blank 43		$ fill in the blank 44
   FV after 2 years	$ fill in the blank 45		$ fill in the blank 46		$ fill in the blank 47		$ fill in the blank 48		$ fill in the blank 49

2. If the TVM is the only consideration, what nominal rate will cause all of the banks to provide the same effective annual rate as Bank A? Round your answers to two decimal places.

   	B		C		D		E
   Nominal rate	fill in the blank 50	%	fill in the blank 51	%	fill in the blank 52	%	fill in the blank 53	%

3. Suppose you don't have the $3,000 but need it at the end of 1 year. You plan to make a series of deposits annually for A, semiannually for B, quarterly for C, monthly for D, and daily for E with payments beginning today. How large must the payments be to each bank? Round your answers to the nearest cent.

   	A		B		C		D		E
   Payment	$ fill in the blank 54		$ fill in the blank 55		$ fill in the blank 56		$ fill in the blank 57		$ fill in the blank 58

4. Even if the five banks provided the same effective annual rate, would a rational investor be indifferent between the banks?

   It is more likely that an investor would prefer the bank that compounded

   moreless

   frequently.

l. Suppose you borrow $14,000. The interest rate is 11%, and it requires 4 equal end-of-year payments. Set up an amortization schedule that shows the annual payments, interest payments, principal repayments, and beginning and ending loan balances. Round your answers to the nearest cent. If your answer is zero, enter "0".

	Beginning			Repayment	Ending
Year	Balance	Payment	Interest	of Principal	Balance
1	$ fill in the blank 60	$ fill in the blank 61	$ fill in the blank 62	$ fill in the blank 63	$ fill in the blank 64
2	$ fill in the blank 65	$ fill in the blank 66	$ fill in the blank 67	$ fill in the blank 68	$ fill in the blank 69
3	$ fill in the blank 70	$ fill in the blank 71	$ fill in the blank 72	$ fill in the blank 73	$ fill in the blank 74
4	$ fill in the blank 75	$ fill in the blank 76	$ fill in the blank 77	$ fill in the blank 78	$ fill in the blank 79

Choose the correct graph that shows how the payments are divided between interest and principal repayment over time.

The correct graph is

graph Agraph Bgraph Cgraph D

.

A.		B.
C.		D.