ارایه دهنده کلیه خدمات حسابداری.حسابرسی و اموزش مدیریت وطراحی سیستم-مشاوره و نظارت TURKMENSTUDENT

MIRR

Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost of the investment and the interest received on reinvestment of cash.

Syntax

MIRR(values,finance_rate,reinvest_rate)

Values    is an array or a reference to cells that contain numbers. These numbers represent a series of payments (negative values) and income (positive values) occurring at regular periods.

  • Values must contain at least one positive value and one negative value to calculate the modified internal rate of return. Otherwise, MIRR returns the #DIV/0! error value.
  • If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

Finance_rate    is the interest rate you pay on the money used in the cash flows.

Reinvest_rate    is the interest rate you receive on the cash flows as you reinvest them.

Remarks

  • MIRR uses the order of values to interpret the order of cash flows. Be sure to enter your payment and income values in the sequence you want and with the correct signs (positive values for cash received, negative values for cash paid).
  • If n is the number of cash flows in values, frate is the finance_rate, and rrate is the reinvest_rate, then the formula for MIRR is:

Example

The example may be easier to understand if you copy it to a blank worksheet.

How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

 

1

2

3

4

5

6

7

8

9

A

B

Data

Description

-$120,000

Initial cost

39,000

Return first year

30,000

Return second year

21,000

Return third year

37,000

Return fourth year

46,000

Return fifth year

10.00%

Annual interest rate for the 120,000 loan

12.00%

Annual interest rate for the reinvested profits

Formula

Description (Result)

=MIRR(A2:A7, A8, A9)

Investment's modified rate of return after five years (13%)

=MIRR(A2:A5, A8, A9)

Modified rate of return after three years (-5%)

=MIRR(A2:A7, A8, 14%)

Five-year modified rate of return based on a reinvest_rate of 14 percent (13%)

 

 

 

 

 

NPER

Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.

Syntax

NPER(rate, pmt, pv, fv, type)

For a more complete description of the arguments in NPER and for more information about annuity functions, see PV.

Rate    is the interest rate per period.

Pmt    is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes.

Pv    is the present value, or the lump-sum amount that a series of future payments is worth right now.

Fv    is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).

Type    is the number 0 or 1 and indicates when payments are due.

Set type equal to

If payments are due

0 or omitted

At the end of the period

1

At the beginning of the period


Example

The example may be easier to understand if you copy it to a blank worksheet.

How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

 

1

2

3

4

5

6

A

B

Data

Description

12%

Annual interest rate

-100

Payment made each period

-1000

Present value

10000

Future value

1

Payment is due at the beginning of the period (see above)

Formula

Description (Result)

=NPER(A2/12, A3, A4, A5, 1)

Periods for the investment with the above terms (60)

=NPER(A2/12, A3, A4, A5)

Periods for the investment with the above terms, except payments are made at the beginning of the period (60)

=NPER(A2/12, A3, A4)

Periods for the investment with the above terms, except with a future value of 0 (-9.578)

 

 

 

 

 

 

 

 

NPV

Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).

Syntax

NPV(rate,value1,value2, ...)

Rate    is the rate of discount over the length of one period.

Value1, value2, ...    are 1 to 29 arguments representing the payments and income.

  • Value1, value2, ... must be equally spaced in time and occur at the end of each period.
  • NPV uses the order of value1, value2, ... to interpret the order of cash flows. Be sure to enter your payment and income values in the correct sequence.
  • Arguments that are numbers, empty cells, logical values, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers are ignored.
  • If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored.

Remarks

  • The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation is based on future cash flows. If your first cash flow occurs at the beginning of the first period, the first value must be added to the NPV result, not included in the values arguments. For more information, see the examples below.
  • If n is the number of cash flows in the list of values, the formula for NPV is:

  • NPV is similar to the PV function (present value). The primary difference between PV and NPV is that PV allows cash flows to begin either at the end or at the beginning of the period. Unlike the variable NPV cash flow values, PV cash flows must be constant throughout the investment. For information about annuities and financial functions, see PV.
  • NPV is also related to the IRR function (internal rate of return). IRR is the rate for which NPV equals zero: NPV(IRR(...), ...) = 0.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

 

1

2

3

4

5

6

A

B

Data

Description

10%

Annual discount rate

-10,000

Initial cost of investment one year from today

3,000

Return from first year

4,200

Return from second year

6,800

Return from third year

Formula

Description (Result)

=NPV(A2, A3, A4, A5, A6)

Net present value of this investment (1,188.44)

In the preceding example, you include the initial $10,000 cost as one of the values, because the payment occurs at the end of the first period.

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

 

1

2

3

4

5

6

7

8

A

B

Data

Description

8%

Annual discount rate. This might represent the rate of inflation or the interest rate of a competing investment.

-40,000

Initial cost of investment

8,000

Return from first year

9,200

Return from second year

10,000

Return from third year

12,000

Return from fourth year

14,500

Return from fifth year

Formula

Description (Result)

=NPV(A2,                             A4:A8)+A3

                Net present value of this investment (1,922.06)

=NPV(A2, A4:A8, -9000)+A3

Net present value of this investment, with a loss in the sixth year of 9000 (-3,749.47)

In the preceding example, you don't include the initial $40,000 cost as one of the values, because the payment occurs at the beginning of the first period.

 

 

 

PMT

Calculates the payment for a loan based on constant payments and a constant interest rate.

Syntax

PMT(rate,nper,pv,fv,type)

For a more complete description of the arguments in PMT, see the PV function.

Rate    is the interest rate for the loan.

Nper    is the total number of payments for the loan.

Pv    is the present value, or the total amount that a series of future payments is worth now; also known as the principal.

Fv    is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type    is the number 0 (zero) or 1 and indicates when payments are due.

Set type equal to

If payments are due

0 or omitted

At the end of the period

1

At the beginning of the period


Remarks

  • The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans.
  • Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper.

Tip

To find the total amount paid over the duration of the loan, multiply the returned PMT value by nper.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

 

1

2

3

4

A

B

Data

Description

8%

Annual interest rate

10

Number of months of payments

10000

Amount of loan

Formula

Description (Result)

=PMT(A2/12, A3, A4)

Monthly payment for a loan with the above terms (-1,037.03)

=PMT(A2/12, A3, A4, 0, 1)

Monthly payment for a loan with the above terms, except payments are due at the beginning of the period (-1,030.16)

Example 2

You can use PMT to determine payments to annuities other than loans.

The example may be easier to understand if you copy it to a blank worksheet.

How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

 

1

2

3

4

A

B

Data

Description

6%

Annual interest rate

18

Years you plan on saving

50,000

Amount you want to have save in 18 years

Formula

Description (Result)

=PMT(A2/12, A3*12, 0, A4)

Amount to save each month to have 50,000 at the end of 18 years (-129.08)

Note  The interest rate is divided by 12 to get a monthly rate. The number of years the money is paid out is multiplied by 12 to get the number of payments.

 

 

 

 

PPMT

Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

Syntax

PPMT(rate,per,nper,pv,fv,type)

For a more complete description of the arguments in PPMT, see PV.

Rate    is the interest rate per period.

Per    specifies the period and must be in the range 1 to nper.

Nper    is the total number of payment periods in an annuity.

Pv    is the present value— the total amount that a series of future payments is worth now.

Fv    is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type    is the number 0 or 1 and indicates when payments are due.

Set type equal to

If payments are due

0 or omitted

At the end of the period

1

At the beginning of the period


Remark

Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

 

1

2

3

4

A

B

Data

Description (Result)

10%

Annual interest rate

2

Number of years in the loan

2000

Amount of loan

Formula

Description (Result)

=PPMT(A2/12, 1, A3*12, A4)

Payment on principle for the first month of loan (-75.62)

Note   The interest rate is divided by 12 to get a monthly rate. The number of years the money is paid out is multiplied by 12 to get the number of payments.

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How?

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

 

1

2

3

4

A

B

Data

Description (Result)

8%

Annual interest rate

10

Number of years in the loan

200,000

Amount of loan

Formula

Description (Result)

=PPMT(A2, A3, 10, A4)

Principal payment for the last year of the loan with the above terms (-27,598.05)

+ نوشته شده در  چهارشنبه چهارم اردیبهشت 1387ساعت 9:4  توسط جلیل بدراق نژادturkmen student ir   |