I work with a girl who is getting her masters degree. She is taking a Finance class, and has gotten to a question she can't figure out. I was pretty good at algebra at one time, but apparently not anymore because I don't even remember enough to attempt to set it up. I told her I didn't know, but I had some imaginary friends that probably did. Can you all help me (her) out?

The problem is:

Consider two streams of cash flows, A and B. Stream A's first cash flow is $11,600 and is received three years from today. Future cash flows in stream A grow by 4 percent in perpetuity. Stream B's first cash flow is -$13,000, is received two years from today, and will continue in perpetuity. Assume that the appropriate discount rate is 12 percent.

Suppose that the two streams are combined into one project, called C. What is the IRR of Project C?

Do you really mean cash flow B is -$13,000?

My first thought was that I have no idea but I can ask SigForum.

Oh wait. . .

quote:

Originally posted by JALLEN:
Do you really mean cash flow B is -$13,000?

That's what the problem says. Sounds like a bad investment to me, but what do I know, I can't do the math! I actually asked her if that was supposed to be the US Government deficit or something, with a negative cash flow into perpetuity.

There's no algebra there just a simple math problem, I don't have Excel or financial calculator to do it right now. Just solve for discount rate that makes NPV 0. Do this with trial and error or financial software. With different start dates it makes it slightly harder.

This would have been an undergrad question for me. Surprised this is considered MBA level question.

Unless I am misreading the problem or there is something missing, it seems to me that it is going to depend on how many years out you go. The net cash flow goes positive after the sixth year and increases in a positive direction each year thereafter. For the IRR calculation, you have to fix the end point somewhere. The way I calculate it, once the cash flow goes sufficiently positive, after year 14 the IRR goes from negative to positive which will continue to increase each year thereafter.

You sure they are not asking "What is the NPV of the cash flows assuming the 12 percent discount rate?"

Either way, you need a point in the future to stop, I think.

Seems like a poorly worded question. How does one receive negative cash flow?

I read it as with A you get $11,600 at the end of year 3 and then the amount grows at 4% in each of the following years. So $11,600*1.04 in year 4, $11,600*1.04^2 in year 5, $11,600*1.04^3 in year 6, and so on.

For B you pay out a fixed $13,000 a year starting at the end of year 2.

If you add the two streams together it would be:

Today: 0+0
Year 1: 0+0
Year 2: 0-$13,000
Year 3: $11,600-$13,000
Year 4 and subsequent years: $11,600*1.04^(Year-3)-$13,000

But again, the question doesn't seem right.

quote:

Seems like a poorly worded question. How does one receive negative cash flow?

That's political speak for 'payment.'

quote:

Originally posted by zipriderson:

quote:

Seems like a poorly worded question. How does one receive negative cash flow?

That's political speak for 'payment.'

Like cutting spending. If I cut spending, I spend less than I did previously. In politispeek, it means you spend more than you did previously, but not as much as you had planned to spend.

quote:

Originally posted by trapper189:
Seems like a poorly worded question. How does one receive negative cash flow?

I read it as with A you get $11,600 at the end of year 3 and then the amount grows at 4% in each of the following years. So $11,600*1.04 in year 4, $11,600*1.04^2 in year 5, $11,600*1.04^3 in year 6, and so on.

For B you pay out a fixed $13,000 a year starting at the end of year 2.

If you add the two streams together it would be:

Today: 0+0
Year 1: 0+0
Year 2: 0-$13,000
Year 3: $11,600-$13,000
Year 4 and subsequent years: $11,600*1.04^(Year-3)-$13,000

But again, the question doesn't seem right.

Same here.

quote:

Originally posted by Sailor1911:

quote:

Originally posted by trapper189:
Seems like a poorly worded question. How does one receive negative cash flow?

I read it as with A you get $11,600 at the end of year 3 and then the amount grows at 4% in each of the following years. So $11,600*1.04 in year 4, $11,600*1.04^2 in year 5, $11,600*1.04^3 in year 6, and so on.

For B you pay out a fixed $13,000 a year starting at the end of year 2.

If you add the two streams together it would be:

Today: 0+0
Year 1: 0+0
Year 2: 0-$13,000
Year 3: $11,600-$13,000
Year 4 and subsequent years: $11,600*1.04^(Year-3)-$13,000

But again, the question doesn't seem right.

Same here.

Clearly, someone is getting paid under the table.

Here you go. First lay out cash flow A through say year 5. Then, in year 6 calculate the PV ( in year 6) of the perpetuity. Repeat for cash flow B. Add the two cash flows to get cash flow C. Calculate the IRR of cash flow C.

Can she ask her finance instructor? Another classmate? If she’s getting a master’s in this field, I’m surprised she’s asking anyone to do her thinking, calculating, or setup for her.

Now if it’s an add-on class her advisor is requiring her to take outside of her field, I guess I get it, but still, I’m a little surprised that she’s not ferreting this out on her own. It doesn’t seem to be too complex, just lay out the problem and do the math it’s asking her to do.

As Skins said, the IRR is the % that forces the NPV to zero. For perpetuity, tell her to use 360 for the number of periods. If she wants to, she can use 400. After ~360 payments, the effect is negligible.

It's been twenty years since my last Finance class, but this shouldn't be that hard to figure out in Excel.

The problem in nonsensical, and simply not answerable the way it is written. Either she didn't get it down correctly, or something was lost in translation to you, or the instructor is incompetent.

"Future cash flows in stream A grow by 4 percent in perpetuity" But it doesn't give the amount of the future cash flows or the number or the times.

"Stream B's first cash flow is -$13,000, is received two years from today, and will continue in perpetuity."

Again, when and what amounts are these "cash flows"? Is this a payout of $13,000 annually? Monthly?

This problem as written makes no sense.

I have an MBA with a Finance focus. The question is poorly worded. I cannot solve for the IRR of Project C without knowing the date of the IRR. In year 1 it is 0. In year 2 it is 0. IRR becomes negative in year 3 through ? - I don't have my HP12C on me right now.

Thank you all. I haven't had a chance to talk to her yet to see what the actual answer the instructor was looking for was, but I appreciate all the help. The question is likely just worded poorly, because I typed it out verbatim.

quote:

Originally posted by irreverent:
Can she ask her finance instructor? Another classmate? If she’s getting a master’s in this field, I’m surprised she’s asking anyone to do her thinking, calculating, or setup for her.

Now if it’s an add-on class her advisor is requiring her to take outside of her field, I guess I get it, but still, I’m a little surprised that she’s not ferreting this out on her own. It doesn’t seem to be too complex, just lay out the problem and do the math it’s asking her to do.

Yeah, she does have people to ask. She's getting a masters in Nutrition Science, so this is a little out of her wheelhouse. I sunk my teeth into it simply because I personally hate not knowing how to do something that I knew once and should still be able to remember. I also figured SigForum might be able to come through faster than her teacher to tell the truth.

I think I could do the match if I understood the jargon. What exactly does the 12% discount apply to?

quote:

Originally posted by Perception:
Thank you all. I haven't had a chance to talk to her yet to see what the actual answer the instructor was looking for was, but I appreciate all the help. The question is likely just worded poorly, because I typed it out verbatim.

quote:

Originally posted by irreverent:
Can she ask her finance instructor? Another classmate? If she’s getting a master’s in this field, I’m surprised she’s asking anyone to do her thinking, calculating, or setup for her.

Now if it’s an add-on class her advisor is requiring her to take outside of her field, I guess I get it, but still, I’m a little surprised that she’s not ferreting this out on her own. It doesn’t seem to be too complex, just lay out the problem and do the math it’s asking her to do.

Yeah, she does have people to ask. She's getting a masters in Nutrition Science, so this is a little out of her wheelhouse. I sunk my teeth into it simply because I personally hate not knowing how to do something that I knew once and should still be able to remember. I also figured SigForum might be able to come through faster than her teacher to tell the truth.

Yuck. Going from Nutrition to Finance? Hope she got the answers she needed/ and that her prof made the question more straightforward. It’ll be a miserable semester if the prof keeps that up.

I haven’t seen a definitive answer yet so I’ll try to help. However, i’m hampered as i’m Only on a phone traveling

First, either there’s a slight misunderstanding on her part or your transcription with the following: stream B’s first cash flow is negative. For the problem to make sense, it has to be positive same as Stream A. Assuming Stream B is a positive cash flow of $13,000 per year starting inyear two going to perpetuity, she can try the solution below.

Another thing throwing me off if the perpetuity but she can test the solution in excel using two sets: one with 100 years and another 300 years. She’s going to have to do this in Excel anyway.

It’s a two step process for each 100 years and 300 year iteration.

The setup is in Excel, label columns across from year 0 to year 100. Then in row 2, enter Stream A casflow. Remember Stream A’s cash flow starts in year 3 and grows by whatever percentage. In the same row under year 0, enter the formula to calculate the Net Present Value of the cash flow using the discounted rate of 12%.

Then in Row 3, enter the constant cash flow starting in year 2. Also calculate the NPV of Stream B.

Then in Row 4, put in the formula to add the numbers in rows 2 and 3 under each of the year. Excel for year 0, the number has to be negative and the numbers for year 2 and on are positive. Then in Row 5 starting under year 2, enter the formula for Excel ‘s IRR against the cash flow of Row 4 beginning from year 0 until year 2. Don’t worry if the result is #NA. She needs to copy the formula across the years always from year 0 to whatever year column she is calculating for. She can do this by putting $ before the cell A4 reference in the formula.

There are two IRR formulas in Excel. The old function which has a calculation error and the new which corrects the error and is the one she should use. As she copies the formula across the years it will get to a value that won’t change anymore. This is the IRR of the combined cash flows.

I know I said she would need to do it for 100 and 300 years but I remembered I used the IRR function when I was figuring out whether to take early lower pension versus waiting for it and just extending the years and calculating the IRR for each incremental year got me the same result.

I would do the problem but, as I said, I am traveling and only have my phone. Good luck.