G
Guest
How do you use the Function for IRR if you have an outlay of cash at the start and then you receive the same amount of cash indefinitely?
N
Norman Harker
Hi Laurie!
If it's a single outlay:
Rate = CashFlow/Outlay
Rate will be the effective rate for the periodicity of the cash flow.
With a monthly cash flow
Annual Effective = (1+CashFlow/Outlay)^12-1
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
If it's a single outlay:
Rate = CashFlow/Outlay
Rate will be the effective rate for the periodicity of the cash flow.
With a monthly cash flow
Annual Effective = (1+CashFlow/Outlay)^12-1
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
M
millsy
You can't use the IRR function because your cash inflows do not end -
this is called a perpetuity.
IRR is the rate of return (or discount rate) at which the net present
value (NPV) of a set of cash flows is zero. The NPV of a perpetuity of
say £150 per annum at a discount rate of 8% is £150/0.08= £1,875.
In your example, there is an initial cash outflow at the beginning so
the NPV is the same i.e. If the outflow is £2,000 the NPV is -£2,000
(using the convention that outflows are negative and inflows are
positive).
So, using the figures above, you need to find the discount rate i
where:
NPV = 0 = (150/i) - 2000
2,000 = 150/i or i=150/2000 = 7.5%
This assumes annual cash flows.
this is called a perpetuity.
IRR is the rate of return (or discount rate) at which the net present
value (NPV) of a set of cash flows is zero. The NPV of a perpetuity of
say £150 per annum at a discount rate of 8% is £150/0.08= £1,875.
In your example, there is an initial cash outflow at the beginning so
the NPV is the same i.e. If the outflow is £2,000 the NPV is -£2,000
(using the convention that outflows are negative and inflows are
positive).
So, using the figures above, you need to find the discount rate i
where:
NPV = 0 = (150/i) - 2000
2,000 = 150/i or i=150/2000 = 7.5%
This assumes annual cash flows.
G
Guest
Thanks for your help, Is this correct then
1000 cash outla
60 per year indefinetel
Would be 60/1000 = 6%
1000 cash outla
60 per year indefinetel
Would be 60/1000 = 6%
N
Norman Harker
Hi Laurie!
Yes! Assuming first receipt of 60 was received at end of year 1.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Yes! Assuming first receipt of 60 was received at end of year 1.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
N
Norman Harker
Hi Laurie!
If payments are received in advance then:
=60/(1000-60)
Returns: 6.38297872340426%
Not as ridiculous as it might seem because that is the invariable
practice with income producing real estate. But there rents are most
often monthly or quarterly in advance and you need to convert the
periodic return to an annual one.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
If payments are received in advance then:
=60/(1000-60)
Returns: 6.38297872340426%
Not as ridiculous as it might seem because that is the invariable
practice with income producing real estate. But there rents are most
often monthly or quarterly in advance and you need to convert the
periodic return to an annual one.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
G
Guest
Great - Thank You very much for your help!!
N
Norman Harker
Hi Laurie!
Always pleased to help. You'd be surprised the number of times I've
been asked how to put infinity into the calculator!
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Always pleased to help. You'd be surprised the number of times I've
been asked how to put infinity into the calculator!
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
G
Guest
Cheating on your homework is cause for removal from the class!!
Did I scare you?
- Leigh Ann
Did I scare you?
- Leigh Ann
N
Norman Harker
Hi Leigh Ann!
It's not so much using answers that were obtained here that are the
problem. It's more failing to acknowledge the source of assistance.
But even then, if the entire answer is obtained from the group, then
I'd want to see some value added. Or I'd be looking to change my
assessment regime.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
It's not so much using answers that were obtained here that are the
problem. It's more failing to acknowledge the source of assistance.
But even then, if the entire answer is obtained from the group, then
I'd want to see some value added. Or I'd be looking to change my
assessment regime.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.