Range contains specific text

Excel Pv Function Examples

Example 1

In the following spreadsheet, the Excel Pv function is used to calculate the present value of an annuity that pays $1,000 per month for a period of 5 years. The interest is 5% per year and each payment is made at the end of the month.

Formulas:

Results:

xample 2

In the example below, the Excel Pv function is used to calculate the present value of an annuity that pays $2,000 per quarter for a period of 4 years. The interest is 10% per year and each payment is made at the start of the quarter.

Formulas:

Results:

Note that, in this example:

• 
  The payments are made quarterly, so we have had to convert the annual interest rate of 10% into the monthly rate (=10%/4), and the 4-year period needs to be input as a number of quarters (=16)
  
• 
  Again, as the initial investment is paid out, the calculated present value is negative.
  

RECEIVED( settlement, maturity, investment, discount, [basis] )

where the arguments are as shown in the table below:

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)
investment	-	The initial amount invested into the security
discount	-	The security's discount rate
[basis]			-	An optional argument which defines the day count basis to be used in the calculation. Possible values of the [basis] argument, 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.

Received Function Example

In the spreadsheet below, the Excel Received function is used to calculate amount received at maturity, on an investment of $1,000, which was used to purchase a security on 01-Apr-2005. The security matured on 1-Mar-2010, with a discount rate of 4.5%, and the US (NASD) 30/360 day count basis is used:

The formula in the above spreadsheet returns the value $1,290.32.

Note that, in this example, the [basis] argument is omitted and so takes on the default of 0 (and therefore uses the US (NASD) 30/360 basis)

Note also that, as recommended by Microsoft, the dates are not typed directly into the function. Instead, in this example, the Excel Date function has been used.

he Excel CUMIPMT function calculates the cumulative interest paid on a loan or investment, between two specified periods.

The syntax of the function is:

CUMIPMT( rate, nper, pv, start_period, end_period, type )

Where the arguments are as follows:

rate	-	The interest rate, per period.
nper	-	The number of periods over which the loan or investment is to be paid.
pv	-	The present value of the loan / investment.
start_period	-	The number of the first period over which the interest is to be calculated (must be an integer between 1 and nper).
end_period	-	The number of the last period over which the interest is to be calculated (must be an integer between 1 and nper).
type	-	An integer (equal to 0 or 1) that specifies whether the payment is made at the start or the end of the period: 0   -   the payment is made at the end of the period; 1   -   the payment is made at the beginning of the period.

Cash Flow Convention:

Note that, in line with the general cash flow convention, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers. This is seen in the example below.