WORK:

Assume that you are 18 years old and you would like to have $5000000 in your account by the time you are 60 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.

QUESTION:

Which saving account would you choose to invest your money in? Type the ID (read the ID from Column A of your Excel file that corresponds to the minimum present value)

____________________________________________________________

PLEASE INCLUDE PICTURES OR EXPLANATIONS OF WHAT YOU ENTERED AND WHERE FOR EACH STEP, AS WELL AS WHAT YOUR ANSWER WAS FOR THE FINAL QUESTION SO THAT I CAN UNDERSTAND HOW TO DO THE SAME FOR FUTURE REFERENCE

TO VIEW THE IMAGE BELOW, PLEASE RIGHT CLICK AND CHOOSE "OPEN IMAGE IN NEW TAB"

Data Review View Help Search Share Comments File A = == D a le Wrap Text AutoSum Fill - ZY O Paste S E E Merge & Center - enter Insert Delete Format Ideas Conditional Format as Formatting Table Clear Home Insert Page Layout Formulas X Cut Calibri - 11 - A A L6 Copy - Format painter BI - - -A- Clipboard Font for id A Id Number of Years $, % 940.00 70 000 2 Number Sort & Find & Filter - Select Editing Alignment Styles Cells Ideas K F rate G nper Interest Compounded Converted H Future Value 5000000 Present Value Compounded 0.071 Monthly 0.072 Yearly 0.069 Yearly 0.075 Monthly 0.085 Yearly 0.074 Yearly 0.073 Daily 0.076 Daily 0.086 Monthly 0.064 Daily 0.083 Daily 0.068 Yearly 0.06 Monthly 0.081 Monthly 0.08 Yearly 0.061 Daily 0.079 Daily 0.062 Monthly 0.067 Daily 0.084 Monthly Sheet1 + @ -- + 100%