Topic 1 DQ 1 – Extra Help and Instructions
I always give you the templates that I want you to use in the “Extra Help and Instructions” post. Please always use the template that I provide here, do not follow the instructions in the DQ post in LoudCloud.
You must use my template; you should save these locally, not on One Drive, and do not turn off any shared document features. I will ask you to redo any work where any locking, history, or conditional formatting is altered or turned off. If you use a Chromebook, which you really should not be using, then there might be issues. I will keep track of those who use Chromebooks and try to keep this in mind.
Here is a video from one of my office hours. Please read this entire post and watch the videos to assist you with this DQ. Please do this first and then try the DQ. If you still have questions, then please ask.
Here is a resource on the financial formulas that we will use in class and how to use Excel to use the formulas efficiently.
In the template for this DQ, the first section (Compound Interest Formula) is completed for you. You will fill in the other three sections (Future Value of Periodic Payments, Loan Payment Formula, and Inflation Rate Formula) similarly.
For each section, you are provided with a symbolic formula in Excel format, a description of how the formula is used, and a sample problem that gives you specific inputs into the formula. You will continue by completing the following five steps for each section:
1) Identify the input variables in the symbolic formula and enter these into the blue-shaded cells in the Inputs section. These are all the distinct variables to the right of the equals sign (=) in each formula, excluding constant numbers. For example, for the Compound Interest Formula, the symbolic formula is given by A(t) = P*(1+r/n)^(n*t), and in this case, there are 4 distinct variables to the right of the = sign: P, r, n, and t. (From the Interpretation for the formula, we see that P stands for the initial Principal, r stands for the annual rate, n stands for the number of compoundings per year, and t stands for the time in years; the value A(t) on the left-hand side is then the result of substituting specific values of P, r, n, and t into the formula.) So, we enter the strings “P ”, “r ”, “n”, and “t” into the Inputs section.
2) Identify the specific values of the inputs, based on the sample problem description, and enter these into the green-shaded cells under the corresponding input variables. In the sample problem for the Compound Interest Formula, the Principal value is $4,000, the annual rate is 12.3%, the number of compoundings per year is 4 (since we have quarterly compounding), and the time is 5 years. Consequently, we enter the values 4000 under the label “P”, 0.123 under the label “r”, 4 under the label “n”, and 5 under the label “t”.
3) Enter the left-hand side of the symbolic formula into the blue-shaded cell to the right of the cell containing the text “Application of the formula to the inputs:”. Here, you should enter the left-hand side of the symbolic formula; for the Compound Interest Formula, for example, the entry is “A(t)”.
4) Translate the symbolic formula into an Excel formula, referencing the appropriate green-shaded cells in the Inputs section. In the Compound Interest Formula example, the value of P is in cell A17, the value of r is in cell A18, the value of n is in cell C17, and the value of t is in cell D17; so, you would substitute cell A17 for P, A18 for r, and so on. This means that the symbolic formula =P*(1+r/n)^(n*t) becomes the Excel formula =A17*(1+B17/C17)^(C17*D17).
As a second example, consider E30. You need to use exactly the formulas provide, just replace the parameters with cell references. The given formula is:
P*((1+r/n)^(n*t)-1)/(r/n)
P is in cell A29, r in cell B29, n in cell C29, and t in cell D29 so your formula (in D30) must be
=A29*((1+B29/C29)^(C29*D29)-1)/(B29/C29)
In Excel,
- “=” indicate that you want Excel to evaluate a formula.
- =A1*A2 evaluates to (values in A1)×(value in A2), that is “*” means multiply.
- =A1^A2 evaluates to (value in A1)(value in A2), that is, “^” means raise to the power.
See the Excel Help for more.
5) Finally, format the green- and gold-shaded cells as described in the “Formatting instructions” cell for each section. Here, you can either use the options in the Number section of the Home tab in the top menu in Excel, or you can left-click on the bottom right “Number Format” link in the Number section to get to the Format Cells dialog, where you will be able to verify that your settings are correct. For example, if you select the rate cell (B17) in the Compound Interest Formula section, and then select the “Number Format link at the top, you will see in the Number tab of the Format Cells dialog that the “Percentage” option is selected from the Category list at the left and that the “Decimal places” is set to 1, as specified in the formatting instructions for this section. Note that it is not sufficient here, and will not be sufficient for your Major Assignments, for the numbers in the cells to “look right”; they must be explicitly set to a specific format, rather than left with a General format setting. For example, if you check the format for the cells for compounding frequency and time, you will see that these are explicitly set to Number with 0 decimal places.
In the same video from above formatting is discussed so that you know how to format values as currency or percentages with whatever number of decimal places are required.
Once you’ve completed the steps above, save your template file and attach it in a DQ response; reply directly to the DQ, not to one of the instructor’s posts. Great job, you’ve completed your first DQ for the class!
I hope this helps!
