Question
Project: Assume that you are 18 years old and you would like to have $10000001000000 in your account by the time you are 6060 years
Project:
Assume that you are 18 years old and you would like to have $10000001000000 in your account by the time you are 6060 years old. A list of various savings accounts was compiled in your Excel file. Calculate the required initial (present value) deposit to the savings accounts based on the interest rates in column C compounded based on the values in column D. Choose the savings account id (column A) that requires the minimum initial investment. When you reference the future value in the PV formula, make sure you add a negative sign before referencing the cell containing the future value (see the example below). Using this method all values in column I will be positive, which makes it easy to find the minimum value and corresponding saving account ID.
Steps:
1- Convert the words "Yearly", "Monthly", "Daily" to numbers 1, 12, 365 respectively using a Nested IF formula and place the results in column E
2- Based on the column C and column E evaluate the interest rate in column F
3- Based on the column B and column E evaluate the nper (total number of periods) in column G
Note:
1- The values in column C are percents (You do NOT need to divide it by 100).
2- You must use the PV formula (If you are not familiar with the formula read the short description below)
3- With the exception of number 0, the formulas that you enter in column I must NOT include ANY numbers. Use absolute/relative references when necessary.
4- Except the cells that you must change, the rest of the cells are protected
PV Formula:
Syntax: =PV (rate, nper, pmt, [fv], [type])
rate: The interest rate per period
nper: The total number of periods
pmt: The payment made each period
fv: The future value
type: 0=end of period, 1=beginning of period. Enter 0 for this project
Example:
What investment today grows to $100 at the end of 10 years with a constant interest rate of 5% compounded monthly?
rate: 0.05/12=0.00417
nper: 10 12=120
pmt: 0
fv: 100
type: 0
=PV(0.00417,120, 0, -100, 0)
Why do we add a negative sign before the future value?
We would like to obtain a positive value as the present value, that is the reason we add a negative before the future value.
Just need the function for each row
F17 B D. Id 1 Number of Years Interest Compounded Converted rate 42 CON CON O 01 FO0 11 Compounded 0.063 Yearly 0.083 Daily 0.062 Monthly 0.086 Monthly 0.084 Monthly 0.067 Daily 0.077 Yearly 0.069 Yearly 0.082 Daily 0.066 Yearly 0.085 Yearly 0.089 Monthly 0.087 Monthly 0.088 Monthly 0.071 Monthly 0.073 Daily 0.064 Daily 0.081 Monthly 0.08 Yearly 0.068 Yearly 12 13 14 15 16 17 18 18 19 20 21 nper Present Value Future Value 1000000 F17 B D. Id 1 Number of Years Interest Compounded Converted rate 42 CON CON O 01 FO0 11 Compounded 0.063 Yearly 0.083 Daily 0.062 Monthly 0.086 Monthly 0.084 Monthly 0.067 Daily 0.077 Yearly 0.069 Yearly 0.082 Daily 0.066 Yearly 0.085 Yearly 0.089 Monthly 0.087 Monthly 0.088 Monthly 0.071 Monthly 0.073 Daily 0.064 Daily 0.081 Monthly 0.08 Yearly 0.068 Yearly 12 13 14 15 16 17 18 18 19 20 21 nper Present Value Future Value 1000000Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started