Excel Calculation & Its Meaning VII

MS-Excel Financial terms

 43)

SLN:–It is also called Straight Line Depreciation. This function calculates the depreciation of an asset on a straight-line basis for one period. In financial modeling, the SLN function helps calculate the straight-line depreciation of a fixed asset when building a budget.

Syntax:             

                         =SLN(cost, salvage, life)

• Cost (required argument) – This is the initial cost (Purchase cost) of the asset.
• Salvage (required argument) – The value at the end of the depreciation (sometimes called the salvage value of the asset). It is the Sale value an asset after depreciation.
• Life (required argument) – This is the number of periods over which the asset is depreciated (sometimes called the useful life of the asset).

Notes

·        Straight-line depreciation is the simplest way of calculating the depreciation of a fixed asset.

·        The formula used in calculating straight-line depreciation is:

                          

                          SLN =                (Cost – Salvage)

                                                          —————

                                                               Life

 Eg:

 

Item	Cost (Purchase value)	Salvage (Sale Value)	Life (number of period)	SLN =SLN(cost,salvage,life)
Computer	25000	3000	5	=SLN(25000,3000,5) =4400
Bike	200000	25000	7	=SLN(200000,25000,7) =25000
Car	1000000	200000	10	=SLN(1000000,200000,10) =80000

Mathematical Value

Computer:

Purchase value=25000         Salvage value=3000       Life=5 years

                          SLN =                (Cost – Salvage)

                                                          —————        = 25000—3000= 22000

                                                               Life

=22000/5  =Rs.4400 = same value as in calculated in Excel formula

44) 

SYD:– SYD is short for sum of years digits. This function calculates the “sum-of-years” depreciation for an asset in a given period in the lifetime of an asset.

Syntax  :—   

                                   SYD(cost, salvage, life, per)

Mathematical formula of SYD=

cost-salvage     ———– i

(life-per+1)*2   ————ii

life*(life+1)     ———–iii

SYD=   i*ii

      ——-

        iii

 Where

• Cost is the initial cost of the asset
• Salvage is the final value of the asset at the end of its lifetime
• Life is the number of periods over which the depreciation occurs
• Per is the period for which the depreciation is being calculated

 Eg:

 

Item	Cost (Purchase value)	Salvage (Sale Value)	Life (number of period)	SLN =SLN(cost,salvage,life)
Computer	25000	3000	5	=SLN(25000,3000,5) =4400

 

YEAR	SLN value
1	4400
2	4400
3	4400
4	4400
5	4400
TOTAL	22000
SALVAGE value	3000
COST value	25000

 

YEAR	SYD value =SYD(cost,salvage,life,per)
1	=SYD(25000,3000,5,1) =7,333.33
2	=SYD(25000,3000,5,2) =5,866.67
3	=SYD(25000,3000,5,3) =4,400.00
4	=SYD(25000,3000,5,4) =2,933.33
5	=SYD(25000,3000,5,5) =1,466.67
TOTAL	22000
SALVAGE value	3000
COST value	25000

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 Mathematical value

cost-salvage     ———– i

(life-per+1)*2   ————ii

life*(life+1)     ———–iii

SYD=   i*ii

      ——-

        Iii

 

SYD  =

Per=4

Cost-salvage= 25000-3000= 22000 ————————–i

(life-per+1)*2   ———— =  (5-4+1)*2 = 4 —————–ii

 

life*(life+1)     ———- = 5*(5+1) =30 ———————–iii

 

SYD= i*ii

—————–

          iii

= 22000*4= 88000/30   = Rs. 2933.33  (same value on per=4)

45) 

TBILLEQ:–  This  function calculates the bond-equivalent yield for a Treasury bill (T-Bill). (Treasury Bill is issued by the Central Government. It is used by us for Money Market or Huge Marketing purpose for Bond purchasing. Treasury Bill is a kind of Loan whose duration is less than a year. The duration of Bond which you purchase directly is between 1 to 30 years.)

Syntax:      

                 =TBILLEQ(settlement, maturity, discount)

·        Settlement (required argument) – This is the settlement date of the T-bill. It is the date after the issue date when the T-bill is traded to the buyer.

• Maturity (required argument) – The date when the T-bill will matured. It must be greater than and within one year of the settlement date. This is so, as the life of T-bills is less than one year.
• Discount (required argument) – The T-bill discount rate. We can take from the risk-free investment rate if unavailable.

Mathematical formula

TBILLEQ = (365 × rate)/(360-(rate × DSM))

Here DSM is the number of days from settlement to maturity. Treasury bills are generally known as T-bills and have a maximum maturity of less than one year (364 days). Hence, they are categorized as money market methods.

 

Settlement Date	Maturity Date	Discount Rate	TBILLEQ =TBILLEQ(settlement,maturity,discount)
04-01-2018(April 01,2018)	09-30-2018(Sept 30, 2018)	7.30%	=TBILLEQ(04-01-2018,09-30-2018,7.30%) =0.076850084 =0.076850084*100 =7.69%

Mathematical formula

TBILLEQ = (365 × rate)/(360-(rate × DSM))

=(365*7.30%)/ (360- (7.30%*176))     

=26.645/  360-12.848 =26.745/347.152  = 0.0770411

=0.0770411*100 = 7.70% (same as calculated with Excel function)          

46)

TBILLPRICE:–This function calculates the price per $100 face value (can be understood as Rs. 100 in context of Indian Market) for a Treasury bill, based on a settlement date, a maturity date, and a discount. It will calculate the fair market value of a Treasury bill / bond. This function is useful determining the value of a bond and deciding if an investment should be made or not.

Syntax        

                     =TBILLPRICE(settlement, maturity, discount)

• Settlement (required argument) – This is the settlement date of the T-bill. The security settlement date is the date after the issue date when the T-bill is traded to the buyer.
• Maturity (required argument) – This is the T-bill’s maturity date. The maturity date is the date when the T-bill is matured.
• Discount (required argument) – The T-bill’s (Treasury Bill) discount rate of the security

Eg:

Settlement Date	Maturity Date	Discount Rate	TBILLEQ =TBILLPRICE(settlement,maturity,discount)
04-01-2018(April 01,2018)	09-30-2018(Sept 30, 2018)	7.30%	=TBILLPRICE(04-01-2018,09-30-2018,7.30%) =96.30944444 = 96.31 [Estimated Market value of Bond)

47)

TBILLYIELD :– TBILLYIELD function is helpful in calculating the yield on a Treasury bill when we are given the start date, end date, and price.

Syntax        

                       =TBILLYIELD(settlement, maturity, pr)

• Settlement (required argument) – This is the settlement date of the Treasury bill. The security’s settlement date is the date after the issue date when the T-bill is traded to the buyer.
• Maturity (required argument) – This is the T-bill’s maturity date. The maturity date is the date when the T-bill is matured
• Pr (required argument) – The T-bill’s (Treasury Bill) price per $100 face value.

 Eg:

 

Settlement Date	Maturity Date	Price per face value	TBILLYIELD =TBILLYIELD(settlement,maturity,pr)
04-01-2018(April 01,2018)	09-30-2018(Sept 30, 2018)	98.5	=TBILLYIELD(04-01-2018,09-30-2018, 98.5) =0.030122162 =0.030122162*100 =3.01%

Mathematical formula

TBILLYIELD=

100-pr———————————————-i

———

Pr

 

360/DSM —————————————-ii

 

TBILLYIELD=  i*ii

 

Pr=98.5

 

100-98.5/98.5= 1.5/98.5= 0.015228—– i

 360/DSM = 360/176 ——–ii

TBILLYIELD =  i*ii =  0.015228*2.046 = 3.1% [equivalent result to the Excel function)

 

                                                                                                                                                                   48)

 VDB—It is also called variable declining balance. This financial function calculates the depreciation of an asset using the Double Declining Balance (DDB) method or another method specified by changing the factor argument.

It also allows us to specify a factor to multiply the Straight-Line Depreciation by, although the function uses the DDB method by default. It will help a financial analyst in building financial models or creating a fixed asset depreciation schedule for analysis.

 Syntax                    

                =VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])

• Cost (required argument) – This is the initial cost (original cost) of the asset.
• Salvage (required argument) – This is the value of an asset at the end of the depreciation. It can be zero. It is also known as the salvage value.
• Life (required argument) – This is the useful life of the asset or the number of periods for which the asset will be depreciated.
• Start_period (required argument) – The starting period for which you want to calculate the depreciation. Start_period must use the same units as life.
• End_period (required argument) – This is the ending period for which you want to calculate the depreciation. End_period must use the same units as life.
• Factor (optional argument) – This is the rate of depreciation. If we omit the argument, the function will take the default value of 2, which denotes the double declining balance method.
• No_switch – This is an optional logical argument that specifies whether the method should switch to straight-line depreciation when depreciation is greater than the declining balance calculation. Possible values are:

TRUE –Excel will not switch to the straight-line depreciation method

FALSE – Excel will switch to the straight-line depreciation method when depreciation is greater than the declining balance calculation.

 Eg:

 

Cost of Bike	Life (Total yrs)	Salvage Value	Period (Yrs)	VDB =VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch])
100000	3 yrs	10000	1	=VDB(100000,10000,3,0,1) =66666.67 (Price depreciated to 66666.67 between 0 to 1 year)
			2	=VDB(100000,10000,3,1,2) =22222.22 (Price depreciated to 22222.22 between 1 to 2 years)
			3	=VDB(100000,10000,3,2,3) =1111.11 (Price depreciated to 1111.11 between 2 to 3 years)

 

If you calculate the Total VDB value ( 66666.67+22222.22+1111.11) = you will get 90000

Salvage value = 100000-90000 = Rs. 10000/