Software optimize

10 steps to creating a multi-scenario savings calculator in Excel

Regardless of what you're saving up for, a worksheet with relevant calculations will help you stay on top of your plan. Walk through this technique to create your own calculator -- or download our ready-made tool.

Saving money is a vital part of any sound financial plan. Regardless of what you're saving for, you work with the same few pieces of financial data: a monthly investment or a goal, an interest rate, and the number of months or years you intend to save. An Excel sheet can help you turn all that data into information you can use to fine-tune your savings plan. Discipline might be the key, but knowledge helps you make the best decisions. It's fairly easy to build a multi-scenario savings calculator in Excel, and we're going to show you how it's done. (If you prefer, you can simply download our pre-built version of this calculator.)

1: Enter the labels

To get started, enter a few labels. Using Figure A (the complete calculator) as a guide, enter the following labels:

B1: Savings Calculator

B3: Future Value of Monthly Investment

B4; B10; B15: What is the annual interest rate?

B5; B11; B16: How many years will you save?

B6: How much do you intend to save each month?

B7; B17: How much is your deposit (a deposit isn't required)?

B8: The future value of your monthly investment is:

B9: Future Value of One-time Investment

B12: How much is your one-time payment?

B13: The future value of your one-time investment is:

B14: Monthly Investment Required to Meet Savings Goal

B18: What is your savings goal?

B19: The monthly payment to reach your savings goal is:

Figure A

Enter informational labels and prompts for users to enter financial data.

2: Enter the formulas

There are three formulas in this savings calculator:

C8: =IF(ISERROR(FV(C4/12,C5*12,-C6,-C7)),"Error",FV(C4/12,C5*12,-C6,-C7))

C13: =IF(ISERROR(FV(C10,C11,0,-C12)),"Error",FV(C10,C11,0,-C12))

C19: =IF(ISERROR(PMT(C15/12,C16*12,-C17,C18)),0,PMT(C15/12,C16*12,-C17,C18))

Excel's FV() function returns the future value of an investment. The PMT() function calculates the monthly payment required to meet a specific goal. (You can also use this function to determine payments on a loan.)

The =IF() and ISERROR() functions work together to prevent Excel error values when the input cells are blank. If the formula returns an error, the formula displays the string "Error." Admittedly, the string "Error" isn't much more helpful than an Excel error value. The point is control -- when you create your own calculator, you can display the error messages you think most appropriate for your users. If you're using Excel 2007 or 2010, use IFERROR().

3: Format the label cells

Formatting should help users, not distract them from the calculator's purpose. Several cells require formatting. Fortunately, you can assign formats to multi-cell selections as follows:

  1. Hold down the [Ctrl] key while you click noncontiguous cells with a similar format. Or hold down the [Shift] key while you select the first and last cells in a contiguous block.
  2. Right-click the selection and choose Format Cells from the resulting context menu.
  3. Apply the format as you normally would to a single cell.

Using the above instructions and Table A, apply the appropriate formatting.

Table A

Label formats

4: Format the formula cells

There are only three formula cells and you'll apply the same formats to all three, so this step is simple:

  1. Select cells C8, C13, and C19 (#3).
  2. Right-click the selection and choose Format Cells.
  3. Click the Number tab.
  4. Choose Currency from the Category list.
  5. Specify 2 decimal places.
  6. Select red with no parentheses for the negative number format. This isn't critical, but the parentheses in the results might confuse users.
  7. Click OK.

With the selection still intact, apply the bold font attribute.

5: Format the input cells

Each savings scenario has its own set of input cells, but you can format groups of them at the same time using a multi-cell selection (#3). Refer to Table B to apply the appropriate formats.

Table B

Format input cells

6: Add validation to input cells

You can eliminate user frustration by applying validation rules to the input cells. Otherwise, users may enter inappropriate values. The validation rule used in the downloadable workbook restricts input to any valid number. To apply this validation rule, do the following:

  1. Select cell C4.
  2. Choose Validation from the Data menu. In Excel 2007 and 2010, click the Data tab | Data Validation.
  3. On the Settings tab, choose Custom from the Allow control.
  4. In the Formula control, enter =ISNUMBER(C4). Do not make the cell reference absolute! You'll see why in a minute.
  5. Click the Input message and enter the following settings:

Title: Input Value

Message: Enter a number.

  1. Click the Error Alert tab and enter the following settings:

Title: Input Value

Message: Enter a number.

Copy the validation rule to the other input cells as follows:

  1. Select C4.
  2. Choose Copy from the Edit menu. In Excel 2007 and 2010, click the Home tab | Copy (in the Clipboard group).
  3. Hold down the [Ctrl] key and click cells C5, C6, C7, C10, C11, C12, C15, C16, C17, and C18.
  4. Choose Paste Special from the Edit menu. In Excel 2007 and 2010, click the Home tab | Paste | Paste Special.
  5. Click the Validation option in the Paste section.
  6. Click OK.

The relative cell address in the ISNUMBER() function lets you copy the validation rule to other input cells.

7: Add a bit of embellishment

While not necessary, you might want to spiff up things just a bit. The downloadable workbook contains a logo, and the back color of the three title cells matches the blue in the logo. In addition, borders outline the calculator area (B1:C10). The row height is a bit larger than the default. Make sure column B is wide enough to display the questions.

To turn off the display of a few features users won't need, do the following in Excel 2003:

  1. Choose Options from the Tools menu.
  2. Click the View tab.
  3. Uncheck the Gridlines and Row & Column Headers options in the Window Options section.
  4. Uncheck Formula Bar in the Show section.
  5. Click OK.

In Excel 2007 and 2010, click the View tab and uncheck Formula Bar, Gridlines, and Headings in the Show group.

8: Unlock the input cells

I recommend that you protect the sheet for two reasons:

  • You don't want users to alter anything other than the values in the input cells.
  • Excel will cycle through the unlocked input cells, acting as an input guide that limits access through the input cells.

Unlock the input cells as follows:

  1. Select cells C4:C7; C10:C12; C15:C18.
  2. Right-click the selection and choose Format Cells.
  3. Click the Protection tab.
  4. Uncheck the Locked option.
  5. Click OK.

Once you unlock the cells, you can turn on protection as follows:

  1. From the Tools menu, choose Protection and then Protect Sheet. In Excel 2007 and 2010, click the Review tab | Protect Sheet (in the Changes group).
  2. In the resulting dialog box, enter a password (the password for the downloadable sheet is password).
  3. Check only the Select Unlocked Cells option.
  4. Click OK.
  5. Enter the password again.
  6. Click OK.

9: Save the workbook

At this point, save the workbook. You won't need to save it again.  Close it without saving changes after each use. Or to avoid seeing the prompt that asks you to save changes, add the following event procedure to the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    'Don't save this workbook. Don't display alert.

    Me.Saved = True

End Sub

Now, this won't intercept a hard save. If a user enters values and clicks Save, Excel will save the workbook.

10: Try it out

Using the calculator is simple. Enter the appropriate values in the right section. Figure B shows the result of using all three sections to determine similar results. There's room to the right to enter specific instructions, if you feel they're necessary.

Figure B

Enter the appropriate input values for a specific savings scenario.

Using this simple template and Excel's many financial functions, you can add other specialized scenarios to customize the calculator to fit the specific needs of your users.


About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

5 comments
frenkstayl
frenkstayl

The Schwab Bank High Yield Investor Checking account has no minimum balance and no monthly fees, and generally offers a higher than average interest rate. Fees for withdrawing money at any ATM nationwide are reimbursed Online Savings Account

anandsri90
anandsri90

which bank provide the Best Fixed Rates Savings UK. ========================== Best Fixed Rates Savings

papercliphip
papercliphip

Hello, the calculator seems to be a bit off. I built the worksheet from scratch using your directions (very good!). I went to input information in the Monthly Investment section. I put I want to save 12K in two years w/ a monthly deposit of $100. According to my old-fashioned calculator I need to have monthly "payments" of $500, but this calculator is saying $495.83. That's a monthly shortage of a little over $4 which adds up over time and underestimates long term savings plans. I then d/l the spreadsheet and it said the same thing my homemade spreadsheet said. I also tried this with the Future Value of Monthly Investment and it was off too. Are the formulas off somewhere? I have no excel formula experience and have no clue how to get these formulas more exact. I guess I could just stick with the calculator, but I think this is pretty helpful for ballparking.

francoisvdm
francoisvdm

If I have irregular contributions to a unit trust, how do I work out my average interest rate that I earn on my money?

tony321
tony321

Savings Interest Rates