# Excel Calculation & Its Meaning VII

Introduction

In our previous series of Financial Calculation through MS-Excel tools , we covered

·        PRICE

·        PRICEDISC

·        PRICEMAT

·        PV

·        RATE

·        RRI

In this Post We will cover some financial terms like

·        SLN

·        SYD

·        TBILLEQ

·        TBILLPRICE

·        TBILLYIELD

·        VDB

For More details regarding Microsoft Excel and its function, Please click the Link Below

https://www.thearticle111.com/2021/01/excel-calculation-its-meaning-viii.html

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/