Purpose
Return value
Syntax
=SYD(cost,salvage,life,period)
- cost - Initial cost of asset.
- salvage - Asset value at the end of the depreciation.
- life - Periods over which asset is depreciated.
- period - Period to calculation depreciation for.
Using the SYD function
The Excel SYD function returns the “sum-of-years” depreciation for an asset in a given period. The calculated depreciation is based on initial asset cost, salvage value, and the number of periods over which the asset is depreciated.
For example, for an asset with an initial cost of $10,000, a useful life of 5 years, and a salvage value of $2,000, depreciation in year 1 can be calculated with SYD as follows:
=SYD(10000,2000,5,1) // returns $2,667
In the worksheet as shown, the formula in C7, copied down, is:
=SYD(cost,salvage,life,B7)
Sum of years calculation
In the example, the asset has a useful life of 5 years. Excel sums the years like this:
=1+2+3+4+5=15
This sum is then used in the calculation as follows: The asset loses 8000 in value over a 5 year period, so depreciation in period 1 = 5/15 * 8000 = $2,667, depreciation in period 2= 4/15 * 8000 = $2,133 , and so on. The table below summarizes the calculation in all periods.
| Year | Depreciation Calculation |
|---|---|
| 1 | =(5/15) * 8000 |
| 2 | =(4/15) * 8000 |
| 3 | =(3/15) * 8000 |
| 4 | =(2/15) * 8000 |
| 5 | =(1/15) * 8000 |
Purpose
Return value
Syntax
=TBILLEQ(settlement,maturity,discount)
- settlement - Settlement date of the security.
- maturity - Maturity date of the security.
- discount - Discount rate of the security.
Using the TBILLEQ function
The Excel TBILLEQ function returns the bond-equivalent yield for a Treasury bill, based on a settlement date, a maturity date, and a discount rate. In the example shown, the settlement date is 5-Feb-2019, the maturity date is 1-Feb-2020, and the discount rate is 2.54%. The formula in F5 is:
=TBILLEQ(C5,C6,C7)
With these inputs, the TBILLEQ function returns a yield of 2.53%, with percentage number format applied.
Entering dates
In Excel, dates are serial numbers . Generally, the best way to enter valid dates is to use cell references, as shown in the example. To enter valid dates directly inside a function, the DATE function is the best option.
About treasury bills
A treasury bill (also called a T-Bill) is a short-term debt obligation issued by the US Treasury Department. T-Bills are sold in increments of $100, and have terms that range from a few days up to 52 weeks. Backed by US government, T-Bills are considered a low risk investment.
T-Bills are typically sold at a discount from par amount (face value), and the discount rate is determined at auction. However, T-bills can also be sold at a premium, when the price is greater than the par amount.
T-Bills do not offer regular interest payments like a coupon bond. However, when a T-Bill matures, the owner is paid it’s par amount, or face value. When the par value is greater than the purchase price, the difference is the interest earned.
