Description
complete the file below
Unformatted Attachment Preview
Complete the following TVM tasks:
For each task, use the template provided to complete your calculations. Use cell references when possible (instead
1. You are planning to send your son to college (at SHSU) in 18 years. You believe that you will need $100,000 at
(a) If you have $20,000 to invest into a saving account today (lump sum deposit), what rate of return do you n
NOTE: you will make no intermediate payments to this account.
(b) Assume the savings account earns the annual rate you calculated in part a. How much do you need to inve
semi-annually (twice each year)? For the ‘Rate of Return (Rate)’ input, ensure you use a reference to cell Q
NOTE: compounding more often allows you to invest less than $20,000 today.
(c) Return to the assumptions (1) annual compounding on the savings account, and (2) the account earns the
Assume you would like to make annual deposits (payments) into the account INSTEAD of a lump sum depo
How much would you need to deposit each year to accumulate $100,000 in the savings account in 18 year
The value of the savings account today is zero (no funds will be added until the annual payment at the end
For the ‘Rate of Return (Rate)’ input, ensure you use a reference to cell Q8.
2. You have $5,000 to invest and need to choose between three savings accounts:
Account 1 pays 1.51% APR with quarterly compounding
Account 2 pays 1.49% APR with monthly compounding
Account 3 pays 1.48% APR with weekly compounding
(a) Fill in the table to the right:
NOTE: * use the EAR formula in column K
* use the EFFECT function in column L
* use the FV function in column M
Account
1
2
3
APR
1.51%
1.49%
1.48%
(b) Which account should you select (recall this is a SAVINGS account)
3. You have just retired (YAY!!). The current value of your retirement account is $75,000.
You would like to receive annual payments (at the end of each year) from this account for 5 years.
Assume the account pays 4.5% annually (APR).
(a) What is the amount of the equal, annual payment you will receive (assume the account balance in 5 years
(b) Demonstrate the value of the account each year. The account value should reflect the interest earned and
Year:
Account Value
0
1
This value should display as positive
2
3
4
5
This value should be zero
The annual values = previous year balance * (1 + rate) – annual paym
ences when possible (instead of hard coding data values)
hat you will need $100,000 at the time in order to pay for his college costs.
, what rate of return do you need to earn on the account in order to reach your goal?
ow much do you need to invest today if your account compounds
e you use a reference to cell Q8.
nd (2) the account earns the annual rate of return computed in part a.
INSTEAD of a lump sum deposit at time zero.
he savings account in 18 years.
he annual payment at the end of the first year).
1+
=
Frequency
Quarterly
Monthly
Weekly
M
Years
Investment
−1
Formula
Function
Function
EAR
EAR
FV in 5 years
Account:
Yikes .. that is
a very short
retirement!!
ccount for 5 years.
he account balance in 5 years is depleted to zero).
eflect the interest earned and the payment withdrawn each year.
5
$5,000
# Periods (Nper)
Payment (PMT)
Present Value (PV)
Future Value (FV)
Rate of Return (Rate) =
Rate of Return (Rate)
# Periods (Nper)
Payment (PMT)
Future Value (FV)
Present Value (PV) =
** Ensure you reference cell Q8 AND convert the rate from part a into
** Ensure you reference cell Q4 and convert the number of years into
Rate of Return (Rate)
# Periods (Nper)
Present Value (PV)
Future Value (FV)
Payment (PMT) =
** Ensure you reference cell Q8.
** The PV = 0 since today the account has no value
** The payment should be negative, since it represents an outflow ea
** Note: Function calls should be entered in row 29 and copied to rows 30:31. Do NOT code functions to spill val
Rate of Return (Rate)
# Periods (Nper)
Present Value (PV)
Future Value (FV)
Payment (PMT) =
** The PV should be negative, since it represents an investment into t
93992
Purchase answer to see full
attachment
