Home

Database Numeric Values
 

Integer-Based Data Types
 

Introduction

A number is a digit (0, 1, 2, 3, 4, 5, 6, 7, 8, or 9), a combination of digits, or a combination of one or more digits, a separator, and one or more digits. Microsoft Access supports three categories of numbers and there are various ways you can apply one to a field. You can work in either the Datasheet View or the Design View of a Table.

 In the Datasheet View, you can use some of the fields from the Field Templates and this options has some limitations. In the Design View, before specifying the particular numeric type of a field, first set its Data Type to Number.

A natural number is one that contains either only one digit or a combination of digits and no other character, except those added to make it easier to read. Examples of natural numbers are 122, 8, and 2864347. When a natural number is too long, such as 3253754343, to make it easier to read, the thousands are separated by a special character.

The character depends on the language or group of languages and it is called the thousands separator. For US English, this character is the comma. The thousands separator symbol is mainly used only to make the number easier to read. You should be careful to use it in your database.

To support different scenarios, Microsoft Access provides different types of natural numbers.

Practical Learning: Using a Number
  1. Start Microsoft Access and, to create a new database, click Blank Database
  2. Set the File Name to Real Estate3 and click Create
  3. Double-click ID, type PropertyID, and press Enter
  4. Right-click the Table1 tab and click Design View
  5. Set the name to Properties and click OK
  6. Click the empty field under PropertyID and type p
  7. For its Data Type, select Lookup Wizard...
  8. In the first page of the wizard, select the second ration button and click Next
  9. Click under Col1 and type Single Family
  10. Press the down arrow key and type Townhouse
  11. Press the down arrow key and type Condominium
  12. Press the down arrow key and type Unknown
  13. Click Next
  14. Set the lookup column label to Property Type and click Finish
  15. Change the following properties:
    Field Size: 20
    Default Value: "Unknown"
  16. In the top section of the table, click the first empty field under Property Type and type p
  17. For its Data Type, select Lookup Wizard...
  18. In the first page of the wizard, select the second ration button and click Next
  19. Click under Col1 and create the following items:
     
    Unknown
    Excellent
    Good Condition
    Needs Some Repair
    Bad Shape
  20. Click Next
  21. Set the lookup column label to Condition and press Enter
  22. Change the following properties:
    Field Size: 20
    Default Value: "Unknown"
  23. Complete the list of fields with the following
     
    Field Name Field Size Input Mask Default Value
    Property Type 20   "Unknown"
    Condition 20   "Unknown"
    Address 100    
    City 50    
    State 2 >LL "MD"
    ZIP Code 12 00000\-9999;0;_  
  24. Save the table

A Byte

A byte is a small positive natural number that is between 0 and 255. This type of number is applied to such items as a person's age, the number of students in an elementary classroom, the number of rooms in a regular house, the number of pages of a magazine or newspaper, etc. To create a field that will need this range of values, use the Design View and specify the field's Data Type as Number. Then, in the lower section of the table, set its Field Size to Byte.

If you have a value that you want to convert to a Byte, you can use the CByte() function. Its syntax is: 

CByte(Expression) As Byte

This function takes one argument as the value that needs to be converted. It then converts it to a Byte.

Practical Learning: Using a Byte Type

  1. Click the first empty box under ZIP Code and type Bedrooms
  2. Set its Data Type to Number
  3. In the lower section of the table, click the arrow of the Field Size combo box and select Byte
  4. Save the table

An Integer

An integer is a natural number larger than the Byte. It can hold a value between -32,768 and 32,767. Examples of such ranges are: the number of pages of a book. Because an integer can hold as much value as the Byte, you can apply the integer type wherever you would use a byte type. Microsoft Access supports the integer type through a data type called Integer. Like the byte, you can specify an Integer data type only in the Design View of a table.

To apply the Integer data type to a field, in the Design View of the table, after specifying the field's Data Type as Number, in the bottom section of the table, set its Field Size to Integer.

To convert a value to an integer, you can call the CInt() function whose syntax is: 

CInt(Expression) As Integer

The function takes a value or expression as argument. It then converts that value or expression to an integer.

Practical Learning: Applying the Integer Type

  1. Click the first empty box under Bedrooms and type Stories
  2. Set its Data Type to Number
  3. In the lower section of the table, click the arrow of the Field Size combo box and select Integer
  4. Click the empty box under Stories, type Year Built and press Tab
  5. Type n and press F6
  6. Type i
  7. Save the table
  8. To switch it to Datasheet View, in the lower-right section, click the Datasheet View button Datasheet View

A Long Integer

A long integer is a natural number whose value is between –2,147,483,648 and 2,147,483,642. Examples are the population of a city, the distance between places of different countries, the number of words of a book. Microsoft Access supports the long integer type through a data type called Long Integer (in reality the data type is called Long). You can apply the long integer type in either the Datasheet View or the Design View of a table:

In the same way, you can change the data type of a field to a numeric based, using the same steps described here.

If you have a value or expression to convert to a long integer, you can call the CLng() function. Its syntax is: 

CLng(Expression) As Long

This function takes one argument that is a value or an expression. It converts it to a long integer.

Practical Learning: Using a Long Integer Type

  1. If the Field Templates window is not available, on the ribbon, click Datasheet.
    In the Fields & Columns section, click the New Field button.
    From the Field Templates window, drag Number and drop it on the right side of the PropertyID right column line
  2. On the table, double-click Number and type Property #
  3. On the ribbon, click the Datasheet tab and, in the Data Type & Formatting section, click the Unique and the Is Required check boxes
  4. Right-click the Properties tab and click Design View

Decimal Types

 

Introduction

A real number is a number that displays a decimal part. This means that the number can be made of two sections separated by a symbol that is referred to as the Decimal Separator or Decimal Symbol. This symbol is different by language or group of languages. In US English, this symbol is the period. You can check this by opening the Regional (and Language) Settings of the Control Panel:

Customize Regional Options

On both sides of the Decimal Symbol, digits are used to specify the value of the number. The number of digits on the right side of the symbol determines how much precision the number offers.

Microsoft Access supports decimal number through various options.

Decimal Numbers With Single Precision

When using a decimal number, you may or may not be interested in a high level of precision for that value. If precision is of lesser importance, Microsoft Access provides the Single data type. A single is a decimal number whose value can range from –3.402823e38 and –1.401298e-45 if the number is negative, or 1.401298e-45 and 3.402823e38 if the number is positive.

To apply the Single data type to a field, you must open the table in Design View. After specifying the field's type as Number, in the bottom section of the table, set the Field Size to Single.

If you have a value that you want to convert to a Single type, call the CSng() function whose syntax is: 

CSng(Expression) As Single

This function takes one argument as the value or the expression that needs to be converted. It then converts it to a Single value.

Practical Learning: Using a Single Data Type

  1. In the top section of the table, right-click Stories and click Insert Rows
  2. Type Bathrooms and press Tab
  3. Set the Data Type to Number
  4. In the lower section of the table, click Field Size, click the arrow of its combo box and select Single
  5. Save the table

Decimal Numbers With Double Precision

As stated above, the Single data type can allow large numbers but it offers less precision. If you want to use a large number with a higher level of precision, Microsoft Access provides the Double data type. This is used for numbers that range from 1.79769313486231e308 to –4.94065645841247e–324 if the number is negative or from 1.79769313486231E308 to 4.94065645841247E–324 if the number is positive. Besides supporting large values and this high level of precision, the Double data type provides various other options. To apply these options, you can use either the Datasheet View or the Design View of the table.

To apply the Double data type to a field, open its table in Design View and set the field's Data Type to Number. Then, in the bottom section of the table, set its Field Size to Double.

To convert an expression or a value to a double type, you can call the CDbl() function. Its syntax is: 

CDbl(Expression) As Double

This function takes one argument as a value or an expression to be converted. It then converts it to a Double type of value.

Practical Learning: Using a Double Data Type

  1. In the top section of the table, click the first empty box under Year Built
  2. Type Market Value and set its data type to Number
  3. In the lower section of the table, click Field Size, click the arrow of its combo box and select Double
  4. To change the view, on the ribbon, in the Views section, click the Datasheet View button
  5. Enter a few records
  6. To start a new database, click the Office Button and click New
  7. Set the File Name to Georgetown Dry Cleaners2 and click Create
  8. Right-click the Table1 tab and click Design View
  9. Set the name to Cleaning Orders and press Enter
  10. Double-click ID and type CleaningOrderID
  11. Click the empty box under CleaningOrderID and create the following fields:
     
    Field Name Data Type Field Size Default Value
    CleaningOrderID      
    Customer Name   50  
    Customer Phone   20  
    Deposit Date   40  
    Retrieve Date   40  
    Unit Price Shirts Number Double 1.15
    Quantity Shirts Number Integer 0
    Unit Price Pants Number Double 1.95
    Quantity Pants Number Integer 0
    Other Item1   80 "Unknown"
    Unit Price Item1 Number Double 0.00
    Quantity Item1 Number Integer 0
  12. In the top section of the table, set the Data Type of the Other Item1 field to Lookup Wizard...
  13. In the first page of the wizard, click the second radio button: I will type in the values that I want and click Next
  14. In the second page of the wizard, click the first empty field under Col1 and create the following items
     
    Tie
    Coat
    Dress
    Other
    Jacket
    Swede
    Silk Shirt
    Sweater
    Unknown
    Comforter
    Women Suit
    Regular Skirt
    Men's Suit 2Pc
    Men's Suit 3Pc
    Skirt With Hook
  15. Click Next
  16. Accept the column label as Other Item1 and click Finish
  17. Click the gray box on the left side of Other Item1
  18. Press and hold Shift
  19. Click the gray box on the left side of Quantity Item1
  20. Press Ctrl + C to copy
  21. Click the first empty box under Quantity Item1
  22. Press Ctrl + V to paste
  23. Change the field names to Other Item2, Unit Price Other2, and Quantity Other2 respectively
  24. Click the first empty box under Quantity Item2
  25. Press Ctrl + V to paste
  26. Change the new field names to Other Item3, Unit Price Other3, and Quantity Other3 respectively
  27. Click the first empty box under Quantity Item3
  28. Press Ctrl + V to paste
  29. Change the field names to Other Item4, Unit Price Other4, and Quantity Other4 respectively
  30. Save the table

Currency

Currency refers to monetary values. To show that a number represents a currency, there is a special character used on either the left or the right side of the number. The character used and its position depends on the language. The rules of a monetary values are specified in the Currency property page of the Customize Regional Options property sheet of the Regional and Language Options available from Control Panel:

To support currency values, Microsoft Access provides the Currency data type. You can apply it to a field in either the Datasheet View or the Design View.

To apply the Currency data type to a field:

To convert a value or an expression to a currency value, you can use the CCur() function. Its syntax is: 

CCur(Expression) As Currency

This function takes one argument as the value or the expression that needs to be converted. It then converts it to a currency value.

Practical Learning: Using Currency Types

  1. Make the following changes to some fields of the table:
     
    Field Name Data Type Field Size Format
    Unit Price Shirts Number Double Currency
    Unit Price Pants Number Double Currency
    Unit Price Item1 Number Double Currency
    Unit Price Item2 Number Double Currency
    Unit Price Item3 Number Double Currency
    Unit Price Item4 Number Double Currency
  2. Save the table

Percentage Values

A number is referred to as percentage if it represents a fraction of a 100. Examples of percentage values are the density of a population or the interest rate of a loan. In most cases, a percentage value is written with the percent symbol, which is %. To apply a percent value to a field, you can work from either the Datasheet View or the Design View.

To set up a field for percentage values:

Practical Learning: Using Percentage Values

  1. Click the first empty box under Quantity Item4 and type Tax Rate
  2. Set its Data Type to Number and press F6
  3. In the lower section of the table, set the Field Size to Double
  4. Set the Format to Percent
  5. Set the Default Value to 0.0775
  6. To change the view, right-click the Cleaning Orders tab and click Datasheet View
  7. When asked to save the table, click Yes
  8. Scroll completely to the right.
    On the Field Templates, under Contacts, drag Notes and drop it to the right of Add New Item
  9. Close the table
  10. To create a new form, on the ribbon, click Create and, in the Forms section, click Blank Form
  11. Right-click the Form1 tab and click Save
  12. Set the name to Cleaning Orders and click OK
  13. To switch the form to Design View, right-click the Cleaning Orders tab and click Design View
  14. Right-click the middle of the form and click Form Header/Footer
  15. On the form, click the button at the intersection of the rulers to select the form
  16. On the ribbon, click the Arrange tab
  17. In the AutoFormat section, click the arrow of the AutoFormat button and, in the window that comes up, select Equity (4th column - 2nd row)
  18. On the form, click the Detail bar
  19. On the ribbon, click the Arrange tab.
    In the AutoFormat section, click the arrow of the AutoFormat button and, in the window that comes up, select Trek (2nd column - 5th row)
  20. Double-click the button at the intersection of the rulers to access the Properties window for the form .
    In the Properties window, click the Data and, in the Record Source, select Cleaning Orders
  21. On the ribbon, click Design and, in the Tools section, click Add Existing Fields
  22. From the Field List, drag each field and drop it on the form
  23. Select each label and, from the Font section, set each label's properties as follows:
    Font: Times New Roman
    Font Color: Maroon 5 (Standard section: 6th column - 6th row)
  24. Using the techniques we learned in lessons 4, 5, and 7, design the form as follows (you will add 9 text boxes):
     
  25. Specify the properties of the new text boxes as follows:
     
    Name Format Control Source
    txtTotalShirts Currency =CCur([Unit Price Shirts])*CInt([Quantity Shirts])
    txtTotalPants Currency =CCur([Unit Price Pants])*CInt([Quantity Pants])
    txtTotalItem1 Currency =CCur([Unit Price Other])*CInt([Quantity Other])
    txtTotalItem2 Currency =CCur([Unit Price Item2])*CInt([Quantity Item2])
    txtTotalItem3 Currency =CCur([Unit Price Item3])*CInt([Quantity Item3])
    txtTotalItem4 Currency =CCur([Unit Price Item4])*CInt([Quantity Item4])
    txtNetTotal Currency =CCur([txtTotalShirts]) + CCur([txtTotalPants]) + CCur([txtTotalItem1]) + CCur([txtTotalItem2]) + CCur([txtTotalItem3]) + CCur([txtTotalItem4])
    txtTaxAmount Currency =CLng(CCur([txtNetTotal])*CDbl([Tax Rate])*100)/100
    txtCleaningTotal Currency =CCur([txtNetTotal]) + CCur([txtTaxAmount])
  26. Save the form
  27. To preview the form, right-click the Cleaning Orders tab and click Form View
  28. To test the form, create the following records:
     
    Customer Name Shirts Pants Other Item1 Other Item2: 
    Unit Price Qty Unit Price Qty Name Unit Price Qty Name Unit Price Qty
    John Hancock 1.15 4 1.85 2 Regular Skirt 3.25 2 Men's Suit 2Pc 6.50 1
    April Drasco 0.00 0 1.95 1 Comforter 18.95 1      
    Christie Hanson 1.15 1 1.85 4            
    Amie Perlman 0.00 0 0.00 0 Silk Shirt 2.55 3      
     
  29. Close the Cleaning Orders form

Scientific Notations

Besides the regular format we are used to using to represent a number, another technique consists of writing the number as an exponent. Using this technique, instead of using 1000 to represent a thousand, you can use 1.00e3. This is referred to as scientific notation.

To apply the scientific notation to a field:

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.

Series-Based Functions
 

Introduction

A series or collection-based function is one that considers a particular column and performs an operation 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 must 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.

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's span can be projected, estimated, or evaluated. As days, months or years go by, the value of such an asset degrades.

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 Method

Another method used to calculate the depreciation of an item is through a concept referred to as the Straight Line Method. 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:

Sum of the Years' Digits

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

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 Access 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

MCAS: Using Microsoft Office Access 2007 Topics

C4 Create fields and modify field properties

Exercises

Yugo National Bank

  1. Open the Yugo National Bank1 database
  2. Open Employees table in Design View and complete its columns as follows:
     
    Field Name Data Type Field Size Caption
    EmployeeID AutoNumber   Employee ID
    EmployeeNumber Text 10 Employee #
    FirstName Text 50 First Name
    LastName Text 50 Last Name
    Title Text 60  
    WorkPhone Text 40 Work Phone
    Extension Number Integer  
    Address Text 60  
    City Text 50  
    State Text 50  
    ZIPCode Text 30 ZIP Code
    Country Text 50  
    HourlySalary Currency   Hourly Salary
    Notes Memo    
  3. Create a form for the Employees table. Save the form as Employees and design it as you see fit
     
    Employeees
  4. Close the table
  5. Open the Customers table in Design View. Change the Data Type of the ZIPCode field to Number and its Field Size to Integer. Save and close the table
  6. Create a form for the Employees table. Save the form as Employees and design it as you see fit before closing it
  7. Export the Customers table as a Microsoft Excel spreadsheet to the My Documents folder
  8. Export the Customers table as a text file to the My Documents folder
  9. Open the Customers table in Design View. Insert a field between CustomerID and AccountNumber. Set its properties as follows:
    Name: DateCreated
    Data Type: Date/Time
    Caption: Date Created
  10. Open the Customers form in Design View and add the Date Created field
     
  11. Adjust the Tab Order accordingly
  12. Save and close the Customers form
  13. Check the size of the database. Then compact it

Watts A Loan

  1. Open the Watts A Loan1 database
  2. Open the Types of Loans table in Design View and change the following properties:
     
    Field Name Data Type Field Size Caption
    LoanTypeID     Loan Type ID
    LoanType   50 Loan Type
    Description Memo    
  3. Save and close the table
  4. Create a form for the Types of Loans table and design it to appear like the other forms of the same database
     
    Watts A Loan
  5. Close it
  6. Open the Types of Loans form and create the following records:
     
    Type of Loan ID Type of Loan Description
    1 Personal This is loan given as a cashier check to a customer who wants a cash loan
    2 Car This loan will be processed by our partners as car dealers
    3 Boat  
    4 Furniture  
    5 Musical Instrument We have some partnerships in musical instruments stores. This is the type of loan we will make available to the customers they find for us
  7. Close the form

US Senate

  1. Open the US Senate1 database 
  2. Open the Senators , start a new table and save it as 
  3. Switch the table to Design View. Rename the first field from ID to SenatorID and add the following fields:
     
    Field Name Data Type Field Size Caption
    SenatorID AutoNumber   Senator ID
    SenatorName Text 50 Senator Name
    SeatingStatus Text 50 Seating Status
    Gender Text 40 Last Name
    State Text 2  
    Race Text 40  
    Religion Text 50  
    YearElected Number Integer Year Elected
    Party Text 50  
    DCAddress Text 255 DC Address
    DCOfficePhoneNumber Text 40 Office Phone #
    WebSite Hyperlink   Web Site
    LocalAddress1 Text   Local Address 1
    LocalAddress2 Text   Local Address 2
    LocalAddress3 Text   Local Address 3
    LocalAddress4 Text   Local Address 4
    Pictures Attachment   Pictures
    Biography Memo    
  4. Use the Lookup Wizard on the Party to create a list of political parties. The options will be Democrat, Republican, Independent, Green, Other
  5. Save and close the table
Previous Copyright © 2008-2019, FunctionX, Inc. Next