Calculations in Excel Project Assignment
Order ID:89JHGSJE83839 Style:APA/MLA/Harvard/Chicago Pages:5-10 Calculations in Excel Project Assignment
Question 1
Excel Project 2
Please read the information below carefully
This project has 2 parts:
Part 1: Excel Formulas: (4 Marks)
- Click hereto download your data.
- Once you completed all the calculations in Excel, save your workbook with an .xlsx extension and upload it below.
- If you forget to upload your excel file or accidentally submit the project or upload an incorrect file, you will receive 0 for the excel section of the project (you cannot send the excel file manually).
Part 2: Final Answer: (4 Marks)
Enter the id of the saving account (column A) that requests the minimum initial investment.
Project:
Assume that you are 18 years old and you would like to have $50000005000000 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.
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)
Instructions: