Built-In Functions Fundamentals

 

Conditional Functions

 

The Choose Function

Once again, since Microsoft Access doesn't inherently provide a programming environment, it relies on logical functions to take care of this aspect. The Choose() function is one of those that can test a condition and provide alternatives. The Choose() function works like nested conditions. It tests for a condition and provides different outcomes depending on the result of the test. Its syntax is:

Choose(Condition, Outcome1, Outcome2, Outcome_n)

The first argument of this function is the condition that should be tested. It should provide a natural number. After this test, the Condition may evaluate to 1, 2, 3, or more options. Each outcome is then dealt with. The first, Outcome1, would be used if the Condition produces 1. The second, Outcome2, would be used if Condition produces 2, etc.

The Switch Function

We have seen that the IIf() function is used to check a condition and can perform one of two statements depending on the result of the condition. In some expressions, there will be more than one condition to check. Although you can nest IIf() functions to create a complex expression, Microsoft Access provides another function that can perform this task. The function is called Switch and its syntax is:

Switch(Expression1, What To Do If Expression1 Is True,
       Expression2, What To Do If Expression2 Is True,
       Expression_n, What To Do If Expression_n Is True)

Unlike IIf(), the Switch() function does not take a fixed number of arguments. It takes as many combinations of <Expression -> Statement>s as you need. Each expression is evaluated. If the expression evaluates to true, the statement that follows it executes. Although you can spend a great deal of time tuning a conditional expression such as one involving a Switch() function, it is still possible that none of the expressions evaluates to true. In this case, you can add a last expression as True and provide a subsequent statement to use. The syntax you would use is:

Switch(Expression1, What To Do If Expression1 Is True,
       Expression2, What To Do If Expression2 Is True,
       Expression_n, What To Do If Expression_n Is True,
       True, What To Do With A False Expression)
   

Practical Learning: Using the Switch Function

  1. From the resources that accompany this book, copy the Operations database and paste it in your Exercises folder
  2. Open the Operations database and, on the Database window, click the Forms button (it should be clicked by default). The double-click frmSwitch to open it in Design View
    3. On the form, click the Result text box and, on the Properties window, click Control Source and change it as follows (on one line):
     
    =Switch([fraOperations]=1,Nz([txtNumber1])+Nz([txtNumber2]),
            [fraOperations]=2,Nz([txtNumber1])-Nz([txtNumber2]), 
            [fraOperations]=3,Nz([txtNumber1])*Nz([txtNumber2]), 
            [fraOperations]=4,Nz([txtNumber1])/Nz([txtNumber2]))
  3. Test the form:
     
  4. Save it
 

Arithmetic Functions

 

The Absolute Value

The decimal numeric system counts from minus infinity (-∞) to infinity (+∞). This means that a  number can be usually negative or positive, depending on its position from 0, which is considered as neutral. In some operations, the number considered will need to be only positive even if it is provided in a negative format.

The absolute value of a number x is x if the number is (already) positive. If the number is negative, then its absolute value is its positive equivalent. For example, the absolute value of 12 is 12, while the absolute value of –12 is 12.

To get the absolute value of a number, you can use one of the Abs() function. Its syntax is:

Abs(number)

The Exponential

The Exp() function is used to calculate the exponential value of a number. Its syntax is:

EXP(number)

The argument, number, a double-precision value, represents the number to be evaluated. If the value of number is less than -708.395996093 (approximately), the result is reset to 0 and qualifies as underflow. If the value of the argument x is greater than 709.78222656 (approximately), the result is infinity and qualified as overflow.

The Square Root

The Sqr() function is used to calculate the square root of a double-precision number. Its syntax is:

Sqr(number)

This function takes one argument as a positive floating number. After the calculation, the function returns the square root of x.

String Functions

 

Character Retrieval

The Chr() function is used to retrieve a character based on an ASCII character number passed to the function. It could be used to convert a number to a character. It could also be used to break a line in a long expression. The syntax of this function is:

Chr(Number)

Based on the table of ASCII characters, a call as Chr(65) would produce the letter A. Not all ASCII characters produce a known letter. For example, when Chr(10) is used in a string, it creates a “new line”.

The Message Box

A message box is a special form used to display some information to the user. As opposed to a regular form, the user cannot type anything on the box. There are usually two ways you can use a message box: You can simply display a message to the user, or you can get an answer from her. If you only want to display a message, the syntax you would use is:

MsgBox(Message To Display, Flag, Caption)

This function takes only one required argument, the message, and some optional arguments. You use the name of the function, MsgBox, to create a message box. Between its parentheses, type the desired message to display. An example would be:

MsgBox(“Remember to submit your time sheet”)

If you want to display the message box on various lines, edit the string to include a call to Chr(10). Here is an example:

MsgBox(“Remember to submit your time sheet” + Chr(10) 
       “Only time sheets received on time will be honored”, )

The message to display can also be created as an expression. After providing the message, you can display it without the other arguments. Here is an example of a message box created with

MsgBox("Remember to submit your time sheet")



After the message, as mentioned already, the other argument are optional. If you provide only the message, the message box displays the OK button. The second argument allows you to specify the button(s) to display. The options are as follows:

Value Buttons
0
1
2
3
4
5

If you provide a value other than those in the list, the message box would display only the OK button. Here is an example of a message box created with:

MsgBox("Do you want to submit your time sheet?",4)

Besides displaying a button, the second argument is also used to display an icon. To get an icon, you add one of the following values:

Value Icon Suited when
16 Warning the user of an action performed on the database
32 Informing the user of a non-critical situation
48 Asking a question that expects a Yes or No, or a Yes, No, or Cancel answer
64 A critical situation or error has occurred. This icon is appropriate when informing the user of a termination or deniability of an action

To use one of these icons, add (a simple addition) its value to that of the desired button or combination of buttons from the previous table. Here is an example created with

MsgBox("Do you want to submit your time sheet?", 32 + 4)

The same as:

MsgBox("Do you want to submit your time sheet?", 36)

When the buttons of a message box displays if the message box has more than one button, one of them has a thick border. This button is referred to as the default button. If the user presses Enter, such a button would be activated. Besides selecting the buttons and displaying an icon, the second argument can also be used to specify what button would have focus, that is, what would have a thick border and would be applied if the user presses Enter, on the message box. The default button is specified using one of the following values:

Value If the message box has more than one button, the default button would be

Value If the message box has more than one button, the default button would be
0 The first button
256 The second button
512 The third button
768 The fourth button

To specify this option, add the number to the button and/or icon value(s).

The third argument of the MsgBox function, Caption, is the string that would display on the title bar of the message box. It is a string whose word or words you can enclose between parentheses or that you can get from a created field.

As mentioned already, you can create a message to simply display a message to the user. Because MsgBox is a function, you can also retrieve the value it returns and use it as you see fit. The value this function returns corresponds to the button the user clicks on the message box. Depending on the buttons the message box is displaying, after the user has clicked, the MsgBox function can return one of the following values:

Displayed Button(s) If the user clicked The return value is
1
1
2
3
4
5
6
7
2
6
7
4
2
 

Date and Time

 

The Values of the Current Date and Time

Microsoft Access provides various functions to perform date and time related operations. These functions allow you to add dates or times, find the difference between dates or times, or add constant values to dates or times.

The current date of the current is represented by a function called Date() while the current time of the computer is represented by a function called Time(). These two values (the current date and the current time) can be combined and are represented by a function called Now().

 

Practical Learning: Setting Current and Time on Data Fields

  1. Open the Bethesda Car Rental2 database and click Tables in the Database window
  2. Double-click the OrderProcessing table to open it
  3. After viewing the table, switch it to Design View
  4. Click OrderDate and press F6
  5. In the lower section of the table, click Default Value. Type =Date() and press Enter
  6. In the upper section of the table, click OrderTime and press F6
  7. In the lower section of the table, click Default Value. Type =Time() and press Enter
  8. Save the table and switch it to Datasheet View

Adding to a Date

Operations on dates and times are performed using functions such as DateAdd() and DateDiff().

The DateAdd() function is used to add an interval date value to the specified date. It is used to add a number of days, weeks, months, or years to another date. The syntax of the DateAdd() function is

DateAdd(Interval, Number, date)

The Interval argument is required and it specifies the kind of value needed as a result. This argument is passed as a string, thus enclosed between double quotes and can have one of the following values:

Interval Used To Get
s Second
n Minute
h Hour
w Numeric Weekday
ww Week of the Year
d Day
y Numeric Day of the Year
m Month
q Quarter
yyyy Year

The Number argument is required also. It specifies the number of units you want to add. If you set it as positive, its value will be added. If you want to subtract, make it negative.

The date argument is the date to which you want to add the number.

Subtraction From a Date

The DateDiff() function is used to find the difference between two date or time values. It allows you to find the number of seconds, minutes, hours, days, weeks, months, or years when you supply two recognizable values. The DateDiff() function takes 5 arguments, 3 are required and 2 are optional.

The syntax of the function is

DateDiff(Interval, Date1, Date2, Option1, Option2)

The Interval argument is required and it specifies the kind of value you want as a result. This argument is passed as a string and can have one of the following values:

Interval Used To Get
s Second
n Minute
h Hour
w Numeric Weekday
ww Week of the Year
d Day
y Numeric Day of the Year
m Month
q Quarter
yyyy Year

Required also, the Date1 and Date2 arguments specify the date or time values that will be used when performing the operation.

By default, the days of a week are counted starting on Sunday. If you want to start counting those days on another day, supply the Option1 argument using one of the following values: 1, 2, 3, 4, 5, 6, 7. There are other variances to that argument.

If your calculation involves weeks or finding the number of weeks, by default, the weeks are counted starting January 1st. If you want to count your weeks starting at a different date, use the Option2 argument to specify where the program should start.

For our time sheet that we want employees to use, we will use a series of combo boxes so the user can only select the time instead of typing it. This reduces the likelihood of errors. When an employee signs a time sheet, he or she can select both starting and ending shifts. We should develop a basic algorithm that can solve our problem in a simple but effective manner. We need to make sure that the start time is less than or equal to the end time. In the same way, the end time should be set higher or equal to the start time. Since we cannot prevent the user from selecting a start time that is higher than the end time or from selecting an end time that is less than the start time, we will set the result to 0 hours whenever the user selects an invalid time sequence.

We will start with the following pseudo-code:

IF Time Out is greater than or equal to Time In THEN
    We can calculate the time
OTHERWISE
    Set the shift value to 0
END IF

This translates to

IF TimeOut >= TimeIn THEN
    Result = TimeOut - TimeIn
ELSE
    Result = 0
END IF

Now, we need to figure out how to calculate the time difference. Because the result will be used to calculate the employee's salary using the hourly wage, we need to have this result as a number, namely a decimal number (as 0.00). If we use the DateDiff() function, we can calculate the minutes or the hours value of the difference. If both start and end times are divisible by 60, as in 09:00 AM to 05:00 PM, the difference can be easily calculated to produce the number of hours, in this case 8.00. To find out if a number is divisible by another number, we can use the Mod operator. This can be done as follows:

IF (TimeOut - TimeIn) Mod 60 = 0  ' The difference is evaluated in minutes
    Result = TimeOut - TimeIn        ' The result is calculated in hours
END IF

If one of either the start or end time doesn't fall on a straight hour value, the resulting time will have a decimal value of 0.50. Therefore, we need to calculate the time difference in minutes instead of hours. Since we are dealing with minutes this time, we can divide the difference by 60 to get the result in minutes. Our pseudo-code would become:

IF (TimeOut - TimeIn) Mod 60 = 0  ' The difference is evaluated in minutes
    Result = TimeOut - TimeIn        ' The result is calculated in hours
OTHERWISE
    Result = TimeOut - TimeIn        ' The result is calculated in minutes

Now that we know how to calculate the time difference, we can include our pseudo-code with the original that would reset the result to 0 if the user selects a wrong time sequence.

Practical Learning: Using Dates and Times

  1. Open the Bethesda Car Rental1 assuming that you had created the time sheet form
  2. Open the TimeSheet form
  3. Click the total text box for Monday and, in the Properties window, click the ellipsis of the Control Source field
  4. On the Expression Builder dialog box, type the following expression:
     
    Expression Builder
     
    Which is:
    =IIf(
          IIf(DateDiff("n",[MondayIn],[MondayOut]) Mod 60=0,
              DateDiff("h",[MondayIn],[MondayOut]),
              DateDiff("n",[MondayIn],[MondayOut])/60)>=0,
          IIf(DateDiff("n",[MondayIn],[MondayOut]) Mod 60=0,
              DateDiff("h",[MondayIn],[MondayOut]),
              DateDiff("n",[MondayIn],[MondayOut])/60),
      0)
  5. Click OK
  6. Set the Format to Fixed and the Decimal Places to 2
  7. In the Properties window, right-click Control for the same Monday total and click Copy
  8. Open Notepad, right-click inside of Notepad and click Paste
  9. Press Ctrl + Home. On the main menu of Notepad, click Edit -> Replace...
  10. In the Find What text box, type Mon and press Tab
  11. In the Replace With text box, type Tues and click Replace All
  12. On the Replace dialog box, click Cancel
  13. Right-click in Notepad and click Select All. Right-click again and click Copy
  14. In Microsoft Access, click the total for Tuesday
  15. In the Properties Window, right-click Control Source and click Paste
  16. Set the Format to Fixed and the Decimal Places to 2
  17. Use the same steps to get the total for the remaining days
  18. Click the Weekly Total text box and set its Control Source to
    =Nz([txtMonday])+Nz([txtTuesday])+Nz([txtWednesday])+Nz([txtThursday])
    +Nz([txtFriday])+Nz([txtSaturday])+Nz([txtSunday])
  19. Set its Format to Fixed and the Decimal Places to 2
  20. Save the form and preview it
     
    Time Sheet
  21. Perform a few selections for different shifts
     
    Time Sheet
  22. Close the form
  23. Open the Bethesda Car Rental2 database
  24. In the Database window, click Forms and double-click the OrderProcessing form to open it
  25. After viewing the form, switch it to Design View
  26. On the form, double-click the text box on the right side of Days
  27. In the Properties window, click the All tab and click Control Source
    Type =DateDiff("d", StartDate, EndDate) and press Enter
  28. On the form, click the text box on the right side of the Sub-Total label
  29. In the Properties window, click Control Source. Type
    =Nz(RateApplied) * Nz(txtNumberOfDays) and press Enter
  30. Click the box to the right the Tax Amount label and set its Control Source property to =CLng([txtSubTotal]*[TaxRate]*100)/100
  31. On the form, click the text box to the right of Rent Total
  32. In the Properties window, set its Control Source to
    =(CLng([txtSubTotal]*[TaxRate]*100)/100) + txtSubTotal
  33. Save and close the form
  34. Open the RentalRates form and open the OrderProcessing form to display both on the screen
  35. Perform a few rental orders
     
     
  36. Close both forms

 

 

Series-Based Functions

Introduction

A series or collection-based function is one that considers a particular column and performs an operations on all of its cells. For example, if you have a particular column in which users enter a string, you may want to count the number of strings that have been entered in the cells under that column. In the same way, suppose you have a column under whose cells users most enter numbers. Using a series-based function, you can get the total of the values entered in the cells of that column.

The general syntax of series-based functions is:

FunctionName(Series)

The FunctionName is one of those we will see shortly. Each of these functions takes one argument, which is usually the name of the column whose cells you want to consider the operation.

The Series-Based Functions

Sum: To perform the addition on various values of a column, you can use the Sum() function. This function is highly valuable as it helps to perform the sum of values in various transactions.

Count: The Count() function is used to count the number of values entered in the cells of a column.

Average: The Avg() function calculates the sum of values of a series and divides it by the count to get an average.

Minimum: Once a series of values have been entered in cells of a column, to get the lowest value in those cells, you can call the Min() function.

Maximum: As opposed to the Min() function, the Max() function gets the highest value of a series.

Practical Learning: Using Series-Based Functions

  1. Open the College Park Auto Shop2 database and click Forms in the Database window
  2. Double-click the sbfParts subform to open it
  3. After viewing the subform, switch it to Design View
  4. On the ToolBox, click the Text Box control and click somewhere in the Form Footer section
  5. Delete its label and change the following properties for the new text box
    Name: txtPartsTotal
    Control Source: =Sum(Nz([UnitPrice]) * Nz([Quantity]))
    Format: Currenty
    Decimal Places: 2
    Top: 0
  6. To make the Part combo box provide values to the appropriate text boxes, click the text box under Part Name. In the Properties window, click Data and set its Control Source to
    =[PartID].[Column](2) and press Enter
  7. To provide the same functionality for the UnitPrice text box, we will write a line of code.
    On the Form, right-click the left combo box under Part and click Build Event...
  8. In the Choose Builder dialog box, click Code Builder and click OK
  9. In the Object combo box, make sure PartID is selected. In the Procedure combo box, select AfterUpdate
  10. Implement the AfterUpdate event as follows (it is the exact same thing as above except that, this time, the user can change the unit price of an item):
    Private Sub PartID_AfterUpdate(Cancel As Integer)
        Me![UnitPrice] = Me![PartID].Column(3)
    End Sub
  11. Close the code window or Microsoft Visual Basic
  12. Back in Microsoft Access, click the text box in the Form Footer section to select it
  13. Using the Format tab of the Properties window, set its Visible property to No
  14. Reduce the height of the text box and reduce the height of the Form Footer section as follows:
     
  15. Save and close the subform
  16. Open the WorkOrders form in Design View
  17. On the Toolbox, click the Text Box and click in the lower-right empty area of the Detail section
  18. Change the Caption of its label to Total Parts
  19. Click its text box and change the following properties
    Name: txtTotalParts
    Control Source: =[Forms]![WorkOrders]![sbfParts].[Form]![txtPartsTotal]
    Format: Currency
    Decimal Places: 2
  20. Once again, on the Toolbox, click the Text Box and click in the same area where the previous text box was added
  21. Change the Caption of its label to Total Labor
  22. Click its text box and change the following properties
    Name: txtTotalLabor
    Control Source: =Nz([LaborCost1])+Nz([LaborCost2])+Nz([LaborCost3])+Nz([LaborCost4])+
    Nz([LaborCost5])+Nz([LaborCost6])
    Format: Currency
    Decimal Places: 2
  23. Add another Text Box and change the Caption of its label to Tax Amount
  24. Click its text box and change the following properties
    Name: txtTaxAmount
    Control Source: =CLng(([Forms]![WorkOrders]![sbfParts].[Form]![txtPartsTotal]+
    Nz([LaborCost1])+Nz([LaborCost2])+Nz([LaborCost3])+Nz([LaborCost4])+
    Nz([LaborCost5])+Nz([LaborCost6]))*[TaxRate]*100)/100
    Format: Currency
    Decimal Places: 2
  25. Add one more Text Box to the same section and change the Caption of its label to Order Total
  26. Click its text box and change the following properties
    Name: txtOrderTotal
    Control Source: =([Forms]![WorkOrders]![sbfParts].[Form]![txtPartsTotal])+
    Nz([LaborCost1])+Nz([LaborCost2])+Nz([LaborCost3])+Nz([LaborCost4])+
    Nz([LaborCost5])+Nz([LaborCost6])+
    CLng(([Forms]![WorkOrders]![sbfParts].[Form]![txtPartsTotal]+
    Nz([LaborCost1])+Nz([LaborCost2])+Nz([LaborCost3])+Nz([LaborCost4])+
    Nz([LaborCost5])+Nz([LaborCost6]))*[TaxRate]*100)/100
    Format: Currency
    Decimal Places: 2
  27. Save and close the form
  28. To see the result, perform a few orders as follows:

Domain-Based Functions

Introduction

A domain-based function is used to get a value from another object and deliver it to the object in which it is being used or called. The general syntax of these functions is:

FunctionName(WhatValue, FromWhatObject, WhatCriteria)

To perform its operation, a domain-based function needs three pieces of information, two of which are required (the first two arguments) and one is optional (the third argument).

when calling one of these functions, you must specify the value of the column you want to retrieve. This is provided as the WhatValue in our syntax. This argument is passed as a string.

The FromWhatObject is the name of the object that holds the value. It is usually the name of a form. This argument also is passed as a string.

The third argument, WhatCriteria in our syntax, specifies the criterion that will be used to filter the WhatValue value. It follows the normal rules of setting a criterion.

The Domain-Based Functions

Domain First: If you want to find out what was the first value entered in the cells of a certain column of an external form or report, you can call the DFirst() function.

Domain Last: The DLast() function does the opposite of the DFirst() function: It retrieves the last value entered in a column of a form or report.

Domain Sum: To get the addition of values that are stored in a column of another form or report, you can use the DSum() function.

Domain Count: The DCount() function is used to count the number of values entered in the cells of a column of a table.

Domain Average: The DAvg() function calculates the sum of values of a series and divides it by the count of cells on the same external form or report to get an average.

Domain Minimum: The DMin() function is used to retrieve the minimum value of the cells in a column of an external form or report

Domain Maximum: As opposed to the DMin() function, the DMax() function gets the highest value of a series of cells in the column of an external form or report.

Business Functions

Introduction

An asset is an object of value. It could be a person, a car, a piece of jewelry, a refrigerator. Anything that has a value is an asset. In the accounting world, an asset is a piece of/or property whose life span can be projected, estimated, or evaluated. As days, months or years go by, the value of such an asset degrade.

When an item is acquired for the first time as “brand new”, the value of the asset is referred to as its Cost. The declining value of an asset is referred to as its Depreciation. At one time, the item will completely lose its worth or productive value. Nevertheless, the value that an asset has after it has

lost all of its value is referred to its Salvage Value. At any time, between the purchase value and the salvage value, accountants estimate the value of an item based on various factors including its original value, its lifetime, its usefulness (how the item is being used), etc.

The Double Declining Balance

The Double Declining Balance is a method used to calculate the depreciating value of an asset.  To get it, you can use the DDB function whose syntax is:

DDB(cost, salvage, life, period)

The first argument, cost, represents the initial value of the item.

The salvage argument is the estimated value of the asset when it will have lost all its productive value. The cost and the salvage values must be given in their monetary values.

The value of life is the length of the lifetime of the item; this could be the number of months for a car or the number of years for a house, for example.

The period is a factor for which the depreciation is calculated. It must be in the same unit as the life argument. For the Double Declining Balance, this period argument is usually 2.

The Straight Line Depreciation

Another method used to calculate the depreciation of an item is through a concept referred to as the Straight Line Depreciation. This time, the depreciation is considered on one period of the life of the item. The function used is SLN and its syntax is:

SLN(cost, salvage, life);

The cost argument is the original amount paid for an item (refrigerator, mechanics toolbox, high-volume printer, etc).

The salvage, also called the scrap value, is the value that the item will have (or is having) at the end of Life.

The life argument represents the period during which the asset is (or was) useful; it is usually measured in years.

 

The Sum of the Years' Digits

The Sum-Of-The-Years’-Digits provides another method for calculating the depreciation of an item. Imagine that a restaurant bought a commercial refrigerator (“cold chamber”) for $18,000 and wants to estimate its depreciation after 5 years using the Sum-Of-Years’-Digits method. Each year is assigned a number, also called a tag, using a consecutive count; this means that the first year is appended 1, the second is 2, etc. This way, the depreciation is not uniformly applied to all years.

Year => 1, 2, 3, 4, and 5.

The total count is made for these tags. For our refrigerator example, this would be

Sum = 1 + 2 + 3 + 4 + 5 = 15

Each year is divided by this Sum, also called the sum of years, used as the common denominator:

This is equivalent to 1. As you can see, the first year would have the lowest divident (1/15 ≈ 0.0067) and the last year would have the highest (5/15 ≈ 0.33).

To calculate the depreciation for each year, the fractions (1/15 + 2/15 + 3/15 + 4/15 + 5/15) are reversed so that the depreciation of the first year is calculated based on the last fraction (the last year divided by the common denominator). Then the new fraction for each year is multiplied by the original price of the asset. This would produce (this table assumes that the refrigerator will have a value of $0.00 after 5 years):

Year Fraction * Amount = Depreciation
1 5/15 * $18,000.00 = $6,000.00
2 4/15 * $18,000.00 = $4,800.00
3 3/15 * $18,000.00 = $3,600.00
4 2/15 * $18,000.00 = $2,400.00
5 1/15 * $18,000.00 = $1,200.00
Total Depreciation = $18,000.00

Overall, Microsoft Office uses the following formula to calculate an item depreciation using the Sum-Of-The-Years'-Digits:

The function used to calculate the depreciation of an asset using the sum of the years' digits is called SYD and its syntax is:

SYD(cost, salvage, life, period)

The cost argument is the original value of the item; in our example, this would be $18,000.

The salvage parameter is the value the asset would have (or has) at the end of its useful life.

The life is the number of years the asset would have a useful life (because assets are usually evaluated in terms of years instead of months).

The period parameter is the particular period or rank of a Life portion. For example, if the life of the depreciation is set to 5 (years), the period could be any number between 1 and 5. If set to 1, the depreciation would be calculated for the first year. If the Period is set to 4, the depreciation would calculated for the 4th year. You can also set the period to a value higher than life. For example, if life is set to 5 but you pass 8 for the period, the depreciation would be calculated for the 8th year. If the asset is worthless in the 8th year, the depreciation would be 0.
 

Finance Functions

 

Introduction

Microsoft Excel provides a series of functions destined to perform various types of financially related operations. These functions use common factors depending on the value that is being calculated. Many of these functions deal with investments or loan financing.

The Present Value is the current value of an investment or a loan. For a savings account, a customer could pledge to make a set amount of deposit on a bank account every month. The initial value that the customer deposits or has in the account is the Present Value. The sign of the variable, when passed to a function, depends on the position of the customer. If the customer is making deposits (car loan, boat financing, etc), this value must be negative. If the customer is receiving money (lottery installment, family inheritance, etc), this value should be positive.

The Future Value is the value the loan or investment will have when the loan is paid off or when the investment is over. For a car loan, a musical instrument loan, a financed refrigerator, a boat, etc, this is usually 0 because the company that is lending the money will not take that item back (they didn't give it to the customer in the first place, they only lend him or her some money to buy the item). This means that at the end of the loan, the item (such as a car, boat, guitar, etc) belongs to the customer and it is most likely still worth something.

As described above and in reality, the Future Value is the amount the item would be worth at the end. In most, if not all, loans, it would be 0. On the other hand, if a customer is borrowing money to buy something like a car, a boat, a piano, etc, the salesperson would ask if the customer wants to put a "down payment", which is an advance of money. Then, the salesperson or loan officer can either use that down payment as the Future Value parameter or simply subtract it from the Present Value and then apply the calculation to the difference. Therefore, you can apply some type of down payment to your functions as the Future Value.

The Number Of Periods is the number of payments that make up a full cycle of a loan or an investment.

The Interest Rate is a fixed percent value applied during the life of the loan or the investment. The rate does not change during the length of the Periods.

For deposits made in a savings account, because their payments are made monthly, the rate is divided by the number of periods (the Periods) of a year, which is 12. If an investment has an interest rate set at 14.50%, the Rate would be 14.50/12 = 1.208. Because the Rate is a percentage value, its actual value must be divided by 100 before passing it to the function. For a loan of 14.50% interest rate, this would be 14.50/12 = 1.208/100 = 0.012.

The Payment is the amount the customer will be paying. For a savings account where a customer has pledged to pay a certain amount in order to save a set (goal) amount, this would be the amount the customer would pay every month. If the customer is making payments (car loan, mortgage, deposits to a savings account, etc), this value must be negative. If the customer is receiving money (lottery installment or annuity, family inheritance, etc), this value must be positive.

The Payment Time specifies whether the payment is made at the beginning or the end of the period. For a monthly payment, this could be the beginning or end of every month.

 The Future Value of an Investment

To calculate the future value of an investment, you can use the FV() function. The syntax of this function is:

FV(Rate, Periods, Payment, PresentValue, PaymentType)

 

The Number of Periods of an Investment

To calculate the number of periods of an investment or a loan, you can use the NPer() function. Its syntax is:

NPer(Rate, Payment, PresentValue, FutureValue, PaymentType);
 

Investment or Loan Payment

The Pmt() function is used to calculate the regular payment of loan or an investment. Its syntax is:

Pmt(Rate, NPeriods, PresentValue, FutureValue, PaymentType)

In the following example, a customer is applying for a car loan. The cost of the car will be entered in cell C4. It will be financed at a rate entered in cell C6 for a period set in cell C7. The dealer estimates that the car will have a value of $0.00 when it is paid off.

 

The Amount Paid As Interest During a Period

When a customer is applying for a loan, an investment company must be very interested to know how much money it would collect as interest. This allows the company to know whether the loan is worth giving. Because the interest earned is related to the interest rate, a company can play with the rate (and also the length) of the loan to get a fair (?) amount.

The IPmt() function is used to calculate the amount paid as interest on a loan during a period of the lifetime of a loan or an investment. It is important to understand what this function calculates. Suppose a customer is applying for a car loan and the salesperson decides (or agrees with the customer) that the loan will be spread over 5 years (5 years * 12 months each = 60 months). The salesperson then applies a certain interest rate. The IPMT() function can help you calculate the amount of interest that the lending institution would earn during a certain period. For example, you can use it to know how much money the company would earn in the 3rd year, or in the 4th year, or in the 1st year. Based on this, this function has an argument called Period, which specifies the year you want to find out the interest earned in.

The syntax of the IPmt() function is:

IPmt(Rate, Period, NPeriods, PresentValue, FutureValue, PaymentType)

The Rate argument is a fixed percent value applied during the life of the loan.

The PresentValue is the current value of the loan or investment. It could be the marked value of the car, the current mortgage value of a house, or the cash amount that a bank is lending.

The FutureValue is the value the loan or investment will have when the loan is paid off.

The NPeriods is the number of periods that occur during the lifetime of the loan. For example, if a car is financed in 5 years, this value would be (5 years * 12 months each =) 60 months. When passing this argument, you must remember to pass the right amount.

The Period argument represents the payment period. For example, it could be 3 to represent the 3rd year of a 5 year loan. In this case, the IPmt() function would calculate the interest earned in the 3rd year only.

The PaymentType specifies whether the periodic (such as monthly) payment of the loan is made at the beginning (1) or at the end (1) of the period.

The FutureValue and the PaymentType arguments are not required.
 

The Amount Paid as Principal

While the IPmt() function calculates the amount paid as interest for a period of a loan or an investment, the PPmt() function calculates the actual amount that applies to the balance of the loan. This is referred to as the principal. Its syntax is:

PPMT(Rate, Period, NPeriods, PresentValue, FutureValue, PaymentType)

The argument are the same as described in the previous sections

The Present Value of a Loan or an Investment

The PV() function calculates the total amount that future investments are worth currently. Its syntax is:

PV(Rate, NPeriods, Payment, FutureValue, PaymentType)

The arguments are the same as described earlier.

The Interest Rate

Suppose a customer comes to your car dealer and wants to buy a car. The salesperson would first present the available cars to the customer so the customer can decide what car he likes. After this process and during the evaluation, the sales person may tell the customer that the monthly payments would be $384.48. The customer may then say, "Wooooh, I can't afford that, man". Then the salesperson would ask, "What type of monthly payment suits you". From now on, both would continue the discussion. Since the salesperson still wants to make some money but without losing the customer because of a high monthly payment, the salesperson would need to find a reasonable rate that can accommodate an affordable monthly payment for the customer. 

The Rate() function is used to calculate the interest applied on a loan or an investment. Its syntax is:

RateE(NPeriods, Payment, PresentValue, FutureValue, PaymentType, Guess)

All of the arguments are the same as described for the other functions, except for the Guess. This argument allows you to give some type of guess for a rate. This argument is not required. If you omit it, its value is assumed to be 10.

The Internal Rate of Return

The IRR() function is used to calculate an internal rate of return based on a series of investments. Its syntax is:

IRR(Values, Guess)

The Values argument is a series (also called an array or a collection) of cash amounts that a customer has made on an investment. For example, a customer could make monthly deposits in a savings or credit union account. Another customer could be running a business and receiving different amounts of money as the business is flowing (or losing money). The cash flows don't have to be the same at different intervals but they should (or must) occur at regular intervals such as weekly (amount cut from a paycheck), bi-weekly (401k directly cut from paycheck, monthly (regular investment), or yearly (income). The Values argument must be passed as a collection of values, such as a range of selected cells, and not an amount. Otherwise you would receive an error.

The Guess parameter is an estimate interest rate of return of the investment.

The Net Present Value

The NPV() function uses a series of cash flows to calculate the present value of an investment. Its syntax is:

NPV(Rate, Value1, Value2, ...)

The Rate parameter is the rate of discount in during one period of the investment.

As the NPV() function doesn't take a fixed number of arguments, you can add a series of values as Value1, Value2, etc. These are regularly made payments for each period involved. Because this function uses a series of payments, any payment made in the past should have a positive value (because it was made already). Any future payment should have a negative value (because it has not been made yet).

 

Lesson Summary

 

MOUS Topics

S17 Use the Control Toolbox to add controls
S31 Create a calculated field
 

Exercises

 

Watts A loan

  1. Open the Watts A Loan database.
    Open the CustomersTransactions form in Design View. Add a Text Box below the subform. Set its Name to txtTotalPayments then set its Format to Currency with 2 Decimal Places. Make it get its value from the txtTransactions text box of the sbfAccountTransactions form
    Add another Text Box below the subform and change its properties as follows:
    Name: txtCurrentBalance
    Control Source: =DLookUp("LoanAmount", "LoanProcessing", "CustomerID = " & CustomerID) - Nz(txtTotalPayments)
    Format: Currency
    Decimal Places: 2

    Save and close the form
  2. Create a new table in Design View with the following fields:
     
    Field Name Data Type Additional Information
    LoanEvaluationID AutoNumber Primary Key
    Caption: Loan Evaluation ID
    LoanAmount Currency Caption: Loan Amount
    Default Value: 0
    InterestRate Number Field Size: Double
    Format: Percent
    Caption: Interest Rate
    Default Value: 0.0875
    NumberOfPeriods Number Field Size: Integer
    Caption: NumberOfPeriods
    Default Value: 12

    Save the table as LoanEvaluation and close it

  3. Create a new form based on the LoanEvaluation table
    Save the form as LoanEvaluation
    Add a Text Box in its Detail section and set its properties as follows:
    Name: txtPeriodicPayment
    Control Source:
    =Abs(Pmt(Nz(CDbl([InterestRate]))/12,
    Nz(CInt([NumberOfPeriods])),Nz(CDbl([LoanAmount])),0,0))

    Format: Currency
    Decimal Places: 2

    Use the Command Button Wizard to add a button that can be used to close the form
    Design the form as follows:
     


    Disable the Maximize button and make the form Pop Up. Don't make it automatically center itself. Before saving the form, position it slightly to the middle-right side of the screen so Microsoft Access would remember that position
    Save and close the form
  4. Open the LoanProcessing form in Design View. Using the Command Button Wizard to add a button that, when clicked, would open the LoanEvaluation form (remember that there is no relationship between both forms; therefore, you will Open The Form And Show All The Records). Set the button's Text to Loan Evaluation and its Name to cmdLoanEval.
    Save and close the form
  5. Open the LoanProcessing form and use its Loan Evaluation button to open the LoanEvaluation form. In the Loan Evaluation form, evaluate a $1500.00 amount of at 12.50% paid in 28 months. After evaluating it, manually create a new personal loan in the Loan Processing form for the amount of $1500 at 12.50% for 28 payments. The loan is processed by the owner, for the 83-457-8 account on April 10th, 2002. Make the 1st payment due on May 20th of the same year and put a reminder that the payments are due every 22th of the month
    Evaluate other amounts and create loans for the other customers.
    Close both forms
  6. Open the CustomersTransactions form to see the results
     
  

Previous Copyright © 2002-2004 Yevol Next