Excel Formulas Halfwit to Hero

The Tutorials
Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 
Chapter 6 Chapter 7 Chapter 8 Chapter 9 Chapter 10

Chapter 6

Calculations with Money

In this chapter you explore Excel's financial functions. They're the perfect tool for examining hypothetical scenarios--for example, comparing investments you might want to make or planning how quickly you can pay back a loan.

Exercise #1: The imaginary investment

A great-great aunt has bequeathed you some money. With Excel's help, you can see its future.

  1. Begin by opening the Imaginary investment.xlsx spreadsheet.

  2. Your job is to uses the FV function to calculate the future worth of your money. Cells B3, B4, and B5 have the data you need for the starting value (PV), the time invested (number of periods), and the interest rate. But what should you use for the payment parameter?

    The payment is 0 because you aren't making regular contributions to top up your investment.

  3. Now write a formula in cell B8 that uses FV. Assume for now that you have a yearly interest rate (not a monthly one).

  4. Now let's check your answer. If you see $95,920.72, your formula is perfect. But it the number is displayed in red type and in brackets like ($95,920.72) it's a negative number, which is wrong. What mistake causes this problem?

    Instead of giving the bank $68,000, you accidentally borrowed $68,000, and now the bank wants its money back. To fix the problem, edit your formula to use a negative starting value (-B3 instead of B3). This tells FV you are giving the bank the money to invest it:

    =FV(B5, B4, 0, -B3)

  5. Right now the FV function uses an annual interest rate that pays interest once per year. But if your investment is compounded monthly, what do you need to change in the formula?

    You need to divide the rate by 12 and multiply the number of years by 12.

  6. Modify the formula to use monthly formatting.

    Here's the revised formula:

    =FV(B5/12, B4*12, 0, -B3)

    You'll notice now that you earn more interest, bringing your final balance to $96,447.45.

Exercise #2: Make me a millionaire

In this exercise you'll use compare different investing choices and see if they can take you to your ultimate investing goal: to save up a cool $1,000,000.

  1. Begin by opening the Make me a millionaire.xlsx spreadsheet. It has the empty skeleton that you'll use to create a two-variable table.

  2. Each cell in the table performs a calculation with the PV function. They payment is always 0 (you're not going to contribute any extra payments) and the FV is always 1000000 (one million). But the rate depends on the column and the number of investment years depends on the row. To make sure you understand this system, find cell C8. What values should it use for the rate and the investment years?

    The rate is 1.5% (see the heading at the top of column C) and the investment years is 4 (see the heading at the left of row 8).

  3. Now move to cell B5. This is where you're going to write your first formula. Your going to use cell references, so take a moment to identify all the information you need on the worksheet.

    You should find the rate in B4, the number of years in A5, and the $1,000,000 investment target in J1.

  4. Using the cells you've identified, write a formula that uses the PV function. Assume that you're using an annual rate and the payment period is once per year. (That means the number of periods will be the same as the number of years, and there's no need to multiply or divide by 12.) You'll know you've done it right if you get a result of -$990,099.

    Here's the finished formula:

    =PV(B4, A5, 0, J1)

    The result is negative because it's money you need to pay into your account at the start of your investment.

  5. Now you need to fill in the rest of the table, which has a whopping 340 cells. You could write each formula by hand, but Excel's helpful formula copying feature will help you out immensely. But before you can use it, you need to change your references. Otherwise, when you copy the formula to another spot, Excel will shift all the references and some will end up pointing to the wrong places.

     

    There are three cell references in formula you just wrote, and they all need to be changed. First, start with the easiest one--the reference to the target amount of money (J1). When you copy your formula, this reference should stay exactly the same. How can you change the relative reference J1 to a fixed reference that Excel will never change?

    You make a fixed reference by adding dollar signs, so J1 becomes $J$1 instead.

  6. Next, you need to change the cell reference that refers to the interest rate (B4). When you copy this formula across the table, Excel should be able to change the column letter so it always points to the right interest rate (for example, C4, D4, E4, and so on). But Excel shouldn't be allowed to change the row number when you go down the table (because all the rate information is in row 4). To enforce this rule, you must change B4 into a mixed reference that uses one $ sign. What does it look like?

    The mixed reference B$4 lets Excel change the column letter but not the row number.

  7. Finally, you need to change the cell reference that refers to the number of years invested (A5). Here you have the reverse problem. You need to let Excel change the row number when you copy down the table (so it gets the right year information), but make sure Excel doesn't change the column number when you copy across the table (because all the year information is in column A). What should your mixed reference look like?

    The mixed reference $A5 lets Excel change the row number but not the column letter.

  8. Now that you've replaced these three references, you should have a ready-to-copy formula.

    It should look like this:

    =PV(B$4, $A5, 0, $J$1)

  9. Press Ctrl+C to copy the cell B5.

  10. You could paste copies in one cell at a time, in one column or row at a time, or even in the entire table at once. You just need to select the right region. Hold down shift while you move down or across the table until all the cells you want are highlighted. (It doesn't matter if you also highlight the cell that has the formula you're copying, B5, because Excel understands what you're trying to do.)

  11. Press Ctrl+V to paste formula copies in the highlighted cells. If you still have some empty cells in the table, copy one of the formulas, and paste it in an area with empty cells. Repeat this step until your table is full. For a bonus, try making a table that compares two different bits of financial data. For example, assume the interest rate is limited to 5%, and you want to see what happens if you change the investment years and the monthly payments. What changes would you make to the structure of the table?

    You need to replace the column headers in cells B4 to Q4 with different monthly payment options (say, from 0 to $1000, in $50 increments). The year information in column A stays the same.

  12. What changes do you need to make to the formulas? Remember, when you make monthly payments, you need to use a monthly payment period.

    To switch to a monthly payment period, you need to divide the rate by 12 and multiply the years by 12. Your finished PV function would look something like this:

    =PV(5%/12, $A5*12, B$4, -$J$1)

    You can see this example in the Bonus worksheet, and the fully filled out years-and-rates table in the Solution worksheet.

PREFER PAPER? If you'd rather do your work offline, you can print this page. Use this button to expand all the hidden sections and then print the exercises for this chapter: