To create a field that supports numbers:
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. This 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.
Microsoft Access suppports different types of natural
numbers.
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,
display the table in the Design View. Specify the Data Type of a field 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.
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.
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. Normally, when you set the
data type of a field to Number, it is automatically converted into Long
Integer. If that's the data type you want, there is nothing more to do.
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.
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:
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 various types of decimal
numbers.
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.
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.
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.
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 create a field that supports percent value, in
either view, set the data type to Number. Then:
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:
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Practical
Learning: Using a Number
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 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 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.
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.
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.
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 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.
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 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):
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.
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.
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)
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);
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.
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.
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 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.
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 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 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).
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
No comments:
Post a Comment