When carrying out row operations in Google Sheets, you may end up with cells whose entries are very long (repeating?) decimals. If you round these numbers, you are potentially changing the solution to the problem you are doing. It would be nice to be able to show the numbers in the lower right hand portion of the picture above as fractions.
Luckily this is possible by formatting those cells with a custom format. To do this, drag select the cells you want to format this way. With the cells selected, go to the Format menu and choose Number.
At the bottom of the this menu is submenu called More Formats. When you choose that option, another menu will appear.
Choose Custom Number Format. This will open a box in which you can customize how the number appears in the cell.
Enter the characters shown above and then press Apply. Make sure the characters start with an underscore ( _ ) and contain a space between the # and the ?.
All of the numbers in the selection will now be written as fractions.
A quick spot check show that cell D8 had contained the decimal -1.333333333. With the new formatting, it appears as the fraction -4/3.
By using the fraction in your solution, you can avoid rounding until the very last step of the calculation where it is more appropriate to round.
Spreadsheets have several built in functions for working with compound interest and annuities. To use these functions, we’ll start with a standard sheet.
This worksheet contains the variables used throughout Chapter 8. These variables correspond to these letter used in the text.
- Number of periods is n
- Annual interest rate is r
- Payment is R
- Present value is P
- Future value is A
- Periods per year is m
Values given in a problem will be entered in column B. Values calculated by the spreadsheet will be entered in column C. We will also assume that amounts paid out are negative and amounts received are positive.
Google Sheets has several built in functions for working with annuities. To use these functions, we’ll start with a standard Sheets worksheet.
This worksheet contains the variables used throughout Chapter 5. Values given in a problem will be entered in column B. Values calculated by Sheets will be entered in column C. We will also assume that amounts paid out are negative and amounts received are positive.
We’ll modify the worksheet shown above. This will allow us to use Sheets to calculate the different amounts in the annuity formula,
This is done using two functions in Sheets, the FV (future value) function and the PV (present value) function. Annuities have a regular payment into or out of the account. If the payment is made at the end of the compounding period, the annuity is called an ordinary annuity. Payments are made at the beginning of the compounding period for an annuity due. In Sheets, amounts that you pay out are considered negative numbers and amount you receive are positive amounts.
Find the Future Value of the Annuity
An investor deposits $500 in a simple annuity at the end of each six-month payment period. This annuity earns 10% per year, compounded semiannually.
a. Find the future value if payments are made for three years.
Solution Since the investor is paying $500 into the annuity, the payment must be entered as a negative number in Sheets.
1. Start from the basic worksheet and enter the values shown below.
2. Click in cell C6. Type =FV( in the cell. As soon as you type the parentheses, Sheets recognizes what you are trying to do.
3. Notice that the word rate is highlighted. This indicates that you need to either type in the rate or the cell reference where the rate is located. The rate for the command is actually the interest rate per period. The annual interest rate is in cell B3 and the number of periods per year is in cell B7. We need to get the interest rate per period by typing B3/B7. You can also click in cell B3, type a /, and then click the cursor in cell B7. Now type a comma.
4. The number of periods is in cell B2. Type B2 or click the cursor in that cell. To continue to the next input, type a comma.
5. The payment is located in cell B4. The value is negative since the money is paid out. Type B4 or choose cell B4 followed by a comma.
6. Since we deposit nothing into the account initially, the present value is zero. Enter B5 or select cell B5 followed by a comma and a parentheses. Press enter on the keyboard to calculate the future value.
The future value of the annuity is $3400.96. There is an optional argument for the FV function. This argument indicates whether the annuity is an ordinary annuity (use 0 for the argument) or an annuity due (use 1 for the argument).
Ordinary Annuity: FV(B3/B7,B2,B4,B5,0)
Annuity Due: FV(B3/B7,B2,B4,B5,1)
If the last argument is not supplied, the annuity is assumed to be an ordinary annuity.
b. Find the future value if payments are made for 30 years.
Solution Thirty years of semiannual payments corresponds to n = 60. We need to make this change in the FV function, and solve for future value again. Change the value in cell B2 to 60.
The future value is now $176,791.86.
Suppose you want to generate a table of values from a formula…perhaps to help evaluate a limit. Google Sheets (or any spreadsheet) can quickly generate the values.
Make a table of values for f (x) = 7x2 + 1 for x = 0, 0.9, 0.99, 0.999, 0.9999.
1. Open Google Sheets.
2. In the first row place the name of the input variable and the function’s name. We will put the values of the independent variable the first column (A) and the values of the dependent variable in the second column (B).
3. In cells A2 through A6, put the values of the independent variable for the table.
4. Click the mouse in cell B2. Enter the formula in that cell by typing =7*A2^2+1.
5. Press Enter to evaluate the formula in cell B2.
6. Click the mouse in cell B2. You will see a black outline around the cell. Use the mouse to grab the fill handle in the lower right hand corner of the black outline.
7. Click on this handle. While holding the left mouse button down, drag the mouse to cell B6.
8. When the mouse button is released, cells B2 through B6 are filled with the function values corresponding to the inputs in column A.