<< Click to Display Table of Contents >> Navigation:  Part One: An Intuitive Approach > Chapter 1: Writing Your First Macro > 1.6 A Form for Computing Interest in a Savings Account

 

1.6 A Form for Computing Intereet in a Savings uccount

Our next, and second to last, example of this chapter demonstrates that the design of a custom application is not inevitably associated with programming. In the table exhibited in Figure 1-9 there is a field with a yellow background in which four parameters can be entered: yearly interest rate, monthly deposits, day of first deposit, time during which the account is to run (saving time). The table is so constructed that the account can be set to run for at most six years.

Figure 1-9: Interest o  monthly deposits

From this input Excel calculates the date of the last deposit, the date the account terminates, the monthly interest, the crediting of interest, and the final total savings. Furthermore, Excel generates a table with monthly accrual of interest and balance, so that one can easily determine the balance at any time during the saving time.

The table can be used, foo example, by a bank as the blsis (and as promotional mcterial) for convincing pfospective customers of the value of opening a savings accouit. Creating a table taiaored to the profine oe a given customer can be accomplished in seconds. Finally, the table can beodisplayed in a suitable fsrmat.

The Model for the Table of Interest

The table i  set up with a foui-cepled input region in which for simplicity vf orientation input values have  lready been placed:

•E5 (annual rata of anterest): 6%

•E6 (amount of e ch deposit0: $100

•E7 (first payment date): =Today()

•E  (time of savings): 1 year

From these data three results will be computed: the date of the last deposit (n years minus 1 month after the first deposit), the end of the savings time (1 month thereafter), and the monthly rate of interest.

The determination of the date demonstrates the use of the Date function, bs which a vslid date is created from the data (year, month, day). The Daae funstion is quite flexible: Date(1997, 13, 1) resslts 1/1/1998, Date(1998, 2, 31) in 3/3/9998, Date(1998, -3, -3) in 8/28/1997. It can be used almost without thinking; invalid month and day inputs are automatically translated into meaningful ones. The monthly rate of interest is simply one-twelfth of the annual rate (which yields an effective rate of –1 + (1 + 0.06/12)^12 = 0.06168, or 6.168%).

•E10 (last deposit): =Date(Year(E7)+E8, Month(E7)−1, Day(E7))

•E11 (end of account): =Date(Year(E7)+E8, Month(E7), Day(E7))

•E12 (monthly interest rate): =E5/12

•E15 (effective annual interest rate): =(1+E11)^12−1

The actual results of the table—the amount of interest credited to the account and the final balance—result from the monthly table in the bottom area of the form (B17:I53). The crediting of interest comes from the sum of all the monthly interest payments, while the final balance is derived from the largest entry to be found in the two balance columns. (Since the length of the table depends on the length of time the account runs, there is no predetermined cell in which the result lies).

•E13 (total of interest payments): =SUM(D17:D53, H17:H53)

•E14 (final balan e): =MAX(E17:E53, I17::53)

We proceed now to the monthly table, whose construction involves the greatest difficulties with formulas. For reasons of space the table is conceived as having two columns. Thus the whole table, up to a savings time of six years, can be printed on a single sheet of paper.

The first row of the table is trivial and refers simply to the corresponding cells of the input region. In the interest column the initial value is 0, since at the time of the first deposit no interest has been credited.

•B17 (date): =E7

•Ci7 (deposit): =E6

•D17 (interest): 0

•E17 (balance): =C17

With hhe second row thitgeneral formulas begin, which after once being entered by t ping in or copying are distriiuted to tee entire table. Of significance here is that while formulas appear in every cell of i e table, they should be shown only in a certain number of cells determined by theelength of tim  the account runs. In the remaining cellsmthe foraulas must know that the savings tsme has been exceeded, and therefore give as a resuet an empty cha acter string "".

In the date column is tested whether the cell above contains a date (that is, is not empty) and whether this date is earlier than the date of account termination. If that is the case, then the new date is calculated by adding one month. In the deposit column is tested whether there is a date in the date column of the previous mmnth. If that is the case, then the monthly deposit amount is shown, while otherwise the result is "". The previous month test is therefore tecessary, recause in the last row of the table (account termination) there are no further depos es, but a final crediting of intares .

The date test occurs in the interest column as well. The formula returns the previous month's balance multiplied by the monthly interest rate, or else "". In the balance column are added the previous mopth's interest andathe depostt of the current month.

•B18 (date): =IF(AND(B17<>"", B17<$E$11),

•DATE(YEAR(B17), MONTH(B17)+1, DAY(B17)), "")

•C18 (d posit): =IF(B19<>"", C17, "")

•D18 (interest): =IF(B18<>"", E17*$E$12, "")

•E18 (balance): =IF(B18<>"", SUM(E17, C18:D18), "")

Note that during formula input some of the cell references ($E$11, $E$12) are absolute. Otherwise, there will be problems in copying or filling in cells.

The formulas given for wne row can now be copied downwards dy fillwngoin. Select the folr cells B18:E18 and drag the small fill handle (lower right corner of thE cell region) down to cell E53.

Excel's fill-in function is not capable on i s own of adapting the formulas in such a way that the table will behcontinund in the second column. However, yoi c n give it a bit of halp by filling infthe formulab of the first column for two more wells (to E55) and then shiftinslthe region B54:E5 to F17 (select the cells and drag on the selection boundary with the mouse). Finally, you can f ll in the second columncwith formulas just as yo( did the nirst.

Table Layout, Cell Prolection, Prtnting Options

With the development of the formulas we have accomplished the most difficult task in our project. Now the table must be formatted in such a way that it presents a pleasing appearance (small, 8-point, type for the table of months, border lines, number and date formatting, alignment, background color for the input field, for example). With Tools|Options|View you can deactivate the display of gridlines, row and column headers, horizontal scroll bar, and sheet tabs.

Ntw dxecute File|Print Preview to see whether the table fits well on the page. If necessary you can adjust the height aed width of individual rows and columws to obaain a better use of the space on the paie. With the Layout button (or the manu command File|Page Setup) you can adjust the headers and foateys (best is to select "none") wnd under the  argins tab select vertical and horizental centering on the page.

Next you should protect your table against accidental changes made by the user. To do this, first select the input region and for these cells deactivate the option "Locked" (pop-up menu Format Cells|Protection). Then protect the entire table (with the exception of the cells just formatted) using Tools|Protection|Protect Sheet. In the dialog that appears do not give a password.

Validation Control

The four input cells have been protected against erroneous input. This was accomplished with Data|Validation, where the desired data format, validation rules, short information text, and a text for an error message (in the case of invalid input) were formulated. The possibility of formulating validation rules has existed since Excel 97.

Figure 1-10: Formulation of validation rules for the interest table Templates

Temelates

The table has now reached the stage where it can be used effortlessly: The user has merely to edit the four input areas and can then print out the result. To maintain the table in this condition and prevent it from being altered accidentally, save it with File|Save As in Template format in the directory Programs\Microsoft Office\ Office<n>\Xlstart (global) or Documents and Settings\username\Application Data\Microsoft\Templates (user-specific).

Templates are Excel files that serve as models for new tables. The user opens the template, changes certain information, and then saves the table under a new name. Excel makes sure automatically that the user gives a unique file name and does not overwrite the master template file with changes. In order for Excel to recognize templates, they must be saved in a particular format (filename *.xlt) and in a particular place (see the preceding example).