## 19 Nov MAT-144- Working with Formulas and Formatting: Identify the input variables in the symbolic formula and enter these into the blue-shaded cells in the Inputs section

please complete the template that is attached unless otherwise provided by your instructor. The spreadsheet provides practice working with Excel formulas and using formatting in Excel.

# 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!

## image1.png

,

## Sheet1

Legend | ||||||||

If a cell is shaded | You should | |||||||

Blue | Enter a text response | |||||||

Green | Enter a number | |||||||

Gold | Enter an Excel formula | |||||||

Any other color | Make no changes | |||||||

Compound Interest Formula (Example) | ||||||||

Symbolic Formula in Excel Format | Interpretation | |||||||

A(t) = P*(1+r/n)^(n*t) | If an initial amount P grows at an annual rate r with n compoundings per year, then the value A(t) of that amount after t years is given by the right-hand side of the formula. | |||||||

Sample problem: An initial balance of $4,000 grows at a rate of 12.3% compounded quarterly. What is the balance after 5 years? | ||||||||

Inputs: | Formatting instructions: | |||||||

P = ?? | r = ?? | n = ?? | t = ?? | Dollar amounts: format as Currency with 2 decimal places | ||||

$4,000.00 | 12.3% | 4 | 5 | Rate: format as Percentage with 1 decimal place | ||||

Application of the formula to the inputs: | A(t) = ?? | $7,330.39 | Periods/Years: format at Number with 0 decimal places | |||||

Future Value of Periodic Payments | ||||||||

Symbolic Formula in Excel Format | Interpretation | |||||||

A(t) = P*((1+r/n)^(n*t)-1)/(r/n) | If an amount P is contributed at the end of each of n periods per year and the balance earns interest at an annual percentage rate of r, the total amount A accrued after t years is given by the right-hand side of the formula. | |||||||

Sample problem: An amount of $250 is contributed at the end of each month, and the balance earns interest at an APR of 6.2%. What is the total amount accrued after 7 years? | ||||||||

Inputs: | Formatting instructions: | |||||||

Dollar amounts: format as Currency with 2 decimal places | ||||||||

Rate: format as Percentage with 1 decimal place | ||||||||

Application of the formula to the inputs: | Periods/Years: format at Number with 0 decimal places | |||||||

Loan Payment Formula | ||||||||

Symbolic Formula in Excel Format | Interpretation | |||||||

PMT = P*(r/n)/(1 – (1 + r/n)^(-n*t)) | The amount PMT that must be paid n times per year for t years to pay off a loan principal amount of P at an APR of r is given by the right-hand side of the formula. | |||||||

Sample problem: What is the monthly payment amount needed to pay off a loan principal amount of $10,000 over 4 years, given an interest rate of 7.3%? | ||||||||

Inputs: | Formatting instructions: | |||||||

Dollar amounts: format as Currency with 2 decimal places | ||||||||

Rate: format as Percentage with 1 decimal place | ||||||||

Application of the formula to the inputs: | Periods/Years: format at Number with 0 decimal places | |||||||

Inflation Rate Formula | ||||||||

Symbolic Formula in Excel Format | Interpretation | |||||||

IR = (new CPI – old CPI)/(old CPI) | Given an old CPI value at one point and a new CPI value n years later, the n-year inflation rate IR is given by the right-hand side of the formula. | |||||||

Sample problem: The CPI value in January 2009 was 211.143; the CPI value in January 2014 was 233.916. What was the 5-year inflation rate between January 2009 and January 2014? | ||||||||

Inputs: | Formatting instructions: | |||||||

CPI values: format as Number with 3 decimal places | ||||||||

Inflation rate: format as Percentage with 3 decimal places | ||||||||

Application of the formula to the inputs: | ||||||||

Topic 1 DQ 1 For this DQ, you will practice creating Excel formulas from symbolic formulas and formatting cells. The formulas here are all ones you will be using in this class. For each of the symbolic formulas given below in Excel format, you will identify inputs from an example statement and apply the formula in Excel, using appropriate cell references. Format cells with numeric values as indicated in the "Formatting instructions" sections. Pay attention to the legend at the right. Your entries should be text, numeric, or formulas as indicated by the color-coding in the spreadsheet.