How do I obtain an IRR that reads a range of data, i.e. cash outflow and
inflows, but ignores error values and blank cells?

Your options a

1. Fix the errors.
2. Change the formula so that errors and blanks return zero.
3. Create a new range which doesn't include the errors/blanks.

Before you do anything, you need to determine what the proper cash flows are.
Remember that IRR requires a cash flow for each period, even if it's zero. I
suspect that #2 is your best bet, but only your data know for sure.

--
Regards,
Fred


"hall12" wrote in message
...
How do I obtain an IRR that reads a range of data, i.e. cash outflow and
inflows, but ignores error values and blank cells?

Fred, Thanks for the reply.

The range varies because I am using an amortization schedule. This is what
I am trying to do.

I am a banker and make loans. Some customers have interest rates that are
fixed and they would like for me to reduce their rate. So I input the
original note data into an amortization schedule to calculate the normal
payments are are being made on the loan. I locate where the loan balance is
on the amortization schedule. A separate column calculates the new loan
payment based on the lower rate. Another column takes the cash savings by
subtracting the original interest cost and subtracting the new interest cost.
Now I have the cash inflows and all I have to do is fill in the fee charged
(cash outflow at the top of the column). The IRR looks at the column with
the cashflows and calculates the rate of return to the customer. The problem
is that range includes error cells that are generated due to time periods
changing, i.e. 60 months vs. 120 months etc.

Thanks



"Fred Smith" wrote:

Your options a

1. Fix the errors.
2. Change the formula so that errors and blanks return zero.
3. Create a new range which doesn't include the errors/blanks.

Before you do anything, you need to determine what the proper cash flows are.
Remember that IRR requires a cash flow for each period, even if it's zero. I
suspect that #2 is your best bet, but only your data know for sure.

--
Regards,
Fred


"hall12" wrote in message
...
How do I obtain an IRR that reads a range of data, i.e. cash outflow and
inflows, but ignores error values and blank cells?

To me, the simplest solution is to just adjust the range. Just change the IRR
formula to include only the correct number of cells.
--
Regards,
Fred


"hall12" wrote in message
...
Fred, Thanks for the reply.

The range varies because I am using an amortization schedule. This is what
I am trying to do.

I am a banker and make loans. Some customers have interest rates that are
fixed and they would like for me to reduce their rate. So I input the
original note data into an amortization schedule to calculate the normal
payments are are being made on the loan. I locate where the loan balance is
on the amortization schedule. A separate column calculates the new loan
payment based on the lower rate. Another column takes the cash savings by
subtracting the original interest cost and subtracting the new interest cost.
Now I have the cash inflows and all I have to do is fill in the fee charged
(cash outflow at the top of the column). The IRR looks at the column with
the cashflows and calculates the rate of return to the customer. The problem
is that range includes error cells that are generated due to time periods
changing, i.e. 60 months vs. 120 months etc.

Thanks



"Fred Smith" wrote:

Your options a

1. Fix the errors.
2. Change the formula so that errors and blanks return zero.
3. Create a new range which doesn't include the errors/blanks.

Before you do anything, you need to determine what the proper cash flows are.
Remember that IRR requires a cash flow for each period, even if it's zero. I
suspect that #2 is your best bet, but only your data know for sure.

--
Regards,
Fred


"hall12" wrote in message
...
How do I obtain an IRR that reads a range of data, i.e. cash outflow and
inflows, but ignores error values and blank cells?