Excel Financial Calculation & its Meaning

                       

                   

What is Microsoft Excel

Microsoft Excel is a Worksheet which is in the form of rows and columns. It is one of the software application from Microsoft Office Suite (It consist of Microsoft Word, Power point, Excel, Outlook , Note) .

This application is initially launched in 1985  by Microsoft. Charles Simonyi is also called the father of MS-Excel. Microsoft Excel 16.x (current version) can handle more than 1 million (1048576) rows and 16384 columns.

MS-Excel is used in making charts, graphs, helps in calculation (arithmetical, financial function etc), VBA programming, macros, pivot tables etc.

Here we are covering some Excel financial  Calculation & Its Meaning  in details

In this post, we are covering some of the  Financial terms used in MS-Excel below are

  • ACCRINT
  • ACCRINTM
  • AMORDEGRC
  • AMORLINC
MS-Excel  Terms and Its Meaning

1)

ACCRINT It returns the accrued (deposit) interest for a security that pays interest on a periodic basis which may be (annually, semi-annually or quarterly).

Let us assume that there is a company that issues shares or bond. ACCRINT function is used to Calculate that  interest  on Shares or Bonds . The periodic interest may be annually, Semi-Annually or Quarterly.

Issue— It is the security’s issue date, expressed as a serial date number.

Syntax:

=ACCRINT (issue, first_interest, settlement, rate, [par], frequency, [basis], [calc_method])

Parameters

  • Issue_date— The date that the security was issued. (the date on that company issues shares or bond)
  • first_interest_date— The date that the first interest will be paid [the first interest should be paid on which date. here company will fix the date)
  • settlement_date— The settlement date of the security [Date on which the whole amount should be settled)
  • Rate:–  The annual coupon rate for the security . [The security issue to trader should be charged on a percentage basis. The organization himself fix the rate percentage]
  • Par:– Par value of security (shares or bond). Also called Face valueor nominal value.

Face value is that initial value when promotors or organizations give money to the business man (on the basis of face value of security (shares or bond)). If you do not put any figure, it will automatically take 1000  by default.

  • Frequency— The frequency is the interest payments for the security. like you are giving interest annually (1) , semi-annually (2) or quarterly (4).
  • Basis:– Optional. It is the type of day count(days in month/days in an year) to use when calculating interest for the security. On Basis row, We have to put as we live in India If this parameter is omitted, it will be assumed that the basis is set to 0.

You can select any of the following values according to the region.

Value                    Explanation
0 30/360  (US/NASD)
1 Actual/Actual
2 Actual/360
3 Actual/365 (India)
4 30/360 (European)

calculation_method—It is an Optional; The logical value that specifies the way to calculate the total accrued interest when the settlement date  is latter than the first_interest date.

If you deposit on the first_interest date, you have to write 0

If you deposit on the settlement date, you have to write 1

Since, This is an optional. If you do not fill anything, it will not have any problem. But if you want to fill it, write “TRUE”, it will take to settlement date.

 It is either of the following values

Value Explanation
0 It Calculates the accrued interest from first_interest_date to settlement_date
1 It Calculates the accrued interest from issue_date to settlement_date

 Returns

The ACCRINT function returns a numeric value.

Issue 01/01/2018 (Jan 01)—The date when the security is issued
First_interest 01/03/2018 ( Jan 03)–  First interest date
settlement 03/04/2018 (March 04) – Settlement date
rate  11%
[par]  150000 [Security Amount]
frequency 4 [Quarterly]
basis 3  [India which has 365 days so we should write 3]
[calc_method] TRUE  [It will take you to settlement date]
ACCRINT (ACCRUED INTEREST)  2802.739726

 2)

ACCRINTM— It is a short form of Accrues Interest Maturity (deposit interest till maturity). It returns (or get) the accrued interest for a security (shares or bond) that pays interest at maturity [It pays interest one time only).

Syntax 

=ACCRINTM(issue, settlement, rate, [par], [basis])

  • Issue— Issue date of the security. (The date on the company issues Shares or Bond)
  • Settlement— The security’s (Shares or Bonds)  settlement date
  • Rate:- The security’s annual coupon rate.
  • [par]–The security’s [par] value. if it is omitted, it will take 1000 by default.
  • [basis]— An optional argument, that specifies the day count basis to be used in the calculation.

 Calculation

Date should be in format of MMDDYY. (Date must be in the format of your computer date settings).

Eg:

Issue 01/01/2018 (Jan 01)
Settlement 03/04/2018 (March 04)
Rate 11%
[par] 63000
[basis] 3
=ACCRINTM(issue, settlement, rate, [par], [basis]) 1177.150685

Notes:

·In Excel, Dates are serial number

·If dates are invalid, ACCRINTM will return #VALUE!

·ACCRINTM returns #NUM  when

oIssue date>=settlement date

oRate<0 or par<=0

oBasis is out of range

3)

AMORDEGRC — It returns the linear depreciation (amount of decrease in the book value) of an asset for each accounting period on a prorated basis.( (prorated means distributed or assessed proportionality).

Syntax: 

=AMORDEGRC(cost, purchase_date, first_period, salvage, period, rate,[basis])

  • Cost:–It is the cost of an asset
  • purchase_date:- the date on which asset was purchased.
  • first_period:- the date of the end of the first period.
  • salvage:– It is the value after the asset has been fully depreciated. [when you have used the asset completely and wanted to sell it again on some amount, so the value or amount which the asset will be sold is called Salvage value]
  • period:- It is the period  to calculate the linear depreciation. [you have to calculate depreciation on which period. either you have to calculate depreciation on the 01st period or 2nd period or 3rd period. Whatever period, you have to calculate you have to fill it]
  • rate:- rate is the rate of depreciation.
  • [basis]—optional. It is the type of year basis to use when calculating the depreciation. If the basis value is omitted, it will automatically take 0.

3)

AMORDEGRC — It returns the linear depreciation (amount of decrease in the book value) of an asset for each accounting period on a prorated basis.( (prorated means distributed or assessed proportionality).

Syntax: 

=AMORDEGRC(cost, purchase_date, first_period, salvage, period, rate,[basis])

  • Cost:–It is the cost of an asset
  • purchase_date:- the date on which asset was purchased.
  • first_period:- the date of the end of the first period.
  • salvage:– It is the value after the asset has been fully depreciated. [when you have used the asset completely and wanted to sell it again on some amount, so the value or amount which the asset will be sold is called Salvage value]
  • period:- It is the period  to calculate the linear depreciation. [you have to calculate depreciation on which period. either you have to calculate depreciation on the 01st period or 2nd period or 3rd period. Whatever period, you have to calculate you have to fill it]
  • rate:- rate is the rate of depreciation.
  • [basis]—optional. It is the type of year basis to use when calculating the depreciation. If the basis value is omitted, it will automatically take 0.
Value Explanation
0 360 days (US &North America)
1  Actual ( If there is a leap year days will be 366 , else 365 days)
3 Actual (365 or 366 days)
4 360 days (European)

Eg:

Cost 10,000
Purchase date 01-01-2020 (Jan 01)—The date must be in that format in which your computer date setting is.
First period 06-30-2020 (June 30)
Salvage  1000
Period 1
Rate 10%
Basis   1
= AMORDEGRC(cost, purchase_date, first_period, salvage, period, rate,[basis])          2191

Notes

  • The depreciation coefficient applied by the AMORDEGRC function is determined by asset life, where asset life=1/rate
Asset Life  Depreciation Coefficient
Between 3 and 4 years                  1.5
Between 5 and 6 years                    2
Above (More than) 6 years                  2.5
  • AMORDEGRC will return #VALUE if any dates are invalid
  • AMORDEGRC will return #NUM if

cost<=salvage

rate<=0

basis is not 0-4

asset life is between 0-1,1-2,2-3,3-4 or 4-5

4)

AMORLINC— It returns the depreciation of an asset for each accounting period, on a prorated basis (prorated means distributed or assessed proportionality).

Syntax :

=AMORLINC (cost, purchase_date, first_period, salvage, period, rate,[basis])

  • cost: cost of the asset
  • purchase_date: the date that the asset was purchased.
  • first_period:– the date of the end of the first period
  • salvage:- salvage value after the asset has been fully depreciated
  • period:- the period in which to calculate the linear depreciation.
  • rate:  it is the rate of depreciation.
  • basis: optional.

Calculation

cost 10,000
Purchase_date 01/01/2020 [Jan 01)
First_period 12/31/2020 (Dec 31)
salvage 1000
period 1
rate 15%
[basis] 1
=AMORLINC((cost,purchase_date,first_period,

salvage,period,rate,[basis])

1500

Leave a Comment