Range contains specific text

Excel Ppmt Function Examples

Example 1

The following spreadsheet shows the Excel Ppmt function used to calculate payment on the principal, in months 1 and 2 on a loan of $50,000 which is to be paid off in full after 5 years. Interest is charged at a rate of 5% per year and the payment to the loan is to be made at the end of each month.

Formula:   A B 1 Payments on the principal, during mths 1 and 2, on a loan of $50,000 that is to be paid off in full over 5 years, with an interest rate of 5% per year (payment made at end of each mth): 2 Mth 1: =PPMT( 5%/12, 1, 60, 50000 ) 3 Mth 2: =PPMT( 5%/12, 2, 60, 50000 )

Result:   A B 1 Payments on the principal, during mths 1 and 2, on a loan of $50,000 that is to be paid off in full over 5 years, with an interest rate of 5% per year (payment made at end of each mth): 2 Mth 1: -$735.23 3 Mth 2: -$738.29

Note that in this example:

• 
  The payments are made monthly, so we have had to convert the annual interest rate of 5% into the monthly rate (=5%/12), and the number of years into months (=5*12).
  
• 
  As the forecast value is zero, and the payment is to be made at the end of the month, the [fv] and [type]arguments can be omitted from the above functions.
  
• 
  The returned payments are negative values, as these represent outgoing payments (for the individual taking out the loan).
  

Example 2

In the spreadsheet below, the Excel Ppmt function is used to calculate the payment on the principal, during quarters 1 and 2 of an investment that is required to increase an investment from $0 to $5,000 over a period of 2 years. Interest is paid at a rate of 3.5% per year and the payment into the investment is to be made at the beginning of each quarter.

Formula:   A B 1 Payments on the principal, during qtrs 1 and 2, into an investment with current value $0, which is required to reach $5,000 over 2 yrs. The interest rate is 3.5% per year (payment made at beginning of each qtr): 2 Qtr 1: =PPMT( 3.5%/4, 1, 8, 0, 5000, 1 ) 3 Qtr 2: =PPMT( 3.5%/4, 2, 8, 0, 5000, 1 )

Result:   A B 1 Payments on the principal, during qtrs 1 and 2, into an investment with current value $0, which is required to reach $5,000 over 2 yrs. The interest rate is 3.5% per year (payment made at beginning of each qtr): 2 Qtr 1: -$600.85 3 Qtr 2: -$606.11

Note that, in this example:

• 
  The payments are made quarterly, so the annual interest rate of 3.5% is converted into a quarterly rate (3.5%/4), and the number of years is converted into quarters (=2*4).
  
• 
  the [type] argument has been set to 1, to indicate that the payment is to be made at the beginning of each quarter.
  
• 
  The returned payments are negative values, as these represent outgoing payments (for the investor).
  

Basic Description

The Excel DISC function calculates the Discount Rate for a bond.

The format of the function is:

DISC( settlement, maturity, pr, redemption, [basis] )

Where the arguments are as follows :

settlement	-	The security's settlement date (ie. the date that the coupon is purchased)
maturity	-	The security's maturity date (ie. the date that the coupon expires)
pr	-	The security's price per $100 face value.
redemption	-	The security's redemption value per $100 face value
[basis]			-	An optional argument which defines the day count basis to be used in the calculation. Possible values of Basis and their meanings are : Basis Day Count Basis 0 (or omitted) US (NASD) 30/360 1 actual/actual 2 actual/360 3 actual/365 4 European 30/360 The financial day count basis rules are explained further on the Wikipedia Day Count Convention page

Note that the settlement and maturity dates should be input as either:

• 
  References to cells containing dates
  

or

• 
  Dates returned from formulas
  

- If you attempt to input these date arguments as text, Excel may misinterpret them, due to different date systems, or date interpretation settings.

Warning: Although you can input the date arguments as date serial numbers, this is not recommended as date serial numbering does vary across different computer systems.

Disc Function Example

In the following example, the Excel Disc function is used to calculate the discount rate of a coupon purchased on 01-Apr-2010, with Maturity date 31-Mar-2015. The price per $100 face value is $95, the Redemption value is $100, and the US (NASD) 30/360 day count basis is used :

	A	B
1	Settlement Date:	01-Apr-2010
2	Maturity Date:	31-Mar-2015
3	=DISC( B1, B2, 95, 100 )

The functions calculates the Discounted Rate to be 1.0%

Note that, as the default US (NASD) 30/360 day method is used in the above example, the basis argument can be omitted.

DEPRECIATION

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

Where the arguments are as follows:

cost	-	The cost of the asset
date_purchased	-	The date of purchase of the asset
first_period	-	The date of the end of the first period
salvage	-	The salvage value, at the end of the lifetime of the asset
period	-	The number of the period over which the depreciation is to be calculated
rate	-	The asset's rate of depreciation
[basis]	-	An optional integer argument which specifies the financial day count basis to be is used. This may be any of the following values:
Basis Day Count Basis 0 (or omitted) US (NASD) 30/360 1 actual/actual 2 actual/360 3 actual/365 4 European 30/360
		A detailed description of the financial day count basis rules is provided on the Wikipedia Day Count Convention page

Note that the date arguments should be entered into the function as either:

• 
  References to cells containing dates
  

or

• 
  Dates returned from formulas
  

Warning:

• 
  If you attempt to input the dates as text, they may be misinterpreted, due to the date system and date interpretation settings on your computer.
  
• 
  Although dates can be entered as serial numbers, this is not advised, as date serial numbering varies across different computer systems.
  

Excel Amordegrc Function Example

In the following example, the Excel Amordegrc function is used to calculate the depreciation of an asset during the first period. The asset was purchased on 01-Jan-2011, at a cost of €150 and the first period ends on 30-Sep-2011. The asset depreciates at a rate of 20% per year and has a salvage value of €20. The European day count basis is used.

	A	B
1	Purchase Date:	01-Jan-2011
2	First Period Date:	30-Sep-2011
3	=AMORDEGRC( 150, B1, B2, 20, 1, 20%, 4 )

The function returns the value 42.

Excel DB Function Example 1

The format of the function is :

DB( cost, salvage, life, period, [month] )

In the example below, the DB function is used to find the yearly depreciation of an asset that cost $10,000 at the start of year 1, and has a salvage value of $1,000 after 5 years.

Note that, in this example, the yearly rate of depreciation, calculated from the equation 1-(Salvage/Cost)^(1/Life) is calculated to be 36.9%

Formulas:

Results:

The format of the function is :

DDB( cost, salvage, life, period, [factor] )

DDB Function Example

In the example below, the DDB function uses the double declining depreciation method to calculate the yearly depreciation of an asset that cost $10,000 at the start of year 1, and has a salvage value of $1,000 after 5 years.

Formulas:

Results:

The format of the function is :

SLN( cost, salvage, life )

cost	-	The initial cost of the asset
salvage	-	The value of the asset at the end of the depreciation
life	-	The number of periods over which the asset is to be depreciated

SLN Function Examples

The examples below show the SLN function, used to calculate the yearly or monthly depreciation of assets with different cost, salvage and lifetime values.

Formulas:

Results:

The format of the function is :

SYD( cost, salvage, life, per )

cost	-	The initial cost of the asset
salvage	-	The value of the asset at the end of the depreciation
life	-	The number of periods over which the asset is to be depreciated
per	-	The period number for which you want to calculate the depreciation

SYD Function Example

In the example below, the SYD function uses the sum-of-years' digits depreciation method to calculate the yearly depreciation of an asset that cost $10,000 at the start of year 1, and has a salvage value of $1,000 after 5 years.

Formulas:

Results: