Question

F17 A В D E 1 Id Number of Years Compounded Converted Interest Compounded rate 2 1 42 0.063 Yearly 3 2 0. 083 Daily 0.062 Monthly 0. 086 Monthly 0.084 Monthly 4 3 5 4 0.067 Daily 7 6 0.077 Yearly 7 9 0.069 Yearly 0.082 Daily 0.066 Yearly 10 9 11 10 0.085 Yearly 12 11 0.089 Monthly 0.087 Monthly 0.088 Monthly 13 12 14 13 15 14 16 15 0.071 Monthly 17 16 0.073 Daily 18 17 0.064 Daily 0.081 Monthly 0.08 Yearly 0.068 Yearly 19 18 19 20 21 20

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.

Answer 1

Formulas used: