Investing and Budgeting
Investment Growth and Your Budget
CIT105P Rubrics Assignment 4: Spreadsheet 2 Equations 6 Formatting Cells 2 Chart 2 Total 10
Larry, one of your coworkers, has been bragging about all the money he is making in the stock market and this has you thinking about your own financial future. As a first step to creating an investment plan you decide to create a budget that shows your income and expenses. Larry also said that, even at a modest rate of return, any investment will grow at an exponential rate and double within 10 years. Larry has a reputation for exaggerating the facts so you decide to also create a worksheet that will test his assertions.
Create a workbook with two worksheets.
- Use the figures below as a guide.
- Use formulas and cell references where appropriate.
- Use the worksheet "Investment Growth" to calculate the number of years it will take for an initial investment to double at 3, 6 and 10 %.
- Fill in cells F4 and F5 with the correct answer.
- For example, F6 contains 8 because it will take an initial investment of $1,000 8 years to double.
- (Note, the exact answer is slightly less than 7.5 years but you should round up to the nearest whole number.)
The following is a more detailed description of the assignment.
- Create an Excel workbook with two worksheets
- (be sure to delete any extra unused worksheets Excel creates for you).
Name your worksheets:
- The title "Personal Budget" should have a larger font than the other characters in your worksheet.
- Row 3 from A-G should be shaded and have a large bold line across the top border.
- The labels "Monthly" and "Annually" in row 3 are bold and right justified.
- The label "Expenses" and Income are bold and left justified.
- The labels in column B should be right justified.
- Numbers representing dollars should be formatted as currency.
- Cells G4:G10 should contain formulas (monthly values * 12). You should type the formula once in cell G4 and then fill down to enter the formula in the remaining cells.
- Cells G11 and G12 should contain the numbers given below.
- Cell H13 should contain the sum() function and contain the sum of cells G4 – G12.
- Cell H16 should contain the sum() function and contain the sum of cells G14 and G15.
- Cell H17 should contain a formula which is the difference between cells H16 and H13.
Above is the result you should be seeing in your budget worksheet with calculated values.
Above is the budget worksheet in "Formula View" showing the cells with formulas.
You can see that in this mode formatting is turned off so you don't see the dollar signs, for example.
Notice that the formulas in the G column could have been written each by hand but it was easier and more powerful to use the "Fill Down" method in Excel by writing the first cell formula then dragging the cell downward.
Notice also that the top two SUM() functions in column H are all additions. The SUM() function in H17 subtracts the 13th row from the 16th row by writing a negative sign (-) in front of "H13" cell label in the function.
Rename the default tab to your chosen name. Right click on the tab to get the option pop-up
Then right click on the "New Sheet" plus (+) sign for each new worksheet in this workbook.
Here is the result of setting up two worksheets in this workbook.
You always need to plan your worksheet and how you will put it together. Before you put in the formulas, you need to sketch it out with the labels and numeric given values.
Each of the given starting numeric values is simply typed in as they are. They will be the basis for the formulas in the calcuation cells. The text is formatted with various text formatting options in the top ribbon panels.
1 - We start the annual totals column by setting the month times 12 formula in the top row of the "Annually" column.
2 - Here is the result of multiplying the top monthly amount ($475) times 12 months (=$5,700).
3 - Now, by selecting that top cell, then grabbing the "handle" at the bottom right and dragging down, Excel will re-write the top formula to do the same thing in each row. (for the set of formulas look above on the "Formula View."). Note how the values are calculated automatically. This is what makes a spreadsheet such a useful and powerful tool.
If you wanted to test the result of various possible monthly expenses all you have to do is try to change any of them and the spreadsheet will do the calculation work for you (something which was done by hand years ago).
Here is the formula which totals (sums) the cells in column G for rows 4 though 12. You start by typing and "=" sign, then "sum(" then clicking on cell G4 and dragging to G12 (producing the blue highlight you see above) and then finally typing a closing parenthesis ")".
At this point you can see the worksheet add up (sum) the annual-total cells and display the totaled value in cell H13.
Notice, the formula in G14 is easier to just type in next.
The last formula we typed in was one to get the difference between total income and total expenses. In fact, we need to subtract the total expenses from the total income. We can do this with the SUM() formula by putting a negative sign (-) in front of the cell which is being subtracted. So H13 becomes -H13.
"Investment Growth" Worksheet
- The title "Investment Growth" should be underlined and have a larger font than the other characters in your worksheet.
- The other labels should be bold.
- The cells A5 – C5 and E3 –F3 should be formatted to have a wide border line along the bottom edge.
- For the first line in the table of numbers (row 6) enter the values given below.
- For the second line enter formulas to compute each value. For example,
- A7 should have the formula A6+1,
- B7 should have the formula C6*$C$3, and
- C7 should have the formula C6 + B7.
Note, you will want to use relative and absolute references where appropriate.
- To get the remaining rows in the table of numbers fill down row 7
Create a graph (chart) showing the growth rate of an investment earning 10% per year. Your graph (chart) should look like the graph in figure 2 below.
To do the Graph/Chart,
For an older version of Microsoft Office (less than 10):
click on the Graph Icon on the Tool Bar, and
use the default bar graph.
For Microsoft Office 10 and later,
click on the "Insert" tab on the Tool Bar,
then click within the "Charts" area on the "Column" chart icon (upper left).
- Change the value in cell C3 to 3%. Check the table you created in rows 7-26 to see how many years that it takes for the investment to double. Record this amount in cell F4. Repeat for 6% and record this value in cell F5.
- Change the value in C3 back to 10% so the worksheet you submit looks like the one below. (It will look the same except you will have the correct answers in cells F4 and F5.)
Figure 2. "Investment Growth" Worksheet
NOTES: Relative and Absolute References
At its basic the spreadsheet is powerful because it can automate hand calculations by writing formulas, not number values, into cells in the worksheet.
This can be extended by "filling down" which is a means to extend calculations to a set of cells which were first written into one cell.
The calculations depend on computing values of cells based on the values in other cells which are either in locations at specific relative positions (such as one right and one up) or at specific absolute positions (such as "C3") which holds a single value used across the cells in the spreadsheet.
In the formula you designate what kinds of operations are performed on the values in the cells, such as the values in C3 times the value in C6 to get the value to show in B7. To do that we start in B7 with a formula. We won't actually see the formula in the cell, just in the input area at the top, we will see the result of applying the formula. Any formula starts with the equal sign (=).
Once you've typed in the equal sign you type in the actual formula. The plus (+) and minus (-) signs do what your think. The times (multiply) sign is an asterisk (*) and the divide sign in a slash (/).
We also need some starting numbers for the formulas to work on. In the image below you see that we've filled in the 10-percent (10%) in cell C3. We've also typed in the starting balance in cell C6 and a zero for the year in A6.
We will start out by setting up values for the next for down, row 7.
Row 7 formulae
- Two formulae will have only relative labels, column A and column C in each row. They are very straighforward.
- For the year labels down column "A" we simply increment by one the value in the row above. We start with row 7 showing year number one (1). In A7 we type =A6+1
- For the new balance after a year's interest we go to cell C7 and type in =C6+B7
- NOTE: These are written like Bingo numbers based on the location in the grid defined by a letter acorss the top and a number down the side.
One formula, the one calculating interest will have a combination of relative (as in relative position) reference and an absolute reference (to the rate of interest). The rate of interest remains the same all the way down so it needs to stay the same. To do that it always references the same cell, in this case C3.
The other part is based on each previous year's balance and because the year changes so does the cell referred to.
- So we start with an equal sign (=)
- bring in the last year's balance which is in the row above and one column to the right. In B7 this means the balance will come from C6.
- put the multiply sign after this - the asterisk (*)
- now we need the cell position where the interest rate is stored which is in C3. But this will look a bit different because we intend to "fill down" the row of formulae.
- If we just write C3 the next row will become C4 then C5 and so forth, meaning it would lose the interest rate after the first row.
- So ... we can tell the spreadsheet to keep this part of the formula as is. To do this we just put dollar signs ($) in both parts of the grid address, like this $C$3
Click into the first cell you want to fill down on (A7) and drag the mouse across from A7 to C7 then release the mouse. You will have the 3-cell area outlined with a small handle on the bottom right corner. Drag the handle down as many rows as you need.
As you pull down you will get this series of results.
For B8 our balance value now come from C7 while the interest still comes from C3 (as $C$3)
For B9 our balance value now come from C8 while the interest still comes from C3 (as $C$3)
For B10 our balance value now come from C9 while the interest still comes from C3 (as $C$3)And all the way down to:
For B26 our balance value now come from C25 while the interest still comes from C3 (as $C$3)
Remember the types of references:
- Relative - Each new value source (cell) is in the same position relative to the current cell
- Absolute - always the same cell location
NOTES: Graph / Chart
To create a graph with these values is just a step or two away.
- Click on the Insert tab in the toolbox across the top
- Click on the Column type of chart (bar chart) located in the top left of the chart-type selections
- Excel produces the chart, as you see it below.