Question

Henrie’s Drapery Service is investigating the purchase of a new machine for cleaning and blocking drapes. The machine would cost $102,990, including freight and installation. Henrie’s has estimated that the new machine would increase the company’s cash inflows, net of expenses, by $30,000 per year. The machine would have a five-year useful life and no salvage value.

    

Required:

1.	Enter the Excel formula inputs and compute the machine’s internal rate of return.
	NPER: PMT: PV: FV: Internal Rate of Return:
2.	Suppose that the new machine would increase the company’s annual cash inflows, net of expenses, by only $26,475 per year, instead of $30,000 per year. Enter the Excel formula inputs and compute the machine’s internal rate of return.
	NPER: PMT: PV: FV: Internal Rate of Return:

     

Answer 1

Internal rate of return (IRR) is the interest rate at which the net present value of all the cash flows (both positive and negative) from a project or investment equal zero.

To calculate IRR we use trial and error method

1st part

for example we take Discount rate as 10%

Then

year	cash flow	present value
0	-102990	-102990
1	30000	27272.72727
2	30000	24793.38843
3	30000	22539.44403
4	30000	20490.40366
5	30000	18627.63969
Total		+10733.60

please note that the outflow is trated as negative and if there is any residual value for the machine that amount is also considered   

Here we discounted all the Cash flows ie inflow and outflow using a discount factor but the sum is not zero which means that the rate at which the net present value become zero is greater than 10%

now lets try 14%

Year	cash flow	present value
0	-102990	-102990
1	30000	26315.78947
2	30000	23084.02585
3	30000	20249.14549
4	30000	17762.40832
5	30000	15581.05993
Total		2.429

So @14% discount rate the net present value becomes nearly 0 .

so IRR =14%

2nd Part

Here the cash inflow decreases so the IRR will also decreses

Lets Try

First we try 10%

Year	Cash Flow	Present Value
0	-102990	-102990
1	26475	24068.18182
2	26475	21880.16529
3	26475	19891.05935
4	26475	18082.78123
5	26475	16438.89203
Total		-2628.92

Here the PV of net cash flow becomes negative so the IRR is less than 10%

so lets try 9%

Year	Cash Flow	Present value
0	-102990	-102990
1	26475	24288.99083
2	26475	22283.47782
3	26475	20443.55763
4	26475	18755.55746
5	26475	17206.93345
total		-11.48

So the PV has become nearly zero so the IRR=9%

1)=NPER (rate, pmt, pv, [fv], [type])

• rate - The interest rate per period.
• pmt - The payment made each period.
• pv - The present value, or total value of all payments now.
• fv - [optional] The future value, or a cash balance you want after the last payment is made. Defaults to 0.
• type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0.

=PMT(rate, nper, pv, [fv], [type])

• Rate is the interest rate for the loan.
• Nper is the total number of payments for the loan.
• Pv is the present value; also known as the principal.
• Fv is optional. It is the future value, or the balance that you want to have left after the last payment. If fv is omitted, the fv is assumed to be zero.
• Type is optional. If omitted, it is assumed to be zero, and payments are due at the end of the period. Use 1 in this argument if payments are due at the beginning of the period.

=PV(rate, nper, pmt, [fv], [type])

1. rate (required argument) – The interest rate per compounding period. A loan with a 12% annual interest rate and monthly required payments would have a monthly interest rate of 12%/12 or 1%. Therefore, the rate would be 1%.
2. nper (required argument) – The number of payment periods. For example, a 3 year loan with monthly payments would have 36 periods. Therefore, nper would be 36 months.
3. pmt (required argument) – The fixed payment per period.
4. fv (optional argument) – An investment’s future value at the end of all payment periods (nper). If there is no input for fv, Excel will assume the input is 0.
5. type (optional argument) – Type indicates when payments are issued. There are only two inputs, 0 and 1. If type is omitted or 0 is the input, payments are made at period end. If set to 1, payments are made at period beginning.

=FV(rate,nper,pmt,[pv],[type])

1. Rate (required argument) – This is the interest rate for each period.
2. Nper (required argument) – The total number of payment periods.
3. Pmt (optional argument) – This specifies the payment per period. If we omit this argument, we need to provide the PV argument.
4. PV (optional argument) – This specifies the present value (PV) of the investment/loan. The PV argument, if omitted, defaults to zero. If we omit the argument, we need to provide the Pmt argument.
5. Type (optional argument) – This defines whether payments are made at start or end of the year. The argument can either be 0 (payment is made at the end of the period) or 1 (the payment is made at the start of the period).

IRR=14%

excel formula is

=IRR (values, [guess])

• values - Array or reference to cells that contain values.
• guess - [optional] An estimate for expected IRR. Default is .1 (10%).

2)=NPER (rate, pmt, pv, [fv], [type])

• rate - The interest rate per period.
• pmt - The payment made each period.
• pv - The present value, or total value of all payments now.
• fv - [optional] The future value, or a cash balance you want after the last payment is made. Defaults to 0.
• type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0.

=PMT(rate, nper, pv, [fv], [type])

• Rate is the interest rate for the loan.
• Nper is the total number of payments for the loan.
• Pv is the present value; also known as the principal.
• Fv is optional. It is the future value, or the balance that you want to have left after the last payment. If fv is omitted, the fv is assumed to be zero.
• Type is optional. If omitted, it is assumed to be zero, and payments are due at the end of the period. Use 1 in this argument if payments are due at the beginning of the period.

=PV(rate, nper, pmt, [fv], [type])

1. rate (required argument) – The interest rate per compounding period. A loan with a 12% annual interest rate and monthly required payments would have a monthly interest rate of 12%/12 or 1%. Therefore, the rate would be 1%.
2. nper (required argument) – The number of payment periods. For example, a 3 year loan with monthly payments would have 36 periods. Therefore, nper would be 36 months.
3. pmt (required argument) – The fixed payment per period.
4. fv (optional argument) – An investment’s future value at the end of all payment periods (nper). If there is no input for fv, Excel will assume the input is 0.
5. type (optional argument) – Type indicates when payments are issued. There are only two inputs, 0 and 1. If type is omitted or 0 is the input, payments are made at period end. If set to 1, payments are made at period beginning.

=FV(rate,nper,pmt,[pv],[type])

1. Rate (required argument) – This is the interest rate for each period.
2. Nper (required argument) – The total number of payment periods.
3. Pmt (optional argument) – This specifies the payment per period. If we omit this argument, we need to provide the PV argument.
4. PV (optional argument) – This specifies the present value (PV) of the investment/loan. The PV argument, if omitted, defaults to zero. If we omit the argument, we need to provide the Pmt argument.
5. Type (optional argument) – This defines whether payments are made at start or end of the year. The argument can either be 0 (payment is made at the end of the period) or 1 (the payment is made at the start of the period).

IRR=9%

excel formula is

=IRR (values, [guess])

• values - Array or reference to cells that contain values.
• guess - [optional] An estimate for expected IRR. Default is .1 (10%).

Hence solved